How to make sure that the total number of nested resources does not exceed a certain number?

Question

Laravel

There is a query that selects auctions and museums closest to the user

$query = Location::select(DB::raw("location.id, location_label as location, latitude, longitude,
            (6371 * acos( cos( radians($lat) )
            * cos( radians( latitude ) )
            * cos( radians( longitude ) - radians($lng) ) + sin( radians($lat) )
            * sin( radians( latitude ) ) ) )
            AS distance"));

$query->orderBy('distance');

$query = $query->with('auctions:id, location_id')->with('museums:id, location_id');

return $query->get();

As a result of the query, we get

[
    {
        "id": 2,
        "location": "Milan",
        "latitude": "51.5073509",
        "longitude": "-0.1277583",
        "distance": 0,
        "auctions": [
            {
                "id": 2,
                "title": "Тайтл",
                "location_id": 2
            }
        ],
        "museums": [
            {
                "id": 2,
                "name": "Museum",
                "location_id": "2"
            }
        ]
    },
    {
        "id": 3,
        "location": "London",
        "latitude": "51.5073509",
        "longitude": "-0.1277503",
        "distance": 0.0005453617665334611,
        "auctions": [],
        "museums": [
            {
                "id": 1,
                "name": "Museum",
                "location_id": "3"
            }
        ]
    }
]

It is necessary that the total number of auctions and museums does not exceed 4.
Ie . on the front it will list:
- Auction1. Distance 1km.
- Museum1. Distance 1km.
- Museum2. Distance 1.5 km.
- Auction2. Distance 1.5 km.
And you need to organize pagination of 4 pieces per page.
Whether it is possible to rewrite the given request somehow more optimally under a task in view?
Or foreach'em to walk and count the total number of auctions and museums? And if the offset is 10,000, it turns out that first you need to go through these 10,000 auctions / forich museums in order to determine the next 4 entries, such a solution, in my opinion.

Answer 1

I can't understand the meaning of Location...
Why are museums and auctions tied to a city? Those. if I am in London, then all London museums are at a distance of 1.5 km from me, or what? If you need the distance to the user, then store your coordinates for each museum and auction. The auctions and museums themselves are in the same table (name, type, coordinates). Distinguish by type. If they have very different fields, then you can store them in different tables like this: https ://laravel.com/docs/6.x/eloquent-relationship...
ten.