Join morph relationship

So I came across this weird situation: I was attempting to join two tables via their morphMany relationship. My code is the following:

Lead::leftJoin('campaignees', function($join) {
                $join->on('campaignees.campaignable_type', '=', 'Lead')
                    ->on('campaignees.campaignable_id', '=', 'leads.id');
            })
            ->get();

I ran the query and got an interesting result:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Lead' in 'on clause' (SQL: select * from `leads` left join `campaignees` on `campaignees`.`campaignable_type` = `Lead` and `campaignees`.`campaignable_id` = `leads`.`id` where `leads`.`deleted_at` is null)

So I know what's going on here, but I don't know why. I would like to use the content of the column in my join, but that seems to be impossible. Can someone tell me whether or not this is a bug and if not, how I can fix it?

jakeryansmith

10 years ago

Level 8

I'm not sure this is a bug, you are trying to join on a column "Lead" which doesn't exist. You need to do a where clause to limit the scope to records where type equals "Lead".

pmall

10 years ago

Level 56

You can join on columns, not on values

Lead::leftJoin('campaignees', function($join) {
    $join->on('campaignees.campaignable_id', '=', 'leads.id');
})
->where('campaignees.campaignable_type', '=', 'Lead')
->get();

Shorter :

Lead::join('campaignees', 'campaignees.campaignable_id', '=', 'leads.id')
    ->where('campaignees.campaignable_type', '=', 'Lead')
    ->get();

Why not just :

Lead::with('campaignees')->get();

1 like

ixudra

10 years ago

Best Answer

Level 4

@pmall because this is Laravel 4.2 and lazy loading morph relationships does not work. As far as I know it also doesn't work in Laravel 5, but I could be wrong.

Anyways, your solution was almost right, the solution I was looking for was the following:

Lead::leftJoin('campaignees', function($join) {
    $join->on('campaignees.campaignable_id', '=', 'leads.id');
    ->where('campaignees.campaignable_type', '=', 'Lead')
})
->get();

Maybe that's what @jakeryansmith meant with his comment, but I'm not sure :-). Anyways, problem solved.

3 likes

Ace

10 years ago

Level 7

Hi I have a similar schema in mind and would appreciate the feedback Not sure how to design the model/relationship structure. Should I have a Model for each Goal?: TrackingCode and set a trackable property for each goal type? Visit Lead Registration Conversion Because then I fear I will have a multiple join problem when generating reports. here is the funnel I have in mind: http://snag.gy/LcBXq.jpg

thanks

Please or to participate in this conversation.