SQL Select连接在Laravel/PHP中有限制 [英] SQL Select Join with a limit in Laravel/PHP

问题描述

我的原始查询搜索了主板，然后找到了主板的图像.但是现在，有些主板没有任何图像关联，因此我想运行一个查询，该查询可以找到20个带有图像的独特主板.

My original query searched the motherboards, then found images for the motherboards. But now, some of the motherboards do not have any image associated so I want to run one query that can find 20 unique motherboards with images.

到目前为止的代码:

$newMotherboards = Motherboards::join('images', 'motherboards.motherboard_id', '=', 'images.item_id')
    ->where('images.item_type', '=', 'motherboard')
    ->orderBy('motherboards.date_added', 'desc')
    ->take(20)
    ->get();

我什至尝试选择特定结果:

I have even tried selecting specific results:

$newMotherboards = Motherboards::join('images', 'motherboards.motherboard_id', '=', 'images.item_id')
    ->select('motherboards.motherboard_id', 'motherboards.name')
    ->where('images.item_type', '=', 'motherboard')
    ->orderBy('motherboards.date_added', 'desc')
    ->take(20)
    ->get();

上面的代码的问题是，当我遍历每个$ newMotherboards时，对于每个ID都存在的图像，它都有重复.我只想检索最近添加的20个独特的主板，但是有图像.

The problem with the code above is that when I loop through each $newMotherboards, it has duplicates for every image that exists with its ID. I only want to retrieve 20 unique motherboards where they were added the most recently, but have images.

如果某件商品有5张图片，并且是最近添加的20张图片中的一张，那么它将出现我20张图片的五倍.

If an item has 5 images and it is one of the recent 20 added, then it will appear five times of my 20 limit.

推荐答案

不同版本:

    $newMotherboards = Motherboards::join('images', 'motherboards.motherboard_id', '=', 'images.item_id')
        ->select('motherboards.motherboard_id', 'motherboards.name')
        ->where('images.item_type', '=', 'motherboard')
        ->distinct()
        ->orderBy('motherboards.date_added', 'desc')
        ->take(20)
        ->get();

分组依据版本:

    $newMotherboards = Motherboards::join('images', 'motherboards.motherboard_id', '=', 'images.item_id')
        ->select('motherboards.motherboard_id', 'motherboards.name')
        ->where('images.item_type', '=', 'motherboard')
        ->groupby('images.item_type')
        ->orderBy('motherboards.date_added', 'desc')
        ->take(20)
        ->get();

这篇关于SQL Select连接在Laravel/PHP中有限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！