我有以下查詢，我想知道這在 laravel 的查詢構建器中是否可行：
SELECT "a".*
FROM "area" AS "a"
INNER JOIN "transaction" AS "t" ON "t"."f_id" = "a"."f_id"
WHERE "t"."id" = 'c5931409-37b7-4248-b958-831edaae4075'
AND "a"."id" NOT IN (
SELECT "zc"."a_id"
FROM "transaction_detail" AS td
INNER JOIN "zone_component" AS "zc" ON "zc"."id" = "td"."comp_id"
WHERE td."t_id" = 'c5931409-37b7-4248-b958-831edaae4075'
AND td."card_type" IN ( 'C_OVA', 'C_M21' )
);
uj5u.com熱心網友回復：
您可以傳遞一個Closure物件或一個Builder物件作為whereIn/的第二個引數whereNotIn
// Builder
$sub = DB::query()
->select('za.aid') // SELECT "zc"."a_id"
->from('transaction_detail', 'td') // FROM "transaction_detail" AS td
->join('zone_component as zc', 'zc.id', 'td.comp_id') // INNER JOIN "zone_component" AS "zc" ON "zc"."id" = "td"."comp_id"
->where('td.t_id', 'c5931409-37b7-4248-b958-831edaae4075') // WHERE td."t_id" = 'c5931409-37b7-4248-b958-831edaae4075'
->whereIn('td.card_type', ['C_OVA', 'C_M21']); // AND td."card_type" IN ( 'C_OVA', 'C_M21' )
// Closure
$sub = function ($query) {
$query->select('za.aid') // SELECT "zc"."a_id"
->from('transaction_detail', 'td') // FROM "transaction_detail" AS td
->join('zone_component as zc', 'zc.id', 'td.comp_id') // INNER JOIN "zone_component" AS "zc" ON "zc"."id" = "td"."comp_id"
->where('td.t_id', 'c5931409-37b7-4248-b958-831edaae4075') // WHERE td."t_id" = 'c5931409-37b7-4248-b958-831edaae4075'
->whereIn('td.card_type', ['C_OVA', 'C_M21']); // AND td."card_type" IN ( 'C_OVA', 'C_M21' )
};
// Full Query
$results = DB::query()
->select('a.*') // SELECT "a".*
->from('area', 'a') // FROM "area" AS "a"
->join('transaction as t', 't.f_id', 'a.f_id') // INNER JOIN "transaction" AS "t" ON "t"."f_id" = "a"."f_id"
->where('t.id', 'c5931409-37b7-4248-b958-831edaae4075') // WHERE "t"."id" = 'c5931409-37b7-4248-b958-831edaae4075'
->whereNotIn('a.id', $sub) // AND "a"."id" NOT IN ( ... )
->get();
您也可以行內子查詢，而不是將它們作為單獨的變數。
whereNotIn('a.id', DB::query()->.....)
whereNotIn('a.id', function ($query) { .... })
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/446176.html
下一篇：如何修復未為視圖定義的路線