Having problems nesting where clausing when querying the Database - Laravel

Question

I'm having a problem querying the database.

My database is as follows:

Table Orders:

ordernumber
secondordernumber
deleted
created_at
user_id
- Table users:
- username
- dealername

I've allready made the model relations from orders to users.

public function user(){
    return $this->hasOne('App\User','id','user_id');
}

How i am constructing my Query:

$query = Order::query();

$query->where('deleted', 0);

$fields = array('user' => ['username','dealername'], 'ordernumber', 'ordernumber_second');

foreach ($fields as $relation => $field) {
    if (is_array($field)){
        $query->whereHas($relation, function ($q) use ($field, $searchquery) {
            $q->where(function ($q) use ($field, $searchquery) {
                foreach ($field as $relatedField){
                    $q->orWhere($relatedField, 'like', "%{$searchquery}%");
                } 
            });
        });
    } else {
        $query->orWhere($field, 'like', "%{$searchquery}%");
    }
}


$orders = $query->orderBy('created_at','desc')->get();

// return view ETC

This works and all, the only this that is not working is the where clause:

$query->where('deleted', 0);

Is not working properly, the query is returning all records even if deleted is true..

It probably has something to do with the orWhere earlier on but i can't figure it out.

What i tried:

putting the where('deleted' , 0); on other places
Nesting the $query inside the else

Any help is appreciated

You might want to output the generated SQL for debugging purposes. — online Thomas
– online Thomas, Commented Mar 6, 2018 at 10:26

fayis003 · Accepted Answer · 2018-03-06 10:33:28Z

1

at first, I think the better relation would be as below

public function user(){
    return $this->belongsTo('App\User','user_id');
}

now in query you may rewrite the code as

        $fields = array('user' => ['username','dealername'], 'ordernumber', 'ordernumber_second');

    Order::where(function($query) use ($searchquery, $fields) {
        foreach ($fields as $relation => $field) {
            if (is_array($field)){
                $query->whereHas($relation, function ($q) use ($field, $searchquery) {
                    $q->where(function ($q) use ($field, $searchquery) {
                        foreach ($field as $relatedField){
                            $q->orWhere($relatedField, 'like', "%{$searchquery}%");
                        }
                    });
                });
            } else {
                $query->orWhere($field, 'like', "%{$searchquery}%");
            }
        }
    })->where('deleted', 0)

answered Mar 6, 2018 at 10:33

fayis003

6804 silver badges12 bronze badges

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

1 Comment

fayis003 Over a year ago

also by default, the column name will be deleted_at if your using soft-deletes

Collectives™ on Stack Overflow

Having problems nesting where clausing when querying the Database - Laravel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related