Laravel 4 Eloquent Query Builder - Complicated joins with variable

Question

I'm trying to replicate a join like so using the laravel query builder:

LEFT JOIN content_userdata
ON content_id = content.id
AND user_id = $user_id

I have discovered that I can do additional "ons" using the following function in my model which extends Eloquent

public function scopeJoinUserData($query, $user_id)
{
    return $query->leftJoin('content_userdata', function($join)
    {
        $join->on('content_userdata_content_id', '=', 'content.content_id')->on('content_userdata_user_id', '=', 10);
    });
}

But this creates two problems. Firstly I cannot get the $user_id variable into the function and secondly even if I hardcode it for testing purposes as I have done above (to int "10") Laravel encases it in ` meaning that it is interpreted as a column name when it shouldn't be, like so:

left join `content_userdata`
on `content_id` = `content`.`id`
and `user_id` = `10`

So now I have two problems.

1. I cannot get the $user_id into the join function when using query scopes
2. Even if I could I cannot send a variable to the join since it always interprets it as a column name

Why would I want to do this? I realise one response may be to place it in a where. However I am trying to do it this way as the join may not necessarily return any results (hence the left join), since the content_userdata table contains things like a users rating for a piece of content. If I use a where then results with nothing in the content_userdata table will not be returned, where as if I can put it in the join then they will be returned due to the left join.

Is there anyway to achieve this in Laravel and if not what are the alternatives, obviously completely changing ditching Laravel is over the top but the only alternative I can think of is to get the userdata in a separate query.

Answer 1

You need to pass the variable to the closure using the use keyword - which imports the variable into scope. Example:

public function scopeJoinUserData($query, $user_id)
{
    return $query->leftJoin('content_userdata', function($join) use ($user_id)
    {
        $join->on('content_userdata_content_id', '=', 'content.content_id')
             ->on('content_userdata_user_id',    '=', DB::raw($user_id));
    });
}

This is a PHP syntax related issue and not a Laravel limitation!