How to do dynamic eager loading based on the value of one of the model's properties?

Question

Let's say we use the same table schema for saving purchased products of orders for website A and B. Databases for A and B locate in different servers. So 2 database connections are defined.

CREATE TABLE `order_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL DEFAULT '0',
  `product_id` int(11) NOT NULL DEFAULT '0',
  # other columns...
);

And we are building an application for analysing all orders in different platforms. We have imported and saved the data in another summary table, schema being like this

CREATE TABLE `summary_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL,
  `source` varchar(15) NOT NULL DEFAULT 'site_a', # 'site_a', 'site_b'
  # other columns...
);

To access purchased products in different platforms, we define 2 identical classes but in different namespaces:



To fetch purchased products of orders, in SummaryOrder class, we define a relation products:
class SummaryOrder extends Model
{
    protected $sourcesToConnections = [
        'source_a' => 'connection_a',
        'source_b' => 'connection_b'
    ];
    
    /**
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function products()
    {
        $namespace = sprintf("Path\To\Class\%s", ucfirst($this->source));
        $class = "$namespace\OrderProduct";
        // $this->source has no value here when calling with()
        $connection = $this->sourcesToConnections[$this->source];
        return $this->setConnection($connection)->hasMany($class, 'id', 'order_id');
    }

    //...
}

Then we want to fetch all data from summary_orders like so
$orders = SummaryOrder::query()
    ->with(['products:product_id,order_id'])
    ->get()
    ->map(function ($order) {
    // prepare more stuff
    });

The above call is gonna fail because when Laravel is building a query and calling products() in SummaryOrder, there is in fact no SummaryOrder instance hence $this->source has no value. So we end up with undefined index error when accessing $sourcesToConnections: $this->sourcesToConnections[$this->source].
A "solution" is dont-do eager loading at the cost of performance:
$orders = SummaryOrder::query()
    ->with(['products:product_id,order_id'])
    ->get()
    ->map(function ($order) {
    // prepare more stuff
        $order->products;
    });

Apparently this is not ideal. So my question is, how can I keep SummaryOrder::products() as flexible as possible, but also keep performance gain with() gives me?

user_9556193 · Answer

Edit Thinking further on this, I'm not sure your approach is possible because eager loading executes a WHERE IN (...) on the id of the related table. Since your relation is split between databases, it's 1) possible to have duplicate primary keys in the order_products tables of A and B, and 2) the the WHERE IN (...) clause wouldn't have anyway to know which value belongs to which table of A or B.

The closest answer I can come up with is to actually split the product relation into separate ones for each "source".

public function productsA()
{
    $connection = 'connection-to-a';
    return $this->setConnection($connection)->hasMany('Path\To\Class\SiteA\OrderProduct', 'id', 'order_id');
}

public function productsB()
{
    $connection = 'connection-to-b';
    return $this->setConnection($connection)->hasMany('Path\To\Class\SiteB\OrderProduct', 'id', 'order_id');
}


$orders = SummaryOrder::query()
    ->with([
        'productA:id,product_id,order_id',
        'productB:id,product_id,order_id'
    ])
    ->get()
    ->map(function ($order) {
        // prepare more stuff
    });

Note the addition of id to the eager loading above. The docs on eager loading specific columns state:

When using this feature, you should always include the id column and any relevant foreign key columns in the list of columns you wish to retrieve.

Obviously not as efficient or clean as a single dynamic relation, but it does avoid the N+1 problem you're facing at the cost of 4 queries vs 2. Interesting question, I'll be looking into this some more!

There is one caveat for anyone who wants to adopt this method: If order IDs are likely to be identical in more than one source, you have to manually handle dataset like so:

$orders = SummaryOrder::query()
    ->with([
        'productA:id,product_id,order_id',
        'productB:id,product_id,order_id'
    ])
    ->get()
    ->map(function ($order) {
        $data = $order;

        if ($order->source === 'site_a') {
            $data['products'] = $order->productA
        }

        if ($order->source === 'site_b') {
            $data['products'] = $order->productB;
        }

        unset($data['productA']);
        unset($data['productB']);

        // do other things
        return $data;
    });

That said, if you are assured that order IDs are unique across sources, you can safely ignore the change above.

user_1220364 · Answer

How about Lazy eager loading

$orders = SummaryOrder::query()->get();

//Lazy eager loading - doesn't create N+1 problem
//executes just 1 query just like with()
$orders->load(['products:product_id, order_id']);

$orders->map(function ($order) {
    // prepare more stuff
        $order->products;
    });

For eg: lets say we have:

Post belongsTo User (author)

Post hasMany Comment

both way relationships are defined in respective Model classes

Let's test in a route with closure function - welcome route


Route::get('/', function (Request $request) {
    $posts = Post::with('author')->get();
    $posts->load('comments')->map(function ($post) {
        // dump($post->author->name);
    });

    return view('welcome');
});

The queries as seen in debug bar

How to do dynamic eager loading based on the value of one of the model's properties?

2 Answers