我有一个画廊图像的数据库表，按以下分类：
"公司"、"食品"、"公园"、"派对"、"游乐设施"、"学校"、"场地"
每个图像都分配了其中一个类别。
我正在构建一个主库页面，我想在其中显示数据库中每个类别的最新4张图像。
有人能帮助我如何构建查询吗？
查询开始如下：
Bugz'GalleryImage::
表格结构
Schema::create('gallery_images', function (Blueprint $table) {
//set the table engine:
$table->engine = 'InnoDb';
//define an auto-incrementing primary key:
$table->increments('id');
//define the general fields:
$table->enum('gallery', array('corporate', 'food', 'park', 'parties', 'rides', 'schools', 'venue'))->default('corporate');
$table->string('title');
$table->string('content')->nullable()->default(null);
//define the audit fields:
$table->timestamps();
$table->softDeletes();
});
我对Eloquent还没有足够的经验来编写更复杂的查询。
谢谢。
这里有一种方法。。。
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(image_id INT NOT NULL ATO_INCREMENT PRIMARY KEY
, category ENUM('corporate', 'food', 'park', 'parties', 'rides', 'schools', 'venue') NOT NULL
);
INSERT INTO my_table (category) VALUES
('corporate'),
('food'),
('park'),
('parties'),
('rides'),
('schools'),
('venue'),
('rides'),
('schools'),
('venue'),
('food'),
('park'),
('parties'),
('rides'),
('corporate'),
('food'),
('park'),
('food'),
('park'),
('parties'),
('rides'),
('food'),
('park'),
('food'),
('corporate'),
('rides'),
('corporate'),
('parties'),
('rides'),
('corporate'),
('food'),
('schools'),
('venue'),
('venue'),
('food'),
('park'),
('parties')
;
中间结果。。。
SELECT x.*
, COUNT(y.image_id) temp_ranks_for_y
FROM my_table x
JOIN my_table y
ON y.category = x.category
AND y.image_id >= x.image_id
GROUP
BY x.image_id;
+----------+-----------+-------------------+
| image_id | category | temp_ranks_for_y |
+----------+-----------+-------------------+
| 1 | corporate | 5 |
| 2 | food | 8 |
| 3 | park | 6 |
| 4 | parties | 5 |
| 5 | rides | 6 |
| 6 | schools | 3 |
| 7 | venue | 4 |
| 8 | rides | 5 |
| 9 | schools | 2 |
| 10 | venue | 3 |
| 11 | food | 7 |
| 12 | park | 5 |
| 13 | parties | 4 |
| 14 | rides | 4 |
| 15 | corporate | 4 |
| 16 | food | 6 |
| 17 | park | 4 |
| 18 | food | 5 |
| 19 | park | 3 |
| 20 | parties | 3 |
| 21 | rides | 3 |
| 22 | food | 4 |
| 23 | park | 2 |
| 24 | food | 3 |
| 25 | corporate | 3 |
| 26 | rides | 2 |
| 27 | corporate | 2 |
| 28 | parties | 2 |
| 29 | rides | 1 |
| 30 | corporate | 1 |
| 31 | food | 2 |
| 32 | schools | 1 |
| 33 | venue | 2 |
| 34 | venue | 1 |
| 35 | food | 1 |
| 36 | park | 1 |
| 37 | parties | 1 |
+----------+-----------+-------------------+
所以。。。
SELECT x.*
FROM my_table x
JOIN my_table y
ON y.category = x.category
AND y.image_id >= x.image_id
GROUP
BY x.image_id
HAVING COUNT(y.image_id) <=4
ORDER
BY category
, image_id DESC;
+----------+-----------+
| image_id | category |
+----------+-----------+
| 30 | corporate |
| 27 | corporate |
| 25 | corporate |
| 15 | corporate |
| 35 | food |
| 31 | food |
| 24 | food |
| 22 | food |
| 36 | park |
| 23 | park |
| 19 | park |
| 17 | park |
| 37 | parties |
| 28 | parties |
| 20 | parties |
| 13 | parties |
| 29 | rides |
| 26 | rides |
| 21 | rides |
| 14 | rides |
| 32 | schools |
| 9 | schools |
| 6 | schools |
| 34 | venue |
| 33 | venue |
| 10 | venue |
| 7 | venue |
+----------+-----------+
27 rows in set (0.00 sec)
此代码将检索每个类别的4个图像。如果您想检索最新的，那么只需在外部查询中添加按集群排序即可。
DB::table('gallery_images AS GI')
->where(function($query)
{
DB::table('tbl_dept_master AS GI2')
->select(DB::raw(count(1)))
->from('gallery_images As GI2')
->where('GI2.gallery','=','GI.gallery')
->where('GI2.title','>=','GI.title');
})
->get();
如果需要更多帮助，请评论。希望这能解决你的问题。