Show 3 latest data from 2 tables with active record in codeigniter

Question

I have a problem with showing 3 latest data from 2 tables with active record in codeigniter.

The tables

1. album: id_album, album_name
2. photo: id_photo, album_id, photo_name

Current data

Album:

1. Car
2. Bike
3. Airplane

Photo:

1. Bike 001
2. Bike 002
3. Airplane 001
4. Airplane 002
5. Airplane 003
6. Car 001

The condition is how to show the data by 3 latest album with 1 latest photo from each album. Maybe the result like this:

1. Car 001,
2. Airplane 003,
3. Bike 002

My active record in codeigniter:

$this->db->select('album.album_name, album.id_album, photo.id_photo, photo.photo_name);
$this->db->join('album', 'photo.album_id = album.id_album');
$this->db->limit(3);
$this->db->order_by('album.id_album', 'desc');
$this->db->order_by('photo.id_photo', 'desc');
$this->db->group_by('album.album_name');    
return $this->db->get($this->table)->result();

If i use the query above, the data will be like this:

1. Car 001,
2. Airplane 001,
3. Bike 001

Any help will be so appreciate

Answer 1

To pick a latest record for each album you can use a self join

SELECT a.album_name, a.id_album, p.id_photo, p.photo_name
FROM albums AS a 
JOIN photos AS p ON a.id_album= p.album_id 
LEFT JOIN photos AS p1 ON p.album_id = p1.album_id 
               AND p.id_photo < p1.id_photo
WHERE p1.id_photo IS NULL
ORDER BY a.id_album DESC
LIMIT 3

or

SELECT a.album_name, a.id_album, p.id_photo, p.photo_name
FROM albums AS a 
JOIN photos AS p ON a.id_album= p.album_id 
JOIN (
    SELECT album_id , MAX(id_photo) id_photo
    FROM photos
    GROUP BY album_id
) AS p1 ON p.album_id = p1.album_id 
               AND p.id_photo = p1.id_photo
ORDER BY a.id_album DESC
LIMIT 3

In query build you could write it as

$this->db->select('a.album_name, a.id_album, p.id_photo, p.photo_name');
$this->db->from('albums AS a');
$this->db->join('photos AS p', 'a.id_album= p.album_id'); 
$this->db->join('photos AS p1', 'p.album_id = p1.album_id AND p.id_photo < p1.id_photo', 'left'); 
$this->db->where('p1.id_photo IS NULL', null, false)
$this->db->limit(3);
$this->db->order_by('a.id_album', 'desc');
$query = $this->db->get();

Answer 2

Rather than dealing with joins, you may want to try a different approach:

// first, fetch the 3 latest albums:

$this->db->select('id_album', 'album_name');
$this->db->from('albums');

// ordering by descending ID would give you the lastest ones first
$this->db->order_by('id_album','desc');
$this->db->limit(3);
$query = $this->db->get();
$albums = $query->result();

// now, loop each album and fetch the latest photo
foreach ($albums as $a)
{
  $this->db->select('id_photo', 'album_id', 'photo_name');
  $this->db->from('photo');
  $this->db->where('album_id', $a->id_album);

  // ordering by descending ID should return the lastest photo first
  $this->db->order_by('id_photo','desc');
  $this->db->limit(1);

  $query = $this->db->get();
  $a->latest_photo = $query->row(0);
}

// return the album information for the 3 latest ones, along with the lastest photo for each
return $albums;

The return will be an array of objects that looks pretty much like this:

Array
(
    [0] => stdClass Object
        (
            [id_album] => 0
            [album_name] => name of album 0
            [latest_photo] => stdClass Object
                (
                    [id_photo] => latest_photo_0
                    [album_id] => 0
                    [photo_name] => Some_name_0
                )

        )

    [1] => stdClass Object
        (
            [id_album] => 1
            [album_name] => name of album 1
            [latest_photo] => stdClass Object
                (
                    [id_photo] => latest_photo_1
                    [album_id] => 1
                    [photo_name] => Some_name_1
                )

        )

    [2] => stdClass Object
        (
            [id_album] => 2
            [album_name] => name of album 2
            [latest_photo] => stdClass Object
                (
                    [id_photo] => latest_photo_2
                    [album_id] => 2
                    [photo_name] => Some_name_2
                )

        )

)

Yes, you'll be running more queries than you'd run with a single joined query, but the performance impact is negligible and you retain a lot more control on the final result, without unnecesarily complicate things from a SQL perspective