代码点火器查询生成器子查询问题

Question

这是我创建的查询

$this->db->query('SELECT p.id,p.tag_in_ids,p.title,p.content,p.slug,p.view_count,
p.like_count, p.dislike_count, p.created_at,m.id user_id,m.full_name,
m.display_name, m.slug user_slug, m.picture, m.profile_pic, c.id cat_id,c.slug cat_slug, cl.title cat_title,
(SELECT count(a.id) cnt FROM answers a WHERE post_id = p.id AND a.status = 1) as answers_count,
(SELECT count(a.id) FROM answers a WHERE post_id = p.id AND a.status = 1 AND a.helpful) as recomended 
FROM post p
INNER JOIN members m ON m.id = p.user_id
INNER JOIN categories c ON c.id = p.cat_id
INNER JOIN categories_lang cl ON cl.cat_id = c.id
WHERE p.tag_in_ids LIKE '.$tag->id.' AND p.status=1 AND cl.lang_id = "'.$language.'"

但是，我需要将其创建为查询生成器。我找不到解决这个问题的办法。我对此有点陌生。

Answer 1

$this->db->select('p.id, p.tag_in_ids, p.title, p.content, p.slug, p.view_count, p.like_count, p.dislike_count, p.created_at, m.id as user_id, m.full_name, m.display_name, m.slug user_slug, m.picture, m.profile_pic, c.id as cat_id, c.slug as cat_slug, cl.title as cat_title, (SELECT count(a.id) FROM answers WHERE post_id = p.id AND a.status = 1) as answers_count, (SELECT count(a.id) FROM answers WHERE post_id = p.id AND a.status = 1 AND a.helpful) as recommended');
//I don't know what is a.helpful at the end of this select. If it is wrong then correct it
$this->db->from('executed_predictions ep');
$this->db->join('members m', 'm.id = p.user_id');
$this->db->join('categories c', 'c.id = p.cat_id');
$this->db->join('categories_lang cl', 'cl.cat_id = c.id');
$this->db->where('p.tag_in_ids',$tag->id);        //Since you have not given any'%' before or after string so it will check for exact match so better to put it in where clause not in like.
$this->db->where('p.status',1);
$this->db->where('cl.lang_id',$language);
return $this->db->get()->result_array();