Advanced query with Eloquent and Query Builder

I'm trying to put together a query using Eloquent, but it's proving to be quite difficult due to the fact that polymorphic relations are involved, many of the smart eloquent methods for easy queries aren't supporting that.

As a quick overview, I have a Package model, with a polymorphic one-to-many packageable relation. These packageables all have bookings, and the bookings relationship is also a polymorphic relationship.

I'm trying to run the following query:

Package::with(['packageable' => function($q) use($bookingDateStart,     $bookingDateEnd) {
            $q->whereHas('bookings', function($q) use($bookingDateStart, $bookingDateEnd) {
                    $q->whereBetween('start_time', [$bookingDateStart, $bookingDateEnd]);
            }, '<', 1);
        }])->get();

So, as you can see, I'm getting all packages, with the eager loaded relations. The problem, comes when i need to try and constrain the eager loaded packageable relationship. This relationship is a polymorphic one, and Laravel therefore can't find the bookings relationship on the packageable instance.

What i want, is, as the code states, to only eager load the instances that doesn't have a booking within the given starting time.

So, i tried this out instead:

Package::with(['packageable' => function($q) use($bookingDateStart,     $bookingDateEnd) {
            $q->whereNotExists(function($subq) use($bookingDateStart,     $bookingDateEnd) {
              $subq->select(\DB::raw(1))->from('bookables')
              ->join('bookings', 'bookables.booking_id', '=', 'bookings.id')
              ->whereRaw('bookings.bookable_id = packages.package_id')-    >where('bookable_type', 'packages.package_type')
->whereBetween('start_time', [$bookingDateStart, $bookingDateEnd]);
});
}])

But for some reason Laravel doesn't constrain the query at all it seems, when it's a polymorphic relation.

So I'm kinda stuck. Does anyone have any idea about how I could get the wanted data with Eloquent, or will i have to resolve to the query builder?

How would you go about tackling this?

cbojer

11 years ago

Best Answer

Level 2

So what i ended up doing, was eager loading the polymorphic relationship manually. Instead of using the with method, i would get all the Packages, and their id and type. I would then filter through the info and create a new query for each model type and use the whereIn method to only load the models that met the Package queries expectations. The queries performed are basically the same that Laravel uses, the only difference, is that I'm now able to constrain the eager loaded relationship.

In case anyone is interested or happens to come across the same problem, here is an example:

 $packages = Package::all();

// First we group by type. This is prep for using the lists method. You'd think we could use that right at the beginning, but we can't use the type as the key, since that would result in each subsequent item of the same relation overriding the last one. We can't use the id either, since packages of different types could easily have the same id. So we group them by type first.
    $packages = $packages->groupBy('package_type');
// Then we loop through all the package types, with the type being the key.
    $packageTypes = [];
    foreach($packages->all() as $type => $packages) {

        $collection = new Collection($packages);
//  Now we get the list of id's
        $packageIds = $collection->lists('package_id');
// And store them with the type holding an array of it's corresponding ids
        packageTypes[$name] = $packageIds;
    }

    $packageResults = new Collection();
 // We loop through the types and create a new model for each one.
    foreach($this->packageTypes as $name => $ids) {
        $model = new $name;

// We get model instances that match our id, and here you can scope/constrain the query all you want for your eager loaded models. To end, i push the results onto a new collection.
        $packageResults->push($model->whereIn('id', $ids)->available($bookingDate)->get());
    }

1 like

Please or to participate in this conversation.