Codeigniter Query Builder subquery issue

Question

This is the query I created

$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.'"

However, I need to create this as a query builder. I couldn't find a solution for this. I'm a little new about it.

could you please simplify your query to the subquery (nested SELECT) issue, thanks — Vickel
– Vickel, Commented Apr 24, 2020 at 1:03

Sandesh · Accepted Answer · 2020-04-24 09:02:02Z

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();

answered Apr 24, 2020 at 9:02

Sandesh

462 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

starlings Over a year ago

Thank you so much!

starlings Over a year ago

"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." how do i add this rule in query builder?

Sandesh Over a year ago

Read CI documentation. It is given there in more details. codeigniter.com/userguide3/database/…

Sandesh Over a year ago

Search for 'Looking for similar data' over there

starlings Over a year ago

Thank you for your advices. why can't I see an effect in the query? It does not cause any changes. column structure integer. the code is below. $this->db->where('p.status', 1);

Collectives™ on Stack Overflow

Codeigniter Query Builder subquery issue

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related