Laravel eloquent - Filtering subquery inside with statement

168
February 22, 2019, at 03:50 AM

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

Answer 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.

Answer 2

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.

Rent Charter Buses Company
READ ALSO
How to execute a Google Cloud Function through PHP?

How to execute a Google Cloud Function through PHP?

Is it possible to excute a Google Cloud Function and wait for it's results through PHP?

229
PHP Open URL Delay Target Blank

PHP Open URL Delay Target Blank

Please, I need help from an expert in PHPI have one PHP code that works fine, but I need to add some delay when URL is opened, so now I click and URL automatically instant open in the new tab, I need to add a delay in this code so when I click to open example...

172
Problem with encoding and parsing a php array

Problem with encoding and parsing a php array

I have a php file that validates a simple form entries for meI assign each error ( found during the validation to a variable ($name_errors, $phone_errors and $email_errors)

154