[SOLVED] Can I limit MySQL Join??

[eluser]fedeisas[/eluser]
Hi guys. I'm proudly finishing my first CI project and I'm falling in love with it. As an "amateurish" PHP developer, I've build several web apps in the past using rough procedural style. This is the first time I use a framework and it's going pretty well.

Altough, I have a question regarding both MySQL and Active Record.

The project is for a real estate CMS. I have a 'properties' table, and each property has an unique ID. And another table for the photos wich stores the path and the property parent.

So...for the main listing page...I have to get all the property records but only one image for each one. If I join the two tables I get one record per image.

Code:

$this->db->select('*');
$this->db->from('properties');
$this->db->join('cities', 'cities.id_city = properties.city');
$this->db->join('photos', 'photos.property = properties.id_properties');
$this->db->where('active', 1);
$this->db->order_by('date_published');
$query = $this->db->get();
if ($query->num_rows() > 0) {
    return $query->result();
}

I'm guessing it's something related with some JOIN parameter. I've read MySQL documentation but I just can get the fix. However, I can perform a query for each property to find a single photo, but that would overload the app, right?

I'm hoping someone can help. Regards my CI friends!


PD: I'm sorry if my english sounds lame, I'm from Argentina y hablo muy bien español.


EDIT: solved, just group my query. Oh God, this user guide is soooo good! Thanks!

$this->db->group_by();

Permits you to write the GROUP BY portion of your query:
$this->db->group_by("title");

// Produces: GROUP BY title

[eluser]nzmike[/eluser]

Oh God, this user guide is soooo good! Thanks!

Agreed. It's something you really appreciate when you've tried to use something with really bad/no documentation.

[eluser]bluepicaso[/eluser]
Thanx a lot, It helped me too