How to simplify laravel query?

Question

Laravel

$items = DataRelationship::where('link_id', $link->id)->get()->filter(function($user) {
                return (isset($user->user));
            })->sortByDesc(function($item) {
                return $item->club->where('status', 1)->count();
            })->take(10)

This query shows the data and sorts by large quantities.
There are 75,000 data in the database and it takes almost 5 minutes. How can I simplify this option so that it loads them faster

Answer 1

Because it is necessary to do one request normally.
And you generally select everything that is possible ->get ()
And then filters begin and more subqueries, at least one for each row that was initially found in the database, one at a time. Those. 75,000 output requests, and maybe even 2 times more, taking into account the first filter.
Read the doc for with(), orderBy() and probably leftJoin() and make one normal request.

Answer 2

DataRelationship::select(['id', 'name', etc...])->whereLinkId($link->id)->whereHas('user')
            ->addSelect(
                DB::raw( '(select count(id) from clubs where data_relationships.id = clubs.data_relationship_id) as clubs_count' )
            )->orderBy('clubs_count', 'desc')->get();

Something like this. In DB::raw change to your sql, I don't know what tables and keys you have.

Answer 3

Write a pure SQL query first and execute it directly, not from PHP. That's when you have it and will be executed for the time that suits you and will return exactly what you need, then we'll think about how to shove it into the ORM.
And so .. an empty lesson in your case.