如何从一个表中获取所有不在Laravel 5.8中的表中的记录 [英] How to get all records from one table that not exist in another table in Laravel 5.8

问题描述

我想从一个表中选择所有在另一表中不存在的记录(在 reserved_shop 表中，而不在 shop_payment 中的记录).

I want to select all records from one table that not exist in another table (records that exist in reserved_shop table, not in shop_payment).

以下查询获取 shop_payment 表中存在的所有记录，那么如何根据需要更改此查询?

The following query gets all record that exists in shop_payment table, so how can I change this query as I want?

$shopPayment = \DB::table('shop_payment as p')
    ->join('market', 'market.market_id', '=', 'p.market_id')
    ->join('customer', 'customer.customer_id', 'p.customer_id')
    ->join('reserved_shop','reserved_shop.market_id','=','p.market_id')
    ->select('market.name as mn', 'p.shop_payment_id', 'p.floor_number', 
        'p.shop_number', 'p.shop_hire', 'p.payment_amount', 'p.borrow', 
        'p.month', 'p.date', 'customer.name')
    ->where('market.market_id', '=', $id)
    ->where('p.month', '=', $month)
    ->whereYear('p.date', '=', $year)
    ->groupBy('mn', 'p.shop_payment_id', 'p.floor_number', 'p.shop_number', 
        'p.shop_hire', 'p.payment_amount', 'p.borrow', 'p.month', 'p.date',
        'customer.name')
    ->where('p.status', '!=', 1)->get();

推荐答案

对DB的附加请求的第一个变体:

First variant with additional request to DB:

$existingIds = DB::table('payment')->get()->pluck('market_id')->toArray();
//then add to your builder
->whereNotIn('p.market_id', $existingIds)

带有原始sql和子查询的第二个变体:

Second variant with raw sql and subquery:

->whereRaw('p.market_id NOT IN (SELECT market_id FROM payment)')

第三(与第二相同，但具有查询生成器):

Third (the same as second but with query builder):

->whereNotIn('p.market_id', function($q){
    $q->select('market_id')->from('payment');
})->get();

UPD :

$shopPayment = \DB::table('shop_payment as p')
            ->join('market', 'market.market_id', '=', 'p.market_id')
            ->join('customer', 'customer.customer_id', 'p.customer_id')
            ->join('reserved_shop','reserved_shop.market_id','=','p.market_id')
            ->select('market.name as mn', 'p.shop_payment_id', 'p.floor_number', 'p.shop_number', 'p.shop_hire', 'p.payment_amount', 'p.borrow', 'p.month', 'p.date',
                'customer.name')
            ->where('market.market_id', '=', $id)
            ->where('p.month', '=', $month)
            ->whereYear('p.date', '=', $year)
            ->groupBy('mn', 'p.shop_payment_id', 'p.floor_number', 'p.shop_number', 'p.shop_hire', 'p.payment_amount', 'p.borrow', 'p.month', 'p.date',
                'customer.name')
            ->where('p.status', '!=', 1)
            ->whereNotIn('p.market_id', function($q){
                 $q->select('market_id')->from('payment');
            })->get();

这篇关于如何从一个表中获取所有不在Laravel 5.8中的表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！