Create subquery in NOT IN

Question

I am using Laravel Framework 6.16.0.

I have the following sql query:

SELECT DISTINCT
    `companies`.*
FROM
    `companies`
LEFT JOIN `trx` ON `trx`.`companies_id` = `companies`.`id`
WHERE
    `trx`.`transaction_date` >= 2020-11-12 AND companies.symbol NOT IN (SELECT DISTINCT
        companies.symbol
    FROM
        `companies`
    LEFT JOIN articles a ON a.companies_id = companies.id
    WHERE
        a.created_at >= 2020-11-12 
    ORDER BY
        created_at
    DESC)
ORDER BY
    transaction_date
DESC

I have created the following eloquent query:

    DB::connection('mysql_prod')->table('companies')->select('companies.symbol')
                ->leftJoin('trx', 'trx.companies_id', '=', 'companies.id')
                ->where('trx.transaction_date', '>=', Carbon::today()->subDays(1)->startOfDay())
                ->orderBy('transaction_date', 'desc')
                ->distinct()
                ->get('symbol');

However, I am not sure how to pack the in my eloquent query to get all the symbol back that should be excluded.

I highly appreciate your replies!

As you have selected `companies.symbol` do you have to mention `->get('symbol')` again? I think `->get()` might work... — dqureshiumar, Nov 13 '20 at 19:09
You are not working with Eloquent, you are using query builder. This would be much easier if you set up relationships between your models and used Eloquent. — miken32, Nov 13 '20 at 19:51

score 1 · Accepted Answer · answered Nov 13 '20 at 19:52

You should try something like this:

$date = Carbon::today()->subDays(1)->startOfDay();
DB::connection('mysql_prod')->table('companies')->select('companies.symbol')
                ->leftJoin('trx', 'trx.companies_id', '=', 'companies.id')
                ->where('trx.transaction_date', '>=', $date)
                ->whereNotIn('companies.symbol', function ($q) use ($date) => {
                    $q->select('companies.symbol')
                      ->from('companies')
                      ->leftJoin('articles', 'articles.companies_id', 'companies.id')
                      ->where('articles.created_at', '>', $date)
                      ->distinct()
                      ->get()
                })
                ->orderBy('transaction_date', 'desc')
                ->distinct()
                ->get();

It will provide a similar query as you mentioned.

Reference from here.

Also, you can read how to write sub Query from Laravel docs.

Check this one more good answer for that what you need.

Create subquery in NOT IN

1 Answers1