Granular eloquent search with multiple where and wherein issue

Hello,

I have a quick question please.

                    $query->whereIn('type', $type)
                        ->whereIn('category1', $data1)
                        ->whereIn('category2', $data2)
                        ->where('subcategory', $data3);

I have the above query and would like to search in all the fields at once and get the subcategory that matches $data3.(which is the only result I am interested in).

It looks like when I chain all the whereIn, it does not work(crashes).

So I have tried like this:

                    $query->whereIn('type', $type)
                        ->orWhereIn('category1', $data1)
                        ->orWhereIn('category2', $data2)
                        ->where('subcategory', $data3);

What I am not understanding is why "or" only works when I need "and"? (As I need to search in all of them at once.

The MYSQL query I get from this is:

where
  `type` in ('buy', 'sell')
  or `category1` in (1)
  or `category2` in (1)
  and `subcategory` = 6

But this gives me the results of all of them and does not just show the subcategory 6 which is the one I really want filtered on its own.

Any idea why please?

Thank you.

aurawindsurfing

5 years ago

Level 50

Hey @boubou

First of all listen to this podcast episode. There are some cool explanation of query builder vs eloquent ORM which is the issue here: https://pca.st/5scdurn0

To make long story short you need to have eloquent collection to use whereIn or orWhereIn and so on. You try to use query builder here that does not have those methods available. You will be able to use those methods after you call ->get() at the end of query and then apply those methods to resulting collection. Have a look here: https://laravel.com/docs/master/queries#retrieving-results

Anyhow replying to your question:


$flights = App\Models\Flight::all();

$your_result = $flights->whereIn('type', $type)
                        ->orWhereIn('category1', $data1)
                        ->orWhereIn('category2', $data2)
                        ->where('subcategory', $data3)
			->get();

Hope this helps.

Or even simpler:

$your_result = App\Models\Flight::whereIn('type', $type)
                        ->orWhereIn('category1', $data1)
                        ->orWhereIn('category2', $data2)
                        ->where('subcategory', $data3)
			->get();

LaraBABA

5 years ago

Level 10

The query you wrote above output this in MySQL:

where
  `listing_type` in ('buy', 'sell')
  or `category1` in (1)
  or `category2` in (1)
  and `subcategory` = 6

Only 1 subcategory has an id of 6. With the above query it outputs all the categories 1 and 2 + types + subcategories.

It is just weird as I di dnot expect this at all with multiple whereIn()

MichalOravec

5 years ago

Best Answer

Level 75

You probably need advanced where clauses

->whereIn('type', $type)
->where(function ($query) use ($data1, $data2) {
   $query->whereIn('category1', $data1)->orWhereIn('category2', $data2);
})->where('subcategory', $data3)

Docs: https://laravel.com/docs/7.x/queries#parameter-grouping

1 like

LaraBABA

5 years ago

Level 10

Terrific!!!!!!

Thank you so much!!!!!

Please or to participate in this conversation.