Laravel left outer join – sort of

Earlier this week, I added some functionality to the online game I’m building to have administrators be able to add a toy to a user’s collection. I was feeling proud of myself, until I realized that the script didn’t weed out toys the user already had, which would end up with duplications. So I had to figure out how to do that. Not being exactly sure how to describe what I wanted, I nevertheless entered a rather vague description into Google and hoped for the best.

Fortunately, other people have been trying to do what I was, and I was able to find some answers pretty easily. I was looking for a left outer join, basically, but with a condition. I wrote up some SQL and was able to get it to run in the terminal. It looked like this:

SELECT patterns.id, patterns.name
FROM patterns
LEFT JOIN toys
ON patterns.id = toys.pattern_id AND toys.user_id = 1
WHERE toys.pattern_id IS NULL

Contrary to my expectations, this was actually the easy part. The next challenge was converting it into Laravel. I followed a couple of examples from StackOverflow, but I kept getting an empty result, and I’m still new enough to Laravel that I am not sure how to dig into the query it’s actually generating. I was eventually able to make it work. The code in the pattern model:

public function scopeUnknownPatterns($query, $userid){
    return $query
        ->select('patterns.id','patterns.name')
        ->leftJoin('toys',function($join) use ($userid){
            $join->on('patterns.id','=','toys.pattern_id');
            $join->on('toys.user_id','=',DB::raw($userid));
        })
        ->where('toys.pattern_id','=',NULL)
        ->orderBy('patterns.name','asc');
}

Then, from the toy controller, I just use:

$patterns = Pattern::unknownPatterns($user->id)->get();

I was tripping up on the ->get() part, as the other scopes I’ve put into the model are limited and … for some reason I don’t really get yet don’t need it. One of the challenges of teaching yourself things is when you can’t actually answer your own questions!

Leave a Reply

Your email address will not be published. Required fields are marked *