wizjo
1 year ago

Search method which queries by nested relationship fields

Posted 1 year ago by wizjo

Im trying to prepare search method which will get Investors from database quering by nested relationship fields.

InvestorController.php::

    public function search(InvestorSearchRequest $request) {
        $investors = User::with('investor', 'investments')->where('type', 'investor')
            ->when($request->firstname, function($query) use ($request){
                return $query->where('investor.firstname', 'like', "%{$request->firstname}%");
                return $query->where('investor.firstname', 'LIKE', "%$request->firstname%");
            })      
            ->when($request->lastname, function($query) use ($request){
                return $query->where('investor.lastname', 'like', "%{$request->lastname}%");
                return $query->where('investor.lastname', 'LIKE', "%$request->lastname%");
            })              
            ->when($request->email, function($query) use ($request){
                return $query->where('email', 'like', "%{$request->email}%");
                return $query->where('email', 'LIKE', "%$request->email%");
            })                  
            ->when($request->phone, function($query) use ($request){
                return $query->where('investor.phone', 'like', "%{$request->phone}%");
                return $query->where('investor.phone', 'LIKE', "%$request->phone%");
            })  
            // ->when($request->inquiries_all, function($query) use ($request){
                // return $query->where('investors.inquiries_all', '<=', $request->inquiries_all);
            // })   
            ->when($request->inquiries_current_month, function($query) use ($request){
                return $query->where('investor.inquiries_current_month', '<=', $request->inquiries_current_month);
            })  
            // ->get()
            ->paginate(15)
        ;
        
        Request::flash();       
        
        return view('backend.investors.index')->with([
            'investors'         => $investors,      
        ]);
    }

Trying to query like that I got this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'investors.phone' in 'where clause' (SQL: select count(*) as aggregate from `users` where `type` = investor and `investor`.`phone` like %76% and `users`.`deleted_at` is null)

changing in my nested where queries "investor." to "investors." doesnt make any diffrence:

Here are my models:

User:

  • id
  • type (enum: admin or investor)
  • email ...

Investor:

  • id
  • user_id
  • phone
  • firstname
  • lastname ...

So how to improve my query to get it work? I need to query by: user.email, investor.phone, investor.firstname, investor.lastname (and some more, but rule for them will be the same).

Please sign in or create an account to participate in this conversation.