Laravel get related columns with custom query-CodePudding

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": "[email protected]",
                "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!

CodePudding user response：

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.