Laravel Eloquent whereHas Issue

Question

I'm trying to use Laravel Eloquent whereHas on my last row of HasMany relation for finding last update status But this query get me all actions off order . I see this . I want to use where query on lastAction and this not work for me. thanks

My Order.php file

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    protected $table = "orders";
    protected $guarded = [];
    
    public function action()
    {
         return $this->hasMany(OrderAction::class, 'order_id');
    }
}

My OrderController.php file

<?php

namespace App\Http\Controllers;


use Illuminate\Http\Request;

class OrderController extends Controller
{
    public function index(Request $request)
    {
        $items = Order::where(function ($query) use ($request) {
            if ( !empty($request->filter_status )) {
                $query->whereHas('action',function($query1) use ($request){
                    $query1->where('state_to',$request->filter_status);
                });
            }})->latest('id')->paginate(20);

        $items->appends($request->query());
        return view('order.index',compact('items'));
    }
}

I believe you need to use a join if you want to order by relationships. Otherwise it will always order your orders by latest id. Check this out: https://stackoverflow.com/questions/48321076/perform-order-by-relationship-field-in-eloquent — Vimona, Jun 20 '20 at 12:02
I want to filter orders by state_to field in last added row in orderAction table , because my order last status store there — seyyed sina, Jun 20 '20 at 16:07
$items = Order::when(isset($request->filter_status),function($query)use($request){ $query->select('orders.*', DB::raw('(select state_to from order_action where orders.id = order_action.order_id order by id desc limit 1) as state_to ' ) ); $query->where('state_to',$request->filter_status); })->latest('id')->get(); this code return unknown column state_to — seyyed sina, Jun 21 '20 at 07:29

Vlad Vladimir Hercules · Answer 1 · 2020-06-21T08:42:07.267

0

Something like that should do it (you may need to adjust the code to fit your application):

$items = Order::when(isset($request->filter_status), function($query) use ($request) { 
   $orderActions = OrderAction::selectRaw('MAX(id), state_to')
       ->groupBy('order_id');

   $query->joinSub($orderActions, 'order_actions', function ($join) {
       $join->on('orders.id', '=', 'order_actions.order_id');
     }) 
     ->where('state_to', $request->filter_status);  
 })
 ->latest('id')
 ->paginate(20);

Reference: https://laravel.com/docs/7.x/queries#joins - Subquery Joins

edited Jun 21 '20 at 08:42

answered Jun 20 '20 at 16:39

Vlad Vladimir Hercules

1,781
2
20
37

This code return error , i fix it with groupby() but there is no difference in result and It's still a problem – seyyed sina Jun 21 '20 at 05:12
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'offtapp_api.select max(id), state_to from `order_action`' doesn't exist (SQL: select count(*) as aggregate from `orders` inner join `select MAX(id), state_to from ``order_action``` on `order_id` = `orders`.`id` where `state_to` = CANCELED) – seyyed sina Jun 21 '20 at 05:13
Could you please try changing query->join to query->joinSub($rawQuery, ‘order_actions’, function($join){ $join->on('order_actions.order_id', 'orders.id'); }) – Vlad Vladimir Hercules Jun 21 '20 at 06:54
Thank You , I didn't find answer but I approached to it , If i can select state_to of row of max(id) and grouping that(for getting last one ) , i get answer – seyyed sina Jun 21 '20 at 16:30

Laravel Eloquent whereHas Issue

1 Answers1