How to add date filter with relationship_count?

[blessen131]

I'm trying to find how many notes each user added and filter by date, everything is working except the date filter

$this->crud->addColumns([
           [
               'name'  => 'name',
               'label' => trans('backpack::permissionmanager.name'),
               'type'  => 'text',
           ],
           [
               'name'      => 'requests',
               'type'      => 'relationship_count',
               'label'     => 'Total requests',
           ],
           [
               'name'      => 'notes',
               'type'      => 'relationship_count',
               'label'     => 'Total notes',
           ]
       ]);

//TODO:: date filter

   CRUD::filter('datetime')
            ->label('Date')
            ->type('date_range')
            ->whenActive(function ($value) {
                $dates = json_decode($value);
                $this->crud->addClause('whereHas', 'notes', function ($query) use ($dates) {
                    $query->where( 'updated_at', '>=', $dates->to);
                    $query->where('updated_at', '<=', $dates->from);
                });
            }
        );

User Model

public function requests(): HasMany
    {
        return $this->hasMany(Request::class);
    }
    public function notes(): HasMany
    {
        return $this->hasMany(Note::class);
    }

[backpack-ai-bot[bot]]

To add a date filter with relationship_count, you need to modify your approach slightly. The relationship_count column type is not directly filterable, so we need to use a different method to achieve the desired result. Here's how you can modify your code to add a date filter for the notes count:

1. First, use withCount() to add a count of notes within the date range:

$this->crud->query->withCount(['notes' => function ($query) {
    // This will be modified by the filter
}]);

2. Then, modify your date range filter:

CRUD::filter('datetime')
    ->label('Date')
    ->type('date_range')
    ->whenActive(function ($value) {
        $dates = json_decode($value);
        $this->crud->query->withCount(['notes' => function ($query) use ($dates) {
            $query->where('updated_at', '>=', $dates->from)
                  ->where('updated_at', '<=', $dates->to . ' 23:59:59');
        }]);
    });

3. Replace the relationship_count column with a text column that displays the filtered count:

[
    'name'      => 'notes_count',
    'type'      => 'text',
    'label'     => 'Total notes',
    'suffix'    => ' notes',
],

This approach will be more performant and allow you to filter the notes count by date. The withCount() method adds a notes_count column to your results, which you can then display using a text column type.

Here are some important points to note:

1. The date range filter uses from and to properties, not to and from as in your original code. Make sure to use them correctly.

2. We add 23:59:59 to the to date to include the entire day in the range.

3. This method will be more efficient than the relationship_count column type, especially for large datasets, as explained in the Backpack documentation on Columns.

4. You may need to adjust the column names and relationship names to match your exact model structure.

Remember to test this thoroughly with your specific data structure and requirements.

Backpack Docs: Columns
Backpack Docs: Filters

ℹ️ Please note our AI Bot does not reply to follow-ups, this will be the only AI answer in this thread. If the AI helped you find the answer, please mark it as answered or write details below so it can learn. Then close the conversation. If not, please give more information for when a human reads this. ℹ️

Relevant sources:

Github Issue (closed): Filter for CRUD (DateRange) not working
Added date and date range pickers to filters
Github Issue (closed): date range filter 'to' date needs time
Github Issue (closed): How can I make custom filters?
CRUD Fluent API|Examples Part 2
Filters PRO|Filter Types
Fields|date_range PRO
Github Issue (closed): [Bug] Pro: date_range filter undefined array key from
Github Issue (closed): [Bug] date_range field doesn't work as subfield inside relationship
Added translations to date filter
Columns|date_range PRO
Github Issue (closed): Passing parameter from model_function
Github Issue (closed): [Bug] Date Range Filter ignores date_range_options->locale

[jcastroa87]

Hello @blessen131

First, as our bot show, your date_range is nos correctly set, you set

$query->where( 'updated_at', '>=', $dates->to);
$query->where('updated_at', '<=', $dates->from);

You can follow bot instructions or try this:

CRUD::filter('datetime')
    ->label('Date')
    ->type('date_range')
    ->whenActive(function ($value) {
        $dates = json_decode($value, true); // Ensure it's an associative array

        if (isset($dates['from'], $dates['to'])) {
            $from = Carbon::parse($dates['from'])->startOfDay();
            $to = Carbon::parse($dates['to'])->endOfDay();

            $this->crud->addClause('whereHas', 'notes', function ($query) use ($from, $to) {
                $query->whereBetween('updated_at', [$from, $to]);
            });
        }
    });

Let me know if that's work for you.

Cheers.

[blessen131]

@jcastroa87 Still not working!

CRUD::filter('datetime')
            ->label('Date')
            ->type('date_range')
            ->whenActive(function ($value) {
                $dates = json_decode($value, true); 
                if (isset($dates['from'], $dates['to'])) {
                    $from = Carbon::parse($dates['from'])->startOfDay();
                    $to = Carbon::parse($dates['to'])->endOfDay();

                    $this->crud->addClause('whereHas', 'notes', function ($query) use ($from, $to) {
                        $query->whereBetween('updated_at', [$from, $to]);
                    });
                }
            });

$this->crud->addColumns([
           [
               'name'  => 'name',
               'label' => trans('backpack::permissionmanager.name'),
               'type'  => 'text',
           ],
           [ // n-n relationship (with pivot table)
               'label'     => trans('backpack::permissionmanager.roles'),
               'type'      => 'select_multiple',
               'name'      => 'roles',
               'entity'    => 'roles',
               'attribute' => 'name',
               'model'     => config('permission.models.role'),
           ],
           [ 
               'name'      => 'notes',
               'type'      => 'relationship_count',
               'label'     => 'Total notes',       
           ]
       ]);

User Model

 public function notes(): HasMany
    {
        return $this->hasMany(Note::class);
    }