How can I write this into laravel eloquent?

I found an answer to a similar problem but when I tried to implement it it didn't work. Basically I am trying to pull from a table based on two foreign IDs and then if multiple tables match that I want to pull the max id so I have a list of orders that aren't duplicating themselves but also giving me the correct order status.

SELECT *  FROM order_status_updates AS s1 JOIN orders AS s2 WHERE s2.id = (SELECT MAX(s2.id) FROM orders) AND s1.order_id = s2.id AND s1.order_status_id = s2.order_status_id

this is what I have but it doesn't work.

return OrderStatusUpdate::
    join(\DB::raw('(SELECT * FROM orders as s2 WHERE s2.id = (SELECT MAX(s2.id) FROM orders'), function($join) {
        $join->on('s2.id', 'order_status_updates.order_id');
        $join->on('s2.order_status_id', 'order_status_updates.order_status_id');
    })

Tray2

3 years ago

Level 73

Even if that is valid SQL, it looks off to me.

SELECT * 
FROM order_status_updates AS s1
JOIN orders AS s2 WHERE s2.id =
		 (SELECT MAX(s2.id) FROM orders)
 AND s1.order_id = s2.id 
AND s1.order_status_id = s2.order_status_id

Let me get this straight,

You want to select everything from order_status_updates and from orders, but you only want to join it for the latest order (MAX(s2.id), and then you want the order_id to match the id from orders and it should match the status as well?

That makes no sense to me at all.

Show your migrations, some sample data from both tables, and the expected result.

Sinnbeck

3 years ago

Level 102

My guess is something like this

OrderStatusUpdate::query()
->join('orders', function($join) [
    $join->on('orders.id', 'order_status_updates.order_status_id')
           ->on('orders.order_status_id', 'order_status_updates.order_status_id');
    
})->where('orders.id', '=', function ($query) {
         $query->selectRaw('MAX(o.id)')->from('orders as o');
})-get();

This is partially guess work as I don't quite understand what your query is supposed to do :)

Please or to participate in this conversation.