CODEWITHSUNDEEP
mysqllaraveleloquent
Dazzle
Dazzle

Reputation: 3083

Except query in Laravel

I would like to exclude certain data from a request

Ideally, it would look something like this


Model::where('firstname', 'like', '%$firstname%')
   ->exceptWhere('lastname, 'like', '%$lastname%');

We want to load all data where condition A is true,
But exclude results that match condition B.

We must use the like or not like syntax.

For our program, we are dealing with big data. We cannot use whereIn as SQL has a limit of 65,000 placeholders. Therefore, we must select the correct data in a query.

How can I do this in Laravel or raw SQL? We are getting an error that exclude keyword is not available in MySQL 5.4.

We have also tried a few variants, which did not work, eg

WHERE (condition A)
AND NOT (condition B)

However, I can get the correct results using Sequel Pro GUI, using contains and does not contain but I cannot get the same results using SQL / Eloquent.

Upvotes: 0

Views: 1626

Answers (1)

Kamlesh Paul
Kamlesh Paul

Reputation: 12391

try whereNotExists()

Model::where('firstname', 'LIKE', "%$firstname%")
    ->whereNotExists(function ($q) use ($lastname) {
        $q->where('lastname', 'like', "%$lastname%");
    })->get();
return view('home');

it will generate SQL like

select * from `models` where `firstname` LIKE ? and not exists (select * where `lastname` like ?)

ref link https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html

Upvotes: 1

Related Questions