Rudolph Rudolph -4 years ago 124
MySQL Question

Laravel: query builder with relationship

I have a table with input fields that act as filters. There are values from Orders table and Users table which has a relationship.

e.g.:

Order Id | User Full Name | Order Price | Date |

[order filter] [user name filter] [order date filter]


How can I filter a value from a users table via relationship?

This returns error of course:

public function name($name)
{
return $this->builder->where('name', 'LIKE', '%$name%')->orWhere('surname', 'LIKE', '%$name%');
}


Error:

QueryException in Connection.php line 770:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'where clause' (SQL: select count(*) as aggregate from `orders` where (`name` LIKE %$name% or `surname` LIKE %$name%))

Answer Source

You can add the condition directly to your query builder or create a scope.

Add this to your Order model.

public function scopeFilterUser($query, $name)
{
   return $query->whereHas('user', function ($q) use ($name) {
       $q->where('name', 'like', "%{$name}%")
           ->orWhere('surname', 'like', "%{$name}%");
   });
}

Use it like so

$orders = Order::filterUser($name)->get();

Apply the condition directly.

$orders = Order::whereHas('user', function ($query) use ($name) {
    $query->where('name', 'like', "%{$name}%")
        ->orWhere('surname', 'like', "%{$name}%");
})->get();

Edit : Based on your filter methods.

public function name($name)
{
    return $this->builder->whereHas('user', function ($q) use ($name) {
        $q->where('name', 'like', "%{$name}%")
            ->orWhere('surname', 'like', "%{$name}%");
    });
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download