Query return empty when Left Join table is empty in codeigniter

0
votes

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

2
phpmysqlcodeigniter
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
Thanks but I didn't get results even I remove group_by - RaGu
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
@RaGu is this field is correct 'rg.booking_id = bh.id' ?? bh.id - Abdulla Nilam
you used group_by with left joined table hence you don't get any result - Abhijit Jagtap

2 Answers

2
votes

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');
0
votes
$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