1

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!

1
  • Put the database strcuture first with some dummy data preferably. Then show what you are expecting. Then show what results you are getting currently now. Right now, there are many parts missing and people will have to guess in order to answer. Commented Jan 5, 2023 at 2:48

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.