How to convert mysql query to codeigniter using query builder?

Question

The following is my mysql query:

select * from db_posts LEFT JOIN db_followers  ON db_posts_user_id = db_followers_following AND db_followers_user_id = 276

How can I convert it to codeigniter using query builder?

$this->db->where('db_posts_user_id', 'db_followers_following');
$this->db->where('db_followers_user_id', 276);
$this->db->order_by('db_posts.db_posts_id', 'DESC');
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id');
$query2 = $this->db->get('db_posts')->result_array();

I get the required result when I use mysql query. But I get a blank array when I use codeigniter queries. What is wrong in my CI Query?

oops i forgot that. but still i get only 10 rows as result while i have total 400 rows in db_posts. is there any mistakes in my query? — compulsive coder
– compulsive coder, Commented Nov 20, 2018 at 11:25
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left'); — Amit Sahu
– Amit Sahu, Commented Nov 20, 2018 at 11:26
also i guess that is not mandatory as i get same result when i omit that statement also! — compulsive coder
– compulsive coder, Commented Nov 20, 2018 at 11:27
there is something wrong with your $this->db->where('db_posts_user_id', 'db_followers_following'); $this->db->where('db_followers_user_id', 276); Please verify it — Amit Sahu
– Amit Sahu, Commented Nov 20, 2018 at 11:28
First, Try your query without any conditions. And then use 1 condition and then second. It will tell you where is the actual problem. — Danish Ali
– Danish Ali, Commented Nov 20, 2018 at 11:33

Rishad · Accepted Answer · 2018-11-20 11:32:42Z

3

why are you using those where clause? if you are using it as condition for your 'JOIN' process, try it like this:

$this->db->order_by('db_posts.db_posts_id', 'DESC');
$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id and db_followers_user_id = 276', 'left');
$query2 = $this->db->get('db_posts')->result_array();

Points to Note

1)as previous comments said, you should 'Left Join' if you want to get all posts from 'db_posts'.

2) You can add Multiple conditions in ON Clause using and but within ''. all your conditions should be specified before second comma(,) which is before mentioning 'LEFT'.

Hope this will help. Lemme know if this help

answered Nov 20, 2018 at 11:32

Rishad

3811 gold badge3 silver badges11 bronze badges

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

3 Comments

compulsive coder Over a year ago

Lemme try it :)

compulsive coder Over a year ago

That worked man. Yous saved my hours. i never thought we could use and conditions in 'join' query builder. thanks man :) Accepting this answer

Rishad Over a year ago

Yeah we can use 'AND' conditions. Happy to know it worked for you :)

ADyson · Accepted Answer · 2018-11-20 11:22:40Z

3

Try adding the "Left" option to tell it what kind of join to do:

$this->db->join('db_followers', 'db_followers.db_followers_following = db_posts.db_posts_user_id', 'Left');

Also I'm not sure you need

$this->db->where('db_posts_user_id', 'db_followers_following');

this is already covered by your JOIN statement.

Source: http://www.bsourcecode.com/codeigniter/codeigniter-join-query/#left-join and https://www.codeigniter.com/userguide3/database/query_builder.html

answered Nov 20, 2018 at 11:22

ADyson

62.9k16 gold badges86 silver badges100 bronze badges

1 Comment

compulsive coder Over a year ago

oo lemme try that

Collectives™ on Stack Overflow

How to convert mysql query to codeigniter using query builder?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related