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:

FROM patterns
ON = 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
        ->leftJoin('toys',function($join) use ($userid){

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 *