Laravel QueryBuilder multiple columns match in the same WhereIn

Question

I would have liked to convert a SQL query like this one in Laravel Eloquent. This query works on MariaDb but i don't know about other engines (might be the reason why it isn't implented):

id	year	country	enabled
0	2000	"France"	0
1	2001	"Spain"	0
2	2002	"France"	1
3	2003	"Germany"	1

SELECT id FROM my_db.countries WHERE (name, enabled) IN (("France", 1), ("Spain", 0));

This returns 1 and 2.

This is possible with Eloquent (as suggested here: laravel whereIn multiple columns but it wouldn't return the expected results:

DB::table('countries')->select('id')->whereIn('name', ["france", "Spain"])->whereIn('enabled', [0, 1])->all();

This returns 0, 1 and 2.

I gave a shot at adapting the Illuminate/Database library to fit my needs but the databinding started to get really complexe.

I managed to make it with a whereRaw query but it isn't really clean enough for production code as there are no data binding (values shows up with ->toSql()).

Does anyone have an idea?

score 0 · Answer 1 · answered Jan 13 '21 at 09:34

I suppose you wanna get records that satisfy two conditions. whereIn checks if the column contains one of the values in the array.

`DB::table('countries')->select('id')->whereIn('name', ["france", "Spain"])->whereIn('enabled', [0, 1])->all();`

that code returns combinations france 0, france 1, Spain 0, Spain 1. TO get combinations france 0 and Spain 1 you can use this code

DB::table('countries')
            ->select('id')
            ->where(function(Builder $builder) {
                $builder
                    ->where('name', 'france')
                    ->where('enabled', 0);
            })
            ->orWhere(function(Builder $builder) {
                $builder
                    ->where('name', 'Spain')
                    ->where('enabled', 1);
            })
            ->get();

It checks the conditions name = france and enabled = 0 work together

I agree that would match what i'm looking for but i am actually trying to bind the id of a massive upsert of thousands of records. I am inclined to think that multiple columns in the same whereIn would be more optimized SQL wise and would really like a way to do it with this grammar. But i think that your solution is better than my current raw query even if it's only for the security. Thank you for your reply! — glamax13, Jan 13 '21 at 09:46

score 0 · Accepted Answer · answered Jan 13 '21 at 10:58

0

The query syntax you want to get:

SELECT id FROM my_db.countries WHERE (name, enabled) IN (("France", 1), ("Spain", 0));

is exclusive to Oracle. The whereIn method of Laravel supports (String, Array), so I think you only have the options to do it through raw queries or using the solution proposed by V-K

answered Jan 13 '21 at 10:58

jssDev

923
5
18

If i am not mistaken Oracle is MySql and the engine i am using is MariaDb (i double checked) and it works. But i guess that as MariaDb is a fork of MySql they share a lots of features. Anyway if it's only supported by those 2 engines it makes sens that Laravel Querybuilder doesn't feels like it has to support it as this behaviour can be simulated by @V-K answer. Thank you for your time. – glamax13 Jan 13 '21 at 11:46

score 0 · Answer 3 · answered Apr 02 '22 at 03:32

If you add a new column that will keep the concatenation of the country and enabled fields, then you can use a simple whereIn method.

id	year	country	enabled	country_enabled
0	2000	"France"	0	"France-0"
1	2001	"Spain"	0	"Spain-0"
2	2002	"France"	1	"France-1"
3	2003	"Germany"	1	"Germany-1"

DB::table('countries')->select('id')->whereIn('country_enabled ', ["France-1", "Spain-0"])->get();

You may even add an index to that column to speed up the search.

Of course, the provided solution will add some slight overhead to the writing in that table.

Laravel QueryBuilder multiple columns match in the same WhereIn

3 Answers3