Grayscale profile picture

Patrique Ouimet

Developer

COVID-19 Stats

Laravel Query Tricks

Tue, Mar 12, 2019 9:05 PM

Intro

A quick post on some of my favourite Laravel query tricks

Where In With a Subquery

DB::table('users')
    ->whereIn('id', function ($query) {
        return $query->from('posts')
            ->select('user_id')
            ->where('topic', 'laravel');
    })->get();

Resulting SQL:

select * from users where id in (
    select user_id from posts where topic = 'laravel'
);

Sub Select

DB::table('users')
    ->select('*')
    ->selectSub(
        DB::table('posts')
            ->select('title')
            ->whereRaw('posts.user_id = users.id')
            ->latest()
            ->limit(1),
        'last_article_title'
    )->get();

Resulting SQL:

select
    *,
    (
        select title from posts
            where posts.user_id = users.id
            order by created_at desc
            limit 1
    ) as last_article_title
from users;