雄辩地与多个表联接 [英] Eloquent wherein with join multiple tables

问题描述

我有4个表，项目，清单，catitem_item和item_listing.

I have 4 tables, items, listings, catitem_item, and item_listing.

项目与列表之间存在多对多的关系. 项目和项目也有许多关系.

items and listing is many to many relationship. items and catitems is also many to many relationship.

类别包含项目类别的列表. 列表就像物品所在的位置. 示例列表商店A可以使项目椅子和项目椅子具有多个catitem类别.

catitems contains list of item's categories. listings is like location where items located. example listing shop A can have item chair and item chair have multiple catitem categories.

我的目标是获取在类别列表(例如类别1和2和3($cats))下并具有该项目所在位置的列表信息的项目. 因此，如果有6个主持人列表，它将返回6个主持人结果.

My goal is to get items which under list of categories such as category 1 AND 2 AND 3 ($cats) and with listing information where this item located. So if there are 6 listings for chair, it will return 6 chair results.

这是我到目前为止的查询.

This is the query I have so far.

$items = DB::table('items')
                ->join('catitem_item', 'catitem_item.item_id', '=', 'items.id')
                ->join('item_listing', 'item_listing.item_id', '=', 'items.id')
                ->join('listings', 'item_listing.listing_id', '=', 'listings.id')
                ->whereIn('catitem_item.catitem_id', $cats)
                //->groupBy('items.id')
                //->having(DB::raw('count(*)'), '=', count($cats))
                ->select('items.id', 'items.name', 'items.price', 'items.slug', 'item_listing.listing_id', 'listings.name as listing_name', 'listings.slug as listing_slug')
                ->get();

推荐答案

请注意，您尝试执行此操作的方式可能会导致每个项目多行(每个相关列表一次).更好的方法是在每个项目中使用一系列列表.

Note that the way you are trying to do it, you might get multiple rows per item (once per related listing). A better way would be to have an array of listings per item.

如果您使用雄辩的模型并正确设置了关系，则可以尝试以下操作:

If you use eloquent models and you have setup the relations correctly, you could try the following:

$cats = [1, 2, 3];

$query = Item::with('listings');
foreach ($cats as $cat) {
    $query->whereHas('catitems', function($q) use($cat) {
        $q->where('id', $cat);
    });
}
$items = $query->get();

现在每个项目都应该有一个listings属性.例如，对于第一个项目，您可以通过以下方式访问列表:

Now every item shoud have a listings property. For example for the first item you can access the listings the following way:

$item1 = $items[0];
$listings1 = $item1->listings;

请注意，whereHas()可能会为$cats数组中的每个条目创建一个相关的EXISTS子查询.如果这样做很慢，则可以使用JOIN查询，例如:

Note that whereHas() will probably create a correlated EXISTS subquery for every entry in the $cats array. If that is to slow, you can use a JOIN query like:

$items = Item::with('listings')
    ->join('catitem_item', 'catitem_item.item_id', '=', 'items.id')
    ->whereIn('catitem_item.catitem_id', $cats)
    ->groupBy('items.id')
    ->having(DB::raw('count(*)'), '=', count($cats))
    ->select('items.*')
    ->get();

如果您不使用口才，也可以自己急于加载".

If you don't use eloquent, you can also do the "eager loading" yourself.

$items = DB::table('items')
    ->join('catitem_item', 'catitem_item.item_id', '=', 'items.id')
    ->whereIn('catitem_item.catitem_id', $cats)
    ->groupBy('items.id')
    ->having(DB::raw('count(*)'), '=', count($cats))
    ->select('items.*')
    ->get()
    ->keyBy('id');

foreach ($items as $item) {
    $item->listings = [];
}

$itemIds = $items->pluck('id');
$listings = DB::table('listings')
    ->join('item_listing', 'item_listing.listing_id', '=', 'listings.id')
    ->whereIn('item_listing.item_id', $itemIds)
    ->groupBy('listings.id')
    ->select('listings.*', DB::raw('group_concat(item_listing.item_id) as item_ids'))
    ->get();

foreach ($listings as $listing) {
    $itemIds = explode(',', $listing->item_ids);
    foreach ($itemIds as $itemId) {
        $items[$itemId]->listings[] = $listing;
    }
    $listing->forget('item_ids');
}

这篇关于雄辩地与多个表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！