Query return empty when Left Join table is empty in codeigniter

Question

I'm using Codeigniter with MySql 7.5. I have a query and it always returns empty when left join table is empty.

$this->db->select('shop.id as shop_id, shop.shop_name, rg.rating');
$this->db->from('shop');            
$this->db->join('booking as bh', 'bh.shop_id = shop.id', 'left');
$this->db->join('rating rg', 'rg.booking_id = bh.id', 'left');
$this->db->group_by("bh.id");
$this->db->order_by("bh.id", "desc");

Here I have entries in booking and shop tables but rating table is empty. But I didn't get any results.

If I remove rg.rating from select it will return correct result.

Did I miss anything? Thanks

I have no idea about CodeIgniter, but from the look of your query I think the way you are using group by seems to be wrong — jonju
– jonju, Commented Aug 2, 2016 at 11:57
I suspect it is your group by that is causing the empty result set. You are grouping on a result that might not be there. You also aren't including the group by column in the select statement. My suggestion would be to turn on the Profiler and see what the actual query looks like. — cfnerd
– cfnerd, Commented Aug 2, 2016 at 13:03
@RaGu is this field is correct 'rg.booking_id = bh.id' ?? bh.id — Abdulla Nilam
– Abdulla Nilam, Commented Aug 2, 2016 at 15:09
you used group_by with left joined table hence you don't get any result — Abhijit Jagtap
– Abhijit Jagtap, Commented Aug 4, 2016 at 21:44

PHP Geek · Accepted Answer · 2016-08-02 12:00:57Z

2

Try this-

$this->db->select('shop.id as shop_id, shop.shop_name, rating.rating');
$this->db->join('booking', 'booking.shop_id = shop.id', 'left');
$this->db->join('rating', 'rating.booking_id = booking.id', 'left');
$this->db->group_by("booking.id");
$this->db->order_by("booking.id", "desc");
return $this->db->get('shop');

answered Aug 2, 2016 at 12:00

PHP Geek

4,0632 gold badges19 silver badges32 bronze badges

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

1 Comment

Jigar7521 Over a year ago

All you need to apply 'group by' properly.@Ragu

Giri Annamalai M · Accepted Answer · 2016-08-02 12:21:26Z

0

$this->db->select('s.id as shop_id, s.shop_name, r.rating');

$this->db->from('shop s');            

$this->db->join('booking bh', 'bh.shop_id = shop.id');

$this->db->join('rating r', 'r.booking_id = bh.id');

$this->db->group_by("bh.id");

$this->db->order_by("bh.id", "desc");

   $qry =  $this->db->get();

   echo $this->db->last_query();

   print_r($qry->result_array()); exit;

Reply your output. Also change config/database.php

'db_debug' => TRUE

edited Aug 2, 2016 at 12:21

answered Aug 2, 2016 at 12:09

Giri Annamalai M

80910 silver badges27 bronze badges

1 Comment

RaGu Over a year ago

No. I got empty results

Collectives™ on Stack Overflow

Query return empty when Left Join table is empty in codeigniter

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related