Use Laravel query builder inside WherenotIn-CodePudding

I have the following query and i want to know if this is possible in laravel's querybuilder:

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' )
);

CodePudding user response：

You can pass a Closure object or a Builder object as the second parameter of 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();

You can inline the subqueries as well instead of having them as separate variables.

whereNotIn('a.id', DB::query()->.....)

whereNotIn('a.id', function ($query) { .... })