dynamic query laravel eloquent with whereHas

Question

I want to create dynamic filters.
for example I want to create this code

$Contact = Contact::whereHas('User', function ($query) use ($searchString) {
        $query->where('name', 'like', '%Jhone%')->orwhere('family', '<>' . 'Doe');
    })->whereHas('Account', function ($query) use ($searchString) {
        $query->where('account_name', '=' , 'test_account' )->orwhere('account_city', 'like', '%test_city%');
    })->get();

and all of parameters is variable name,like,%Jhone%,family,<>,Doe,..... and I want to pass variables to function and function create above query.

What exactly is your current problem? How does the rest of your controller action look like? — Namoshek, Jun 23 '18 at 06:52
I want to create function and passvaribles to custom function and it can create my query like above query. I don't what to do? — paranoid, Jun 23 '18 at 06:55
@paranoid : this is your requirement. please add your try also ? — Niklesh Raut, Jun 23 '18 at 07:01
@C2486 See namoshek answer and I want to pass array like this to getaccounts method `$classes=array(['User'=>['name','like','%Jhone%'],['family','<>','Doe']],['Account'=>['account_name','=','test_account'],['account_city','like','%test_city%']])` — paranoid, Jun 23 '18 at 07:24

Namoshek · Accepted Answer · 2018-06-23T07:39:52.283

I assume that the relationship functions within your Contact, User and Account models are written in camelCase and not PascalCase like your example shows.

public function getContacts(Request $request)
{
    return Contact::when($request->get('username'), function ($query, $val) use ($request) {
            $query->whereHas('user', function ($q) use ($val, $request) {
                $q->where('name', 'like', '%'.$val.'%');
                if ($request->has('familyname')) {
                    $q->orWhere('family', '<>', $request->get('familyname'));
                }
            });
        })
        ->when($request->get('accountname'), function ($query, $val) use ($request) {
            $query->whereHas('account', function ($q) use ($val, $request) {
                $q->where('account_name', $val);
                if ($request->has('city')) {
                    $q->orWhere('account_city', 'like', '%'.$request->get('city').'%');
                }
            });
        })
        ->get();
}

This function will return all contacts when no GET parameters are given on the request. If a parameter for username is present, it will only return contacts where a user with the given name exists for. If furthermore a familyname parameter is present, it will give contacts with a user that has a matching username or a familyname different from the one given. The very same applies to the account, accountname and city.

In particular, there are two things interesting about this example:

The when($value, $callback) function can be used to build very dynamic queries which only execute the $callback when $value is true. If you use $request->get('something') and something is not available as parameter, the function will return null and the callback is not executed. The callback itself has the form function ($query, $value) { ... }, where $value is the variable you passed to when() as first parameter.
Using $request->has('something') inside the query builder functions to dynamically build constraints on the query is an alternative to when(). I only added it for the purpose of demonstration - in general I'd recomment sticking to one style.

If you would extend on the example, you could also build highly dynamic queries where not only the variable content like Doe for the family name is given as parameters, but also the comparator like =, <> or like. But extending further on this topic is too much for this answer and there are already tutorials about this topic available anyway.

Edit: here an example for a dynamic query with more detailed input

Expected input (slightly different than your request because yours cannot work):

$filters = [
    'user' => [
        ['name','like','%Jhone%'],
        ['family','<>','Doe'],
    ],
    'account' => [
        ['account_name','=','test_account'], 
        ['account_city','like','%test_city%'],
    ]
];

And the function:

public function getContacts(Request $request, array $filters)
{
    $query = Contact::query();

    foreach ($filters as $key => $constraints) {
        $query->whereHas($key, function ($q) use ($constraints) {
            if (count($constraints) > 0) {
                $q->where($constraints[0][0], $constraints[0][1], $constraints[0][2]);
            }
            for ($i = 1; $i < count($constraints); $i++) {
                $q->orWhere($constraints[$i][0], $constraints[$i][1], $constraints[$i][2]);
            }
        });
    }

    return $query->get();
}

This will always use OR for multiple constraints and not AND. Using AND and OR mixed would require a lot more sophisticated system.

Can you customize with this array. I want to pass array like this to getaccounts method `$classes=array(['User'=>['name','like','%Jhone%'],['family','<>','Doe']],['Account'=>['account_name','=','test_account'],['account_city','like','%test_city%']])`; — paranoid, Jun 23 '18 at 07:22
@paranoid I added an example of what you requested. Please be aware that I changed the filter input slightly because yours would not work. — Namoshek, Jun 23 '18 at 07:40
@Namoshek Thanks too and one question: in your last code example between IF and FOR needed ELSE? Like this: if (count($constraints) > 0) {..} ELSE {for($i=1..)..} ? — Vit, Apr 28 '20 at 12:47
No, you want both. The reason we have two different statements is that you need `OR` concatenation. The `if` will add the first where condition (no leading `OR`) while the `for` loop will add the other conditions with `OR` concatenation. — Namoshek, Apr 28 '20 at 12:54

dynamic query laravel eloquent with whereHas

1 Answers1