with() does not work if I use where() in relations

Published 3 weeks ago by pilat

Hi, I've stumbled over this issue.

Laravel 5.3

Here's couple of examples:

class Activity extends Model
{
    public function custom_fields()
    {
        return $this->hasMany(\App\CustomField::class, 'element_id', 'element_id');
    }

    public function lead()
    {
        return $this->hasOne(\App\AmoLeadsCache::class, 'id', 'element_id');
    }
}

class Activity extends Model
{
    public function custom_fields()
    {
        return $this->hasMany(\App\CustomField::class, 'element_id', 'element_id')
            ->where('custom_fields.subdomain', $this->subdomain);
    }

    public function lead()
    {
        return $this->hasOne(\App\AmoLeadsCache::class, 'id', 'element_id')
            ->where('leads_cache.subdomain', $this->subdomain);
    }
}

Now, in client's code I do the following:

Activity::with('custom_fields', 'lead')->where(/*some other cond*/)->get();

It works as expected if I don't have ->where() in the "relations" methods. If i have, however, all relations are empty (lead=>null, custom_fields=>[]).

Anyone knows how to overcome this? I really need to preserve those ->where() clauses, but I also need eager loading.

BryceSharp

Can you include the ->where(/*clause*/) that is causing you problems?

robrogers3

humm, I can't reproduce this.

what happens if you do a ->where('1', '=', '1')? //always true

also do a dd(Activity::with('custom_fields', 'lead')->where(/some other cond/)->toSql()) and same with getBindings()

pilat
pilat
3 weeks ago (9,510 XP)

Ok, I made that client code up, in fact.

Here's the part of the same Activity class that does not work:

class Activity extends Model
{
    // ...

    public function fillData($template)
    {
        if (strpos($template, '{price}') !== false) {
            $replaceData['price'] = empty($this->lead->price) ? '0' : $this->lead->price;
        }
        if (strpos($template, '{url}') !== false) {
            $replaceData['url'] = empty($this->lead->url) ? '[no url]' : $this->lead->url;
        }
        if (strpos($template, '{name}') !== false) {
            $replaceData['name'] = empty($this->lead->name) ? '[no name]' : $this->lead->name;
    }

    // ...
}

Now goes the client code:

1. No eager loading:

>>> App\Entities\Activity::latest()->take(3)->get()->map(function ($a) { return $a->fillData('{name}: {url}'); });

=> Illuminate\Support\Collection {#1026
     all: [
       "Name 1: https://url1",
       "Name 2: https://url2",
       "Name 3: https://url3",
     ],
   }

2. With eager loading:

>>> App\Entities\Activity::with('lead')->latest()->take(3)->get()->map(function ($a) { return $a->fillData('{name}: {url}'); });

=> Illuminate\Support\Collection {#1028
     all: [
       ": ",
       ": ",
       ": ",
     ],
   }

3. Still with eager loading, but without "::where()" in relation functions (my very first example):

>>> App\Entities\Activity::with('lead')->latest()->take(3)->get()->map(function ($a) { return $a->fillData('{name}: {url}'); });

=> Illuminate\Support\Collection {#1026
     all: [
       "Name 1: https://url1",
       "Name 2: https://url2",
       "Name 3: https://url3",
     ],
   }
pilat
pilat
3 weeks ago (9,510 XP)

Listening to SQL queries:

>>> DB::listen(function ($query) { dump($query->sql); dump($query->bindings); dump($query->time); });
=> null
>>> App\Entities\Activity::with('lead')->latest()->take(3)->get()->map(function ($a) { return $a->fillData('{name}: {url}'); });
"select * from `activities` order by `created_at` desc limit 3"
[]
74.0
"select * from `leads_cache` where `leads_cache`.`subdomain` is null and `leads_cache`.`id` in (?, ?, ?)"
array:3 [
  0 => 4879927
  1 => 5219288
  2 => 5673393
]
13.18
=> Illuminate\Support\Collection {#1034
     all: [
       ": ",
       ": ",
       ": ",
     ],
   }

So, it looks for null subdomain for some reason…

But if there's no eager loading, it makes proper queries (just too many of them):

>>> App\Entities\Activity::latest()->take(3)->get()->map(function ($a) { return $a->fillData('{name}: {url}'); });
"select * from `activities` order by `created_at` desc limit 3"
[]
80.55
"select * from `leads_cache` where `leads_cache`.`id` = ? and `leads_cache`.`id` is not null and `leads_cache`.`subdomain` = ? limit 1"
array:2 [
  0 => 4879927
  1 => "mysubdomain"
]
8.77
"select * from `leads_cache` where `leads_cache`.`id` = ? and `leads_cache`.`id` is not null and `leads_cache`.`subdomain` = ? limit 1"
array:2 [
  0 => 5219288
  1 => "mysubdomain"
]
1.31
"select * from `leads_cache` where `leads_cache`.`id` = ? and `leads_cache`.`id` is not null and `leads_cache`.`subdomain` = ? limit 1"
array:2 [
  0 => 5673393
  1 => "mysubdomain"
]
0.75
=> Illuminate\Support\Collection {#1028
     all: [
       "Name 1: https://url1",
       "Name 2: https://url2",
       "Name 3: https://url3",
     ],
   }
BryceSharp

You are assuming that the instance of Activity calling fillData() has the scope of the eager loaded lead. It does not. The below code should fix your errors.

public function fillData($template)
    {
    $lead = $this->lead();
        if (strpos($template, '{price}') !== false) {
            $replaceData['price'] = empty($lead->price) ? '0' : $lead->price;
        }
        if (strpos($template, '{url}') !== false) {
            $replaceData['url'] = empty($lead->url) ? '[no url]' : $lead->url;
        }
        if (strpos($template, '{name}') !== false) {
            $replaceData['name'] = empty($lead->name) ? '[no name]' : $lead->name;
    }
pilat
pilat
3 weeks ago (9,510 XP)

@BryceSharp thank you. It looks similar to my current workaround.

However, I loose eager loading this way. So, if I have, say, 500 activities in the list, I'll execute 500 extra SQL queries to fill them all.

You are assuming that the instance of Activity calling fillData() has the scope of the eager loaded lead.

Yes, this is what bothering me. Why is it exactyl there is no that scope when I use ->where() in the relation functions, but the scope is present when I don't use ->where()? Is there a way I could preserve both the extra constraint in relations AND the eager loading?

pilat
pilat
2 weeks ago (9,510 XP)

Ok. What I understand at this moment is that there has to be an option to use ::withColumn() in the eager-loading query builder.

Something like this:

    public function custom_fields()
    {
        return $this->hasMany(\App\CustomField::class, 'element_id', 'element_id')
            ->whereColumn('custom_fields.subdomain', 'activities.subdomain');
            // or simpler: ->whereColumn('subdomain', 'subdomain');
    }

    public function lead()
    {
        return $this->hasOne(\App\AmoLeadsCache::class, 'id', 'element_id')
             ->whereColumn('leads_cache.subdomain', 'activities.subdomain');
    }
    // this doesn't work, off cause…

At the moment, I'll stick to the custom made local scopes, like this one:

    public function scopeWithLead($query, $subdomain)
    {
        return $query
            ->with([ 'lead' => function ($query) use ($subdomain) {
                $query
                    ->where('subdomain', $subdomain);
            }]);
    }

, although I don't find it very DRY having to repeat that $subdomain constraint on both the Activitiy and its relations:

// finally, the client code:
\App\Entities\Activity::subdomain('subdomain1')->withLead('subdomain1')->take(3)->get();
// don't like that repetition of subdomain constraint however… :/

Please sign in or create an account to participate in this conversation.