Group By question (Active Record)

[eluser]oscaralexander[/eluser]
Hi folks! I'm running into something that should be fairly simple but I just can't get right. Here's the thing: I have a table named venues, a table named users and a table named photos. When I fetch records from the venues table, I want to include some information on the user that submitted the venue, plus the photo with the lowest order index. Here's what my tables look like (relevant bits only, for the sake of brevity):

Code:

+-------------------------------+
| VENUES                        |
+-------------------------------+
| id        7                   |
| name      Blue Note Jazz Club |
| user_id   10                  |
+-------------------------------+

+-------------------------------+
| USERS                         |
+-------------------------------+
| id        10                  |
| name      Michael Knight      |
+-------------------------------+

+-------------------------------+
| PHOTOS                        |
+-------------------------------+
| filename  some_image.jpg      |
| id        1                   |
| order     1                   |
| venue_id  7                   |
+-------------------------------+

Now, when I run the following query, I'm not getting the photo with lowest order value, but with the lowest ID instead.

Code:

$this->db->select('venues.*');    
$this->db->select('categories.name AS category_name');
$this->db->select('categories.name_url AS category_name_url');
$this->db->select('MIN(photos.order) AS photo_order');
$this->db->select('photos.filename AS photo_filename');
$this->db->select('users.email AS user_email');
$this->db->select('users.name AS user_name');
$this->db->from('venues');
$this->db->join('categories', 'categories.id = venues.category_id', 'INNER');
$this->db->join('photos', 'photos.venue_id = venues.id', 'LEFT');
$this->db->join('users', 'users.id = venues.user_id', 'INNER');
$this->db->group_by('venues.id');
$this->db->order_by('venues.num_votes', 'desc');

Any help would be very much appreciated!

[eluser]theprodigy[/eluser]
have you tried adding a where clause? Something like:

Code:

$this->db->where('photos.order','MIN(photos.order)');

Not sure it will work, but worth a try

[eluser]oscaralexander[/eluser]
It worked! Sweet!

* nominates theprodigy for most useful person of 2010 *

[eluser]oscaralexander[/eluser]
Ouch, guess my enthusiasm came a little too soon

I can't use that where clause with a LEFT join, because I'm now only getting venue records that actually have a related photo, whilst I really should be getting all.

Ideas, anyone?

[eluser]theprodigy[/eluser]

I can’t use that where clause with a LEFT join, because I’m now only getting venue records that actually have a related photo, whilst I really should be getting all.

Try something like:

Code:

$where = "photos.order = MIN(photos.order) or photos.order IS NULL";

$this->db->select('venues.*, categories.name AS category_name, categories.name_url AS category_name_url, MIN(photos.order) AS photo_order, photos.filename AS photo_filename, users.email AS user_email, users.name AS user_name');
$this->db->from('venues');
$this->db->join('categories', 'categories.id = venues.category_id', 'INNER');
$this->db->join('photos', 'photos.venue_id = venues.id', 'LEFT');
$this->db->join('users', 'users.id = venues.user_id', 'INNER');
$this->db->where($where);
$this->db->group_by('venues.id');
$this->db->order_by('venues.num_votes', 'desc');