Laravel eloquent - Filtering subquery inside with statement

Question

I have the following query:

 $countries = Country::where('code', '=', $code)
 ->with(array('cities.details' => function ($query) use ($user) {
 $query->where('cities.name', '=', 'This is a city name');
 }))->first();

Lets see the example: I have three tables, Country, City and CityDetails. I want to get all countries, then get all cities (INCLUDING the details information) but I also want to filter cities by name and fetch details table which belongs to the city table.

If I want to use with to get the child and another table using with(cities.details), how can I filter using CITIES attributes?

The main question is: How can I fetch two tables in a with statement like secondTable.OtherTable and filter the query using secondTable attributes?

Just for make it clearer, if I use statement like this:

  $countries = Country::where('code', '=', $code)
 ->with(array('cities.details' => function ($query) use ($user) {
    $query->where('name', '=', 'This is a detail name');
    }))->first();

I can access only details table attributes. The question is: How can I access the city table attribute to filter inside a with a statement?

Thanks in advance

Lucas Santos · Accepted Answer · 2019-02-20 23:37:58Z

1

I just found a solution. Basically, I should apply the filter for city table and then call wit function on the subquery. This code solved my problem:

    $countries = Country::where('code', '=', $code)
 ->with(array('cities' => function ($query) use ($user) {
    $query->where('name', '=', 'San Francisco')->with('details');
    }))->first();

Note that I called with('details') on city only after filter in subquery.

answered Feb 20, 2019 at 23:37

Lucas Santos

3,2815 gold badges22 silver badges31 bronze badges

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

Comments

Emily · Accepted Answer · 2019-02-21 01:05:09Z

The most straightforward one is using join query:

(new Country)
    ->join('cities', 'cities.id', '=', 'countries.city_id')
    ->join('city_details', 'cities.id', '=', 'city_details.city_id')
    ->where('cities.name', 'TheName')
    ->where('city_details.info', 'info')
    ->select('...');

But the result has only one level. So,

$query = (new Models\Country())
    ->with([
        'cities' => function ($query) {
            $query->where('name', 'xxx')->with([
                'details' => function ($query) {
                    $query->where('info', 'info');
                }
            ]);
        }
    ]);

$result = $query->get()->filter(function ($item) {
    return count($item['cities']);
});

The result gives countries with empty cities. So use Laravel collection to filter in the last.

Collectives™ on Stack Overflow

Laravel eloquent - Filtering subquery inside with statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related