Albums and Album Thumbnail (Thumbnail of latest photo inside album)

[eluser]umefarooq[/eluser]
[quote author="umefarooq" date="1267911205"]yes you can do it using sub query here is your query, sub query works on mysql 5.+ not on mysql 4,sub query will return on one record not multiple

Code:

function get_all_albums($limit=null, $offset=null)
    {
$this->db->selec('albums.*');
$this->db->select('(select photo from photos where album_id = album.id limit 1 order by created_on desc) as photo', FALSE);
$this->db->join('users', 'users.user_id = albums.user_id');
        $this->db->order_by('created_on', 'DESC');
        return $this->db->get($this->table_name, $limit, $offset);
}

[/quote]

yes error just because of i put limit before order by in sub query now run it will work.

Code:

$this->db->select('(select photo from photos where album_id = album.id order by created_on desc limit 1) as photo', FALSE);