Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

mg983's avatar
Level 4

Trying to write this query that self joins using the query builder

I'm trying to write:

SELECT a.id FROM posts a INNER JOIN ( SELECT DATE(timestamp) date, MAX(likes) max_likes FROM posts GROUP BY DATE(timestamp)) b ON DATE(a.timestamp) = b.date AND a.likes = b.max_likes

The inner join is simple, it's:

$inner = DB::table('posts as b')
                   ->selectRaw('date(timestamp) date')
                   ->groupBy(DB::raw('date(timestamp)'));

I'm just not sure how to get started on the outer...

$outer = DB::table('posts as a')
               ->selectRaw('a.id')
               ->join($inner)
               ->whereRaw(['DATE(a.timestamp) = b.date'], ['a.likes = b.max_likes']);
0 likes
5 replies
mg983's avatar
Level 4

The application I am using expects a query builder object response. I didn't write the app, just trying to play nice with it.

jlrdw's avatar

Just use getPdo() for something like that.

rsands's avatar

Are you trying to get the max number of likes and the date that occured for each post ID?

Please or to participate in this conversation.