Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

andrecuellar's avatar

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

  1. I need "franchises" as a final result, not customers
  2. 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

  1. some franchises do not have any customer, I need all.
  2. 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```
0 likes
9 replies
Sinnbeck's avatar

When adding your own selects in relationships, always remember to include the ID! Otherwise it cannot match the models

1 like
andrecuellar's avatar

@Sinnbeck thanks for reply, I need all the data from the table, including the relationships, I can't put an ID to match because I need all of them, in that case, what would be the correct query?

The only thing I want is to be able to sort the customers by distance, but the distance is obtained by the formula indicated above by using ->select(DB::raw($formulaToGetNearestLocation))

andrecuellar's avatar

@Sinnbeck this is the complete query

->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'))

I get distance by this, distance is not a raw in my table

andrecuellar's avatar

@Sinnbeck yes, and I get the same error TypeError mb_strpos(): Argument #1 ($haystack) must be of type string, Closure given

at the last line, })->get();

Sinnbeck's avatar
Sinnbeck
Best Answer
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.