Laravel get related columns with custom query

Question

So I have a function to get all companies in range based on longitude and latitude. Now I want to get te related columns with the query, but I do not know how.

This is the code how I am getting the companies in the area:

$companies = DB::table('companies')
            ->selectRaw("id, owner, company_info, address, latitude, longitude,
                         (6371 * acos( cos( radians(?))
                         * cos( radians(latitude) )
                         * cos( radians( longitude ) - radians(?))
                         + sin( radians(?) )
                         * sin( radians(latitude)))) AS distance", [$requestedCords['latitude'], $requestedCords['longitude'], $requestedCords['latitude']])
            ->having("distance", "<=", $requestedCords['radius'])
            ->orderBy("distance",'asc')
            ->offset(0)
            ->limit(20)
            ->get();

In my company model I have noted my relations like this:

public function owner(){
        return $this->belongsTo(User::class, 'owner', $this->primaryKey);
    }

    public function address(){
        return $this->hasOne(Address::class, $this->primaryKey, 'address');
    }

    public function companyInfo(){
        return $this->hasOne(CompanyInfo::class, $this->primaryKey, 'company_info');
    }

This is the response what I am getting from the code above:

{
            "id": 2,
            "owner": 1,
            "company_info": 2,
            "address": 1,
            "latitude": 52.37304046,
            "longitude": 5.244694307,
            "distance": 0
        },

This is the response what I want

{
            "id": 23,
            "owner": {
                "id": 9,
                "firstname": "kees",
                "lastname": "piet",
                "email": "piet@test.com",
                "email_verified_at": null,
                "phone": null,
                "total_apmnt": 0,
                "no_show": 0,
                "total_shows": 0,
                "last_time_payed": null,
                "created_at": "2022-12-05T19:09:24.000000Z",
                "updated_at": "2022-12-05T19:09:24.000000Z"
            },
            "company_info": {
                "id": 6,
                "name": "hetro",
                "kvk": "2234",
                "phone": 459594,
                "type": "massage",
                "created_at": "2022-12-05T18:45:31.000000Z",
                "updated_at": "2022-12-05T18:45:31.000000Z"
            },
            "address": {
                "id": 4,
                "country": "nederland",
                "state": "gebak",
                "postalcode": "7741DN",
                "street": "yummy",
                "place": "yumyium",
                "house_nmr": 143,
                "nmr_addition": null
            },
            "latitude": 52.67007374,
            "longitude": 6.735819476,
            "created_at": "2022-12-05T19:09:40.000000Z",
            "updated_at": "2022-12-05T19:09:40.000000Z"
        }

This bit of code is working fine. Now I want the owner, company_info and the address return the column/row that it's related to. But I really wouldn't know how. I've tried in many different ways to add ->with('relation'). But I cannot get it working.

I am also not that strong in sql and I am getting really confused by how you build these custom queries in laravel.

Or is there a way that I can get the related models and add the selectRaw query to it?

Thanks in advance!

Answer 1

You need to use your Eloquent model instead of the DB facade for the query.

$companies = Company::selectRaw("id, owner, company_info, address, latitude, longitude,
                         (6371 * acos( cos( radians(?))
                         * cos( radians(latitude) )
                         * cos( radians( longitude ) - radians(?))
                         + sin( radians(?) )
                         * sin( radians(latitude)))) AS distance", [$requestedCords['latitude'], $requestedCords['longitude'], $requestedCords['latitude']])
            ->with(['owner', 'address', 'companyInfo'])
            ->having("distance", "<=", $requestedCords['radius'])
            ->orderBy("distance",'asc')
            ->offset(0)
            ->limit(20)
            ->get();

And the with relations will join correctly because you have selected the foreign keys in the query already.