显示代码点火器中有活动记录的2个表中的3个最新数据

Question

我在显示codeigniter中有活动记录的2个表中的3个最新数据时遇到问题。

表格

1. 相册：id_album，相册名称
2. 照片：id_photo，album_id，照片名称

当前数据

相册：

1. 汽车
2. 自行车
3. 飞机

照片：

1. 自行车001
2. 002自行车
3. 飞机001
4. 002型飞机
5. 003型飞机
6. 001车

条件是如何按3个最新相册显示数据，每个相册中有一张最新照片。也许结果是这样的：

1. 001车
2. 飞机003
3. 002自行车

我在代码点火器中的活动记录：

$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();

如果我使用上面的查询，数据将是这样的：

1. 001车
2. 飞机001
3. 自行车001

任何帮助都将不胜感激

Answer 1

要为每张专辑选择最新唱片，可以使用自加入

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

或

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

在查询构建中，您可以将其写为

$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

与其处理联接，不如尝试一种不同的方法：

// 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;

返回的将是一个看起来很像的对象数组：

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
)
)
)

是的，与使用单个联接查询相比，您将运行更多的查询，但性能影响可以忽略不计，并且您可以对最终结果保留更多的控制权，而不会从SQL角度使事情变得不必要的复杂