Level 24
What's your Laravel version?
Aug 10, 2018
10
Level 1
Doing a correlated query in a subquery
I'm trying to accomplish the following in Eloquent:
SELECT post_id,id,(SELECT browser from visits as v1 WHERE v1.post_id = v.post_id group by browser ORDER BY count(browser) DESC limit 1), count(post_id) as n FROM `visits` as v group by post_id
And I have the following code in the query builder
$item = visitsModel::select('*', DB::raw('count(post_id) as n'))->with('posts')->orderBy('post_id')->groupBy('post_id');
But I can't figure out how subqueries work in Eloquent
Level 1
5.6
Level 24
You can use selectSub():
$items = visitsModel::select('id', 'post_id', DB::raw('count(post_id) as n'))
->selectSub(function($query) {
$query->select('browser')
->from('visits as v1')
->whereColumn('v1.post_id', 'v.post_id')
->groupBy('browser')
->orderByRaw('count(browser) desc')
->limit(1);
}, 'browser')
->from('visits as v')
->groupBy('post_id')
->orderBy('post_id')
->with('posts')
->get();
2 likes
Level 1
And the a last question, how do I assign an alias to the outer query in the query builder? Given the above code, I've got FROM visits as v
Level 24
I updated my answer.
Level 1
The browser is being returned as null when I dd($item)
Level 1
The error is somewhere in your where->('v1.post_id', 'v.post_id') in the sub query
Level 1
Ok, figured out the error. Update your answer to whereRaw->('v1.post_id = v.post_id') inside your subquery, I'm gonna accept it anyways because you helped me a lot :)
Level 24
I fixed that in my answer. It's better to use whereColumn() than whereRaw().
1 like
Level 1
Even better, thanks for your time!
Please or to participate in this conversation.