John Davidson

Message:

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!

---

My Answer:
You can achieve this by using a subquery in the `whereNotIn` method of your eloquent query. Here's how you can modify your eloquent query to include the subquery:

php
$excludedSymbols = DB::connection('mysql_prod')->table('companies')
->select('companies.symbol')
->leftJoin('articles as a', 'a.companies_id', '=', 'companies.id')
->where('a.created_at', '>=', Carbon::today()->subDays(1)->startOfDay())
->orderBy('a.created_at', 'desc')
->distinct()
->pluck('symbol');

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


In this modified query, we first retrieve the symbols that should be excluded using a subquery. Then, we use the `whereNotIn` method to exclude these symbols from the final result set.