How to properly filter table widget's relational data?

I have a dashboard with several widgets. There are 4 stats widgets and 2 table widgets. So I have created a page filter to handle filtering data for all widgets. Following the docs on Filtering widget data it works perfectly for the stats widgets. However, the table widget doesn't;t work as I need it to because its summing data from a rational table.

The table shows the top 10 results from the Website model based on Orders from each website.

Website model contains HasMany orders. And it displays perfectly without the filter. But when I select the data, it filters out Websites that have had orders within the date range, rather than filtering out the orders and value. - I hope that makes sense.

The code below is what I am using which is using the use InteractsWithPageFilters; trait. What I need to do is to filter the values in the columns. Any help would be great. thanks in advance.

	return $table
        ->query(
            Website::whereBelongsTo(auth()->user()->company)
            ->when($startDate, fn (Builder $query) => $query->whereHas('orders', function ($q){
                $startDate = $this->filters['startDate'] ?? null;
                return $q->whereDate('order_placed_at', '>=', $startDate);
            }))
            ->when($endDate, fn (Builder $query) => $query->whereHas('orders', function ($q){
                $endDate = $this->filters['endDate'] ?? null;
                return $q->whereDate('order_placed_at', '<=', $endDate);
            }))
            ->limit(10)
        )
        ->columns([
            TextColumn::make('name'),
            TextColumn::make('orders_count')
                ->counts('orders')
                ->label('Total orders'),
            TextColumn::make('orders_sum_total_price')
            ->sum('orders', 'total_price')
            ->label('Total revenue')
            ->money('gbp'),
                
        ])
        ->defaultSort('orders_count', 'desc')
        ->heading('Top 10 websites by order volume')
        ->striped()
        ->paginated(false);

tisuchi

2 years ago

Level 70

@threelyons How about this?


return $table
    ->query(
        Website::whereBelongsTo(auth()->user()->company)
            ->with(['orders' => function ($query) use ($startDate, $endDate) {
                if ($startDate) {
                    $query->whereDate('order_placed_at', '>=', $startDate);
                }
                if ($endDate) {
                    $query->whereDate('order_placed_at', '<=', $endDate);
                }
            }])
            ->limit(10)
    )
...

threelyons

2 years ago

Level 1

@tisuchi Many thanks for your reply. This looks like it should work, but for some reason, the data isn't changing at all when I update the filters.

threelyons

2 years ago

Level 1

@tisuchi I also tried this. but that didn't change the results either. The data results stay the same when I update the filter date range.

$startDate = $this->filters['startDate'] ?? null;
$endDate = $this->filters['endDate'] ?? null;
return $table
    ->query(
        Website::whereBelongsTo(auth()->user()->company)
            ->with(['orders' => function ($query) use ($startDate, $endDate) {
                $query->whereBetween('order_placed_at', [$startDate, $endDate])->get();
            }])
        ->limit(10)
    )

threelyons

2 years ago

Level 1

I have been playing around a bit and tried the following. But that just took me back to square one, where the table is filtered by the websites that took orders. But the order data is not changed. So, it shows that in the sample data, only 3 websites had orders within the date range. but the total order value remains the same.

I'm totally confused now, but I'm sure there is such a simple explanation, I just can't see it.

public function table(Table $table): Table
{

    $startDate = $this->filters['startDate'] ?? null;
    $endDate = $this->filters['endDate'] ?? null;

    return $table
        ->query(
            Website::query()
                ->whereHas('orders', function(Builder $q) use ($startDate, $endDate){
                    $q->whereBetween('order_placed_at', [$startDate, $endDate]);
                })
                ->whereBelongsTo(auth()->user()->company)
                ->with('orders')
        )
        ->columns([
            TextColumn::make('name'),
            TextColumn::make('orders_count')
                ->counts('orders')
                ->label('Total orders'),
            TextColumn::make('orders_sum_total_price')
            ->sum('orders', 'total_price')
            ->label('Total revenue')
            ->money('gbp'),
                
        ])
        ->defaultSort('orders_count', 'desc')
        ->heading('Top 10 websites by order volume')
        ->striped()
        ->paginated(false);
}

threelyons

2 years ago

Best Answer

Level 1

I managed to figure out how to get the right data. It took me a while to work out a solution. In the end I had to select the right data in a closure function within each TextColumn. I'm sure there is a better way. but this works now.

public function table(Table $table): Table
{

    $startDate = $this->filters['startDate'];
    $endDate = $this->filters['endDate'];

    return $table
        ->query(
            Website::query()
                ->whereHas('orders', function(Builder $q) use ($startDate, $endDate){
                    $q->whereBetween('order_placed_at', [$startDate, $endDate]);
                })
                ->whereBelongsTo(auth()->user()->company)
                ->with('orders')
        )
        ->columns([
            TextColumn::make('name'),
            TextColumn::make('total_orders')
                ->state(function (OrderDetail $orders, Website $record) use($startDate, $endDate) {
                    $count = $orders::whereBelongsTo(auth()->user()->company)
                        ->where('website_id', $record->id)
                        ->whereBetween('order_placed_at', [$startDate, $endDate])
                        ->get();
                    return $count->count();
                })
                ->label('Total orders'),
            TextColumn::make('total_revenue')
                ->state(function (OrderDetail $orders, Website $record) use($startDate, $endDate) {
                    $sum = $orders::whereBelongsTo(auth()->user()->company)
                        ->where('website_id', $record->id)
                        ->whereBetween('order_placed_at', [$startDate, $endDate])
                        ->get();
                    return $sum->sum('total_price');
                })
                ->label('Total revenue')
                ->money('gbp'),
                
        ])
        ->defaultSort(function () use($startDate, $endDate){
            $websites = Website::whereBelongsTo(auth()->user()->company)
                ->whereHas('orders', function(Builder $q) use ($startDate, $endDate){
                    $q->whereBetween('order_placed_at', [$startDate, $endDate]);
                })
                ->withCount('orders')
                ->orderBy('orders_count', 'desc')
                ->get();
            return $websites;
        })
        ->heading('Top 10 websites by order volume')
        ->striped()
        ->paginated(false);
}

Please or to participate in this conversation.