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!

Answer 2

In the accepted answer, just adding quotes around the DB::raw part of the query will not fully protect it from sql injection. Just pass some quotes in your user_id and see. To parameterize you can do something like this:

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',    '=', DB::raw('?'));
        }
    ->setBindings(array_merge($query->getBindings(),array($user_id)));
}

Notice in this example that you don't have to pass the variable into the closure. Alternatively you could try and write this part completely raw.

UPDATE: Taylor added joinWhere, leftJoinWhere... if you have a function join just use ->where and ->orWhere from within the Closure.

Answer 3

I managed to fix this myself, there's a note at the bottom of why it's not completely optimal but here's how to do it anyway:

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.'"'));
    });
}

Note the use of "use ($user_id)" as suggested by @Half Crazed.

DB::raw() is used to wrap $user_id in quotes even though it's an integer and not a string. This will stop Laravel automatically using ` which makes it MySQL interpret it as a column name.

Performance: One thing to note is that MySQL queries can be considerably faster when using an integer rather than a string and will interpret it as a string if it's wrapped in quotes. I'm not worrying about that for now, but I figured I should mention it if others are using this as a solution.

Answer 4

Why dont you just use relationships? That is the whole point of an ORM like Eloquent?

Something like this;

class User extends Eloquent {
    public function userdata()
    {
        return $this->hasOne('Userdata');
    }
}

$result= User::find(1)->userdata();

edit to show you can do whatever you want with relationships

Option 1:

$place = new Place;

$array = $place->with(array('users' => function($query)
{
    $query->where('user_id', $user_id);
}))->get();

var_dump($array->toArray());

or Option 2:

$place = new Place;

$array = $place->with('users')->where('user_id', $user_id)->get();

var_dump($array->toArray());

Both give different results - but you get the idea

Answer 5

Your first problem: You should use PHP syntax for closure as the answer of Half. About your second problem, I think the part AND user_id = $user_id of the query does not belong to a JOIN clause but a WHERE clause because it just depends on one table, not both in this joining relationship. I think you should use a subquery like this:

public function scopeJoinUserData($query, $user_id)
{
    return $query->leftJoin(\DB:raw("(SELECT * FROM content_userdata WHERE user_id = {$user_id}) AS t"), function($join)
    {
        $join->on('t.content_id', '=', 'content.content_id');
    });
}

However, as you see, let be sure that the $user_id variable is safe because we use \DB:raw method.