Thread

We want to query a table to find a list of active product (motorcycles) and then query another table to find the primary picture for each product.

We want to take only the primary picture for each product to be used for the thumbnail image. The primary picture will be the one with the lowest numeric value in the ‘priority’ field.

So, we’re using this ...

$this->db->select('*');
$this->db->from('product');
$this->db->where('product.status', 'active');

$this->db->limit($limit, $offset); // used for pagination

$this->db->order_by('product.make asc');
$this->db->order_by('product.model asc');

$this->db->join('product_images', 'product_images.product_id = product.product_id');
$this->db->order_by('product_images.priority asc');

$this->db->group_by('product.product_id');

$ret['rows'] = $this->db->get()->result();

It all works okay except the picture selected is not actually sorted by priority so basically, this line is not working ...

$this->db->order_by('product_images.priority asc');

Any thoughts as to how we can sort the table that is joined?