When adding your own selects in relationships, always remember to include the ID! Otherwise it cannot match the models
Jan 12, 2022
9
Level 1
How to do select(DB::raw()) in subquery
Hello, I have this query
return Customer::select(DB::raw(->select(DB::raw('*, ( '. 6371000 .' * acos( cos( radians(' . $this->latitude . ') )
* cos( radians( latitude ) ) * cos( radians( longitude ) - radians(' . $this->longitude . ') )
+ sin( radians(' . $this->latitude . ') ) * sin( radians( latitude ) ) ) ) AS distance'))))
->with('franchises')
->orderBy('distance')
->paginate(10);
I can order by "distance" using DB:raw
It works, but I have two problems with this
- I need "franchises" as a final result, not customers
- I need "customers" as a relation inside each franchise (is many to many)
this is my Franchise model
public function customers()
{
return $this->belongsToMany(Customer::class, 'franchise_customer', 'franchise_id', 'customer_id')
->using(FranchiseCustomer::class);
}
and this is my customer model
public function franchises()
{
return $this->belongsToMany(Franchise::class, 'franchise_customer', 'customer_id', 'franchise_id')
->using(FranchiseCustomer::class);
}
I need to have all my franchise table
- some franchises do not have any customer, I need all.
- For the franchises that have customers, I need to sort them by distance for each every customer (is many to many), it is a piece of information that I need to work later in my view
If I get this
return Franchise::with('customers')->get();
It works, but I need the "distance" data of each 'customer' with a subquery, how can I do it?
I tried with this
return Franchise::with('customers', function ($query) use ($formulaToGetNearestLocation){
$query->select(DB::raw($formulaToGetNearestLocation))
->orderBy('distance');
})->get();
but I get this error
mb_strpos(): Argument #1 ($haystack) must be of type string, Closure given```
Level 102
Just spotted the issue. Sorry. The syntax differs a bit from place to place. with() needs an array
return Franchise::with(['customers' => function ($query) use ($formulaToGetNearestLocation){
$query->select(DB::raw($formulaToGetNearestLocation))
->orderBy('distance');
}])->get();
1 like
Please or to participate in this conversation.