Filament•4mo ago

Eager loaded relationships filter

Hi, does anyone know how make a filter that would filter record's relationships. What I basically need is I want to display all users, but be able to filter how many posts have they posted between 2 dates. Below you can see the code I tries, but somehow it does not work. Or maybe it is not possible that way?

Filter::make('start')
                    ->form([
                        DatePicker::make('start_date')->label('Start Date'),
                    ])
                    ->query(function ($query, array $data) {
                        $startDate = Carbon::parse($data['start_date'])->format('Y-m-d');
                        return $query->with('posts', function ($q) use ($startDate) {
                           $q->where('date', '>=', $startDate);
                        });
                    }),

Filter::make('start')
                    ->form([
                        DatePicker::make('start_date')->label('Start Date'),
                    ])
                    ->query(function ($query, array $data) {
                        $startDate = Carbon::parse($data['start_date'])->format('Y-m-d');
                        return $query->with('posts', function ($q) use ($startDate) {
                           $q->where('date', '>=', $startDate);
                        });
                    }),

9 Replies

Keika•4mo ago

I Hope this will help, I was actually looking for that for a trend today. I found this post which basically says to use whereBetween with 2 dates https://www.larahints.com/#how-to-get-records-within-a-quarter Hopefully that's what you need too

skashizadeh•4mo ago

this might help ``` ->filters([ Filter::make('posts_between_dates') ->form([ DatePicker::make('start_date') ->label('Start Date') ->required(), DatePicker::make('end_date') ->label('End Date') ->required(), ]) ->query(function ($query, array $data) { $startDate = Carbon::parse($data['start_date'])->startOfDay(); $endDate = Carbon::parse($data['end_date'])->endOfDay(); return $query->whereHas('posts', function ($q) use ($startDate, $endDate) { $q->whereBetween('created_at', [$startDate, $endDate]); }); }), ]);

varovasOP•4mo ago

This works, but it hides users that does not have any posts between these dates. I want them to be on the list, but show 0 in the posts column

skashizadeh•4mo ago

you might need a left join look at this return $query->withCount(['posts' => function ($q) use ($startDate, $endDate) { $q->whereBetween('created_at', [$startDate, $endDate]); }]);

varovasOP•4mo ago

Hmm, somehow $q->whereBetween is dropped at the end. Counts everything

skashizadeh•4mo ago

can you show the query from debugger?

varovasOP•4mo ago

This is on first page load with date parameter in url

varovasOP•4mo ago

This one is when I change the filter

varovasOP•4mo ago

Also i noticed, that that filter is not appearing in active filters serction. I dont know if it should be that way, or it's because something is wrong Ok, it work when I am using not query(), but baseQuery() It looks like it has left only one headache, why that filter does not appear in active filters bar Ok, i had to use indicateUsing(). Thank you for your effor guys

Gaming

Programming

Eager loaded relationships filter