当X < - > Y是多对多时，查询Eloquent模型X并按模型Y的属性排序

Question

I'm having two Eloquent models, one called Company and one called Discounts and both are releated to each other by a many-to-many relation. (Meaning one company can have multiple discounts and one discount can be valid for multiple companies.)

The Eloquent (Laravel 5.1) based models are defined as following (simplified):

class Discount extends Model {
    protected $table = 'discounts';
    public function companies() {
        return $this->belongsToMany('App\Company');
    }
}

class Company extends Model {
    protected $table = 'companies';
    public function discounts() {
        return $this->belongsToMany('App\Discount');
    }
}

Each company also has a longitude and a latitude column and I am aware of the coordinates of the customer. Finding the nearest companies to the customer would work easily with some trigonometry and ORDER BY.

However I want to find all the discounts of companies near to the customer. So I need to query discounts table, join with the companies and order the discounts based on the distance of the companies. I would have an idea how to formulate that query with an raw SQL statement, however I don't have any idea how to get that working with Eloquent, which is required since I want to handle the models in my Laravel based PHP web application.

Answer 1

Found a working solution myself:

$discounts = $this->discount->select('discounts.*')
            ->leftJoin('company_discount', 'discounts.id', '=', 'company_discount.discount_id')
            ->leftJoin('companies', 'companies.id', '=', 'company_discount.company_id')
            ->orderByRaw("((companies.longitude - $customerLongitude)^2 + (companies.latitude - $customerLatitude)^2) ASC")
            ->get();