Lukas Lukas - 3 months ago 19
MySQL Question

Eloquent where over multiple tables at once with a nested json result like 'with' does

I need to filter a list of immigrants based on possibly many parameters (like their religion, name, current address and so on) but the data is stored in multiple tables (religions have their own table for example). I would also like to use the 'with' function to get a nested json output like:

{
"id": 5,
"birthdate": "1996-08-13",
"person": {
"name": "Lorem",
"street": "Ipsum"
}
...
}


In addition to that I don't know the exact filters, because they are included in the request json (column, operator, value and maybe table if needed), so I can not hardcode them.

I am currently doing a foreach over the filters and tried to use nested whereHas with no success.
I tried the join functionality of the builder, and it worked but the json was not nested.

return Migrant::with('Person.Ort', ...(many other tables))->whereHas('Person', function($q) use($filter) {
foreach ($filter as $key => $value) {
$q->whereAnd($key, $value['operator'], $value['value']);
}
})->get();


This works, but as you would guess only for filters involving the 'Person' table and not one of the possible other tables.

Is it possible to do something like this with eloquent?

EDIT:
For clarification, I want to do a query like this:

SELECT * FROM Migrant INNER JOIN Persons ON Migrant.id = Persons.id INNER JOIN Countries ON Migrant.country = Countries.id INNER JOIN ... WHERE Persons.name = "Lorem" AND Countries.country = "Disneyland" AND ...

Answer
return Migrant::with([
'Person.table1'=>function($query) use($filter){
    $query->where($filter['table1']['key'], $filter['table1']['operator'], $filter['table1']['value']);
},
'Person.table2'=>function($query) use($filter){
    $query->where($filter['table2']['key'], $filter['table2']['operator'], $filter['table2']['value']);
}])->get();

Update:

$mainQuery = Migrant::with('Person.Ort', ...(many other tables));

if($filter['table1']){
    $mainQuery = $mainQuery->whereHas('table1', function($q) use($filter) {

        $q->whereAnd($filter['table1']['key'], $filter['table1']['operator'], $filter['table1']['value']);

})
}
.
.

return $mainQuery->get();

Update 2:

$mainQuery = Migrant::with(['Person.Ort', 'Land', 'Herkunft', 'Staat', 'Erfasser', 'Konfession', 'Betreuer', 'Aufenthaltsstatus', 'Arbeitserlaubnis']);

foreach($filter as $table=>$value){
    $mainQuery = $mainQuery->whereHas($table, function($q) use($value) {
        $q->where($value['key'], $value['operator'], $value['value']);
    })
}
return $mainQuery->get();
Comments