Combination of where and where is not working

Question

I have a problem code beneath this line does not work! How can I let this work? where ... orWhere orWhere does filter but cumulates the queries. where ... where does not provide any result. Can someone help me?

$artworks = Artwork::where('category_id', $category)
    ->where('style_id', $style)
    ->where('technic_id', $technic)
    ->where('orientation', $orientation)
    ->get();

Here is the full code:

if (request()->category_id) {
                $category = request()->category_id;
            } else {
                $category = 0;
            }

            if (request()->style_id) {
                $style = request()->style_id;
            } else {
                $style = 0;
            }

            if (request()->technic_id) {
                $technic = request()->technic_id;
            } else {
                $technic = 0;
            }

            if (request()->orientation_id == 'vertical') {
                $orientation = 'vertical';
            } else if (request()->orientation_id == 'horizontal') {
                $orientation = 'horizontal';
            } else {
                $orientation = 0;
            }


            $artists = Artist::get();
            $artworks = Artwork::where('category_id', $category)
                ->where('style_id', $style)
                ->where('technic_id', $technic)
                ->where('orientation', $orientation)
                ->get();
            
            return view('frontend.index', compact('artworks', 'artists'));

by does not work do you mean it returns nothing, no data in the collection? — bhucho, Oct 21 '20 at 13:10
are you getting data in `Artwork::where('orientation', $orientation) ->get();` — VIKAS KATARIYA, Oct 21 '20 at 13:57
you can try this method [How to Create Multiple Where Clause Query Using Laravel Eloquent?](https://stackoverflow.com/questions/19325312/how-to-create-multiple-where-clause-query-using-laravel-eloquent) though it is the same just passing conditions in array — bhucho, Oct 21 '20 at 14:02
@GijsMachielsen: IF orWhere is returning the result then I think the combination of this doesn't match with database resulting in 0 results, can you please check if the database contains any record which matches against each where condition — svikramjeet, Oct 22 '20 at 05:34

score 0 · Accepted Answer · answered Oct 21 '20 at 13:14

0

I think you want to use OR Condition and you are mistaking it with double where. Please look below to understand properly

If you want AND condition in your query then the double where are used but if you want OR condition then you have to use orWhere

Examples:

AND condition

Query::where(condition)->where(condition)->get();

OR Conditon

Query::where(condition)->orWhere(condition)->get();

answered Oct 21 '20 at 13:14

Khalid Khan

3,017
1
11
27

I want the And and statement. If I do this ```$artworks = Artwork::where('category_id', $category)->get()``` it will provide results or this ```$artworks = Artwork::where('style_id', $style)->get()```or if I do this ```$artworks = Artwork::where('technic_id', $technic)->get()```. But if all want to combine them via this query : ```$artworks = Artwork::where('category_id', $category)->where('style_id', $style)->where('technic_id', $technic)->get()```; then it is not working – Gijs Machielsen Oct 21 '20 at 13:34
Does anyone have an idea why I have no result with where where where? – Gijs Machielsen Oct 21 '20 at 14:32
presumably, they have not debugged the issue, because the query is correct. Since they're getting `_id` variables from the post request, they should first name the variables as such, i.e `category_id` instead of `category` then make sure that they are all set before the query. I will bet that one or more are not. Defaulting them to `0` is a really bad idea... – Ballard Oct 21 '20 at 16:28
also, this suggests that you can have duplicate rows in the table, which is a problem on its own, consider making a link table – Ballard Oct 21 '20 at 16:29
also, why are you checking if all of your `ids` are true? `if (request()->style_id) {` – Ballard Oct 21 '20 at 16:30

Ballard · Answer 2 · 2020-10-21T16:38:42.087

If you expect all of your variables to be set

Your query variables category_id, style_id, orientation_id & technic_id are being defaulted to 0 if they are not true.

Your query is fine, but you may not have the data you think you do.

Run the following at the top of this function:

print_r($request->all());
exit;

If all of your variables are optional

very procedural, basic way to achieve this:

            $artists = Artist::get();
            $artworks = Artwork::where('id', '>', 0);

            $category_id = request()->input('category_id');
            if ($category_id != '') {
                $artworks->where('category_id', request()->category_id);
            }

            $style_id = request()->input('style_id');
            if ($style_id != '') {
                $artworks->where('style_id', request()->style_id);
            }

            $technic_id = request()->input('technic_id');
            if ($technic_id != '') {
                $artworks->where('technic_id', request()->technic_id);
            }

            $orientation_id = request()->input('orientation_id');
            if ($orientation_id != '') {
                $artworks->where('orientation_id', request()->orientation_id);
            }

            $artworks->get();
            
            return view('frontend.index', compact('artworks', 'artists'));

Combination of where and where is not working

2 Answers2