Laravel 4: add whereIn to the join condition

Question

I want to run the following query

SELECT * FROM order_item JOIN order ON (order_item.order_id = order.id AND order.order_status IN ('payment_completed','refund_requested')) WHERE order_item.item_id=1; 

I tried as shown below.

 OrderItem::join('order', function($join){ $join->on('order.id','=','order_item.order_id'); $join->whereIn('order.order_status',array('payment_completed','refund_requested')); })->where('order_item.item_id','=','1')->get(); 

I know this is wrong. What is the correct way to use whereIn in a join condition?

Thanks in advance!

Answer 1

Damn, I have another option for this.

You can use FIND_IN_SET ('ABC', 'ABC, CDE, DEF, XYZ');

It will return true if the condition is true, so you can add this condition to the connection reason.

 OrderItem::join('order', function($join){ $join->on('order.id','=','order_item.order_id') $join->on(\DB::raw("FIND_IN_SET(order.order_status,'payment_completed,refund_requested')"),"=",\DB::raw('"1"')); })->where('order_item.item_id','=','1')->get();