laravel eloquent - check if all values of array in table

Question

I have a table options and I have an array of strings ["value1", "value2", "value3"].

What I'd like to do is check if all of the values within the array are present in the table.

I've tried whereIn but I think it checks if any values exist in the table.

This is what I have done currently:

$v = ["value1", "value2", "value3"];

$options = Options::whereIn('value', $v)->get();

if ($options->count() != count($v)) {
//something must be missing
}

This works, but I wonder if there is a better way? The table has millions of records so I'd like to do only 1 query if possible.

Thanks!

Can you please explain a bit more like how is the data present in your database. Is that stored like an array ["value1", "value2", "value3"] or having single values like 'value1' , 'value2'. — nayan verma
– nayan verma, Commented May 13, 2021 at 8:07
Unrelated to your question, but it's generally best practice in Laravel to make your models singular. So you should use Option. — BritishWerewolf
– BritishWerewolf, Commented May 13, 2021 at 8:11
As I understand this, you want to check that value1, value2 and value3 are all present in the database, as separate entries? If so, can't you just do $v = ["value1", "value2", "value3"]; $options = Options::whereIn('value', $v)->get(); if ($options->count() === count($v)) { echo "all three are present"; }. This assumes that you will only have one occurrence of value1, value2 and value3. — BritishWerewolf
– BritishWerewolf, Commented May 13, 2021 at 8:13
@JustCarty thanks. That's what I have done, but I didn't put it clearly in the question, sorry! — reans
– reans, Commented May 13, 2021 at 8:33

Jason · Accepted Answer · 2021-05-13 08:54:59Z

4

The answer in the comments by justcarty is technically correct, but you can reduce the load by not pulling in the options if you don't intend to use them.

if (Option::whereIn('value', [...])->count() != count([...])) {
    //perform action
}

Also note, as justcarty mentioned, this won't work if you have multiple occurrences of a value in your database, but you can get around this by adding DISTINCT clause to your query.

if (Option::distinct(['value'])->whereIn('value', [...])->count() != count([...])) {
    //perform action
}

answered May 13, 2021 at 8:54

Jason

3,0402 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BritishWerewolf Over a year ago

The only reason I pulled all values, then counted, was because I wasn't sure if OP needed to use the options afterwards. If not, then this is the best approach. As for the distinct values, that's a great shout, I couldn't remember the syntax and by the time I did I couldn't edit the comment :P

MarcelWeidum · Accepted Answer · 2021-05-13 07:58:03Z

0

whereIn check specifically for that one value in the arrays.

You can try this:

$search_values = ['value1', 'value2', 'value3'];
$found = true;
foreach ($search_values as $search) {
    if (!Search::where('column', $search)->first()) {
        $found = false;
    }
}

if (!$found) {
    // Something must be missing
}

answered May 13, 2021 at 7:58

MarcelWeidum

1453 silver badges11 bronze badges

Collectives™ on Stack Overflow

laravel eloquent - check if all values of array in table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related