Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 2 years ago.
0

So far I have the following solution which seems to work, however there is still some raw SQL which I would like to abstract some more;

$projects = Project::with('issues')
			->selectRaw('(SELECT COUNT(*) FROM issues i WHERE i.project_id = projects.id AND i.created_at >= '.$end_week.') AS issueCount')
			->orderBy('issueCount', 'DESC')
			->take(5)
			->get();
0

It seems to me that you do not need the issues, only their count, when you use "with" you are loading the models associated with each project.

Let me rewrite your query:

SELECT
    *
FROM
	projects
	INNER JOIN
	( 
		SELECT project_id, COUNT(*) AS issue_count
		FROM issues	
		WHERE created_at >= curdate()-7
		GROUP BY project_id
    ) i ON i.project_id = projects.id
ORDER BY issue_count DESC
LIMIT 5

Now in Eloquent:

$issuesJoinQuery = Issue::select('project_id', DB::raw('COUNT(*) AS issue_count'))
	->whereRaw('created_at >= curdate()-7')
	->groupBy('project_id');

$projects = Project::select('*')
	->join(DB::raw('(' . $issuesJoinQuery->toSql() . ') i'), function ($join)
	    {
	        $join->on('i.project_id', '=', 'projects.id');
		})
	->orderBy('issue_count', 'desc')
    ->take(5)
	->get();
Last updated 9 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.