Using join in indexQuery causes duplicate records

[bocanhcam]

I have model Order have a relationship one to many with OrderItem.
OrderItem belongs to Room and Room belongs to Building.

In the Resource of Order I have a custom column that displays OrderItem of Order.

Text::make(__('Content'), function (){
    return view('partials.order-items', [
           'order' => $this,
           'orderItems' => $this->orderItems,
     ])->render();
})->asHtml()->onlyOnIndex(),

I have a filter BookingDate which is equal to OrderItem.booking_date and BuildingId which is equal to Room.building_id.

// BookingDate filter
public function apply(NovaRequest $request, $query, $value)
{
        return $query->whereHas('orderItems', function ($query) use ($value){
            $query->where('booking_date', '=', $value);
        });
}

// BuildingId filter
public function apply(NovaRequest $request, $query, $value)
{
        return $query->whereHas('orderItems.room', function ($query) use ($value){
            $query->where('building_id', '=', $value);
        });
}

The problem is when I apply both filters. If just one condition matches then I still get that Order and still print the OrderItem that only matches one condition, not both.

I tried to use join in indexQuery instead and it seems not to work also.

public static function indexQuery(NovaRequest $request, $query): Builder
{
    return $query
            ->select('orders.*')
            ->leftJoin('order_items', 'order_items.order_id', '=', 'orders.id')
            ->leftJoin('rooms', 'rooms.id', '=', 'order_items.room_id')
            ->distinct();
}

// and also change the filter query

 // BookingDate filter
public function apply(NovaRequest $request, $query, $value)
{
     return $query->where('order_items.booking_date', '=', $value);
}
    
// BuildingId filter
public function apply(NovaRequest $request, $query, $value)
{
    return $query->where('rooms.building_id', '=', $value);
}

• Laravel Version: 10
• Nova Version: 4
• PHP Version: 8

[bocanhcam]

I resolve this by using with and combine condition in the indexQuery