phpmysqlcodeigniterdistinctone-to-many

How to prevent duplicate parent rows when joining a table with one-to-many relationships in CodeIgniter?


shops table:

+--+-------+--------+
|id|name   |date    |
+--+-------+--------+
|1 |x      |March 10|
+--+-------+--------+
|2 |y      |March 10|
+--+-------+--------+

shop categories table (shop_cats):

+--+-------+--------+
|id|shop_id|cat_id  |
+--+-------+--------+
|1 |1      |1       |
+--+-------+--------+
|2 |1      |2       |
+--+-------+--------+

I want to get shops by category (categories are passed-in via a $cat array).

$this->db->select('shops.*');
$this->db->from('shops');
if (!empty($cat)) {
    $this->db->join('shop_cats' , 'shop_cats.shop_id = shops.id' );
    $this->db->where_in('shop_cats.cat_id', $cat);
}
     
$this->db->limit($limit, $offset);
$res = $this->db->get();

My problem is it returns duplicate results.

For example in this table

+--+-------+--------+
|id|shop_id|cat_id  |
+--+-------+--------+
|1 |1      |1       |
+--+-------+--------+
|2 |1      |2       |
+--+-------+--------+

If I want shops with a category of 1 or 2, I get the shop with id = 1 twice. I want it to return each shop only once without any duplicates.

I've tried to use group by

if (!empty($cat)) {
    $this->db->join('shop_cats' , 'shop_cats.shop_id = shops.id');
    $this->db->group_by('shop_cats.shop_id');
    $this->db->where_in('shop_cats.cat_id' , $cat);
}

It didn't work. I've also tried:

if (!empty($cat)) {
    $this->db->select('DISTINCT shop_cats.shop_id');
    $this->db->join('shop_cats', 'shop_cats.shop_id = shops.id');
    $this->db->where_in('shop_cats.cat_id' , $cat);
}

but I get a syntax error because CodeIgniter quotes the DISTINCT keyword like it is a column name.


Solution

  • Try

    $this->db->distinct('shops.*');
    $this->db->from('shops');
    $this->db->join('shop_cats', 'shop_cats.shop_id = shops.id', 'left');
    $this->db->where('shop_cats.cat_id', $cat);
    $this->db->limit($limit , $offset);
    $res = $this->db->get();