even more advanced CRUD SQL queries than what's shown in the documentation

[prescriptionlifeline]

I have a table for users and I have a table for dues. Each user has to pay their dues annually. The dues table has columns like year, amount_paid, date_paid, user_id, created_at, updated_at, etc.

Dues can be marked as paid through the User CRUD, however, the User CRUD only lets you edit the payment date and the amount for the current year. Here's how the user CRUD handles that:

        CRUD::field([
            'name' => 'currentDues',
            'label' => date('Y') . ' Dues',
            'type' => 'relationship',
            'subfields' => [
                [
                    'label' => 'Date Paid',
                    'name' => 'date_paid',
                    'type' => 'date',
                ],
                [
                    'label' => 'Amount Paid',
                    'name' => 'amount_paid',
                    'type' => 'number',
                ],
                [
                    'name' => 'year',
                    'type' => 'hidden',
                    'value' => date('Y'),
                ],
            ],
        ]);

Because the Dues fields in the User CRUD works like it does if you make a non-dues change to a particular user via the User CRUD a NULL entry for the current year will be created in the Dues table. eg. the only non-NULL columns will be id, user_id, year, created_at and updated_at. Until that's done, however, there won't be ANY entries in the Dues table for the current year.

To view who all has paid dues and who all hasn't I have a Dues CRUD. In the Dues CRUD I have a filter where you can select the year and a filter to see who all is Unpaid:

        // show the most recent 5 years, starting with 2024
        $years = range('2024', date('Y'));
        $years = array_slice($years, -5);
        $years = array_combine($years, $years);
        CRUD::filter('year')
            ->type('year')
            ->values($years)
            ->whenActive(function($value) {
                CRUD::addClause('WHERE', 'year', $value);
            });
...
        CRUD::filter('unpaid')
            ->label('Unpaid')
            ->type('simple')
            ->whenActive(function() {
                CRUD::addClause('unpaid');
            })->else(function() {
                CRUD::addClause('paid');
            });

I have additional code in place to make it so that if you visit the Dues CRUD without specifying a year that you'll be redirected to the same page with the years GET parameter is set to the current year. This is achieved through Widget::add()->type('script')->content(...).

That all works great for seeing who is all paid up for a given year. Even the raw SQL is easy enough to wrap ones head around:

SELECT u.first_name, u.last_name, d.date_paid, d.amount_paid
FROM users u
JOIN dues d ON u.id = d.user_id AND d.year = 2025
WHERE d.date_paid IS NOT NULL;

In my Dues model I achieve that by doing this:

    public function scopePaid(Builder $query): void
    {
        $query->whereNotNull('date_paid');
    }

Where things start to break down is for the unpaid filter. Here's what I currently have that doesn't work very well:

    public function scopeUnpaid(Builder $query): void
    {
        $query->whereNull('date_paid');
    }

The problem with that is that that only works if there actually is an entry in the Dues table. ie. if someone edited an entry in the Users CRUD without actually changing any of the Dues fields. But what if you never performed such an edit for a particular user? If not then said user will not have an entry for the current year in the dues table, at all, and, consequently, won't be returned as being unpaid.

So it seems to me that there are three possible solutions, one of which I rather don't like and the other two of which I am unclear on how to achieve.

Solution # 1 (which I don't like)

Have a cron job that runs once a year on Jan 1 creating NULL entries in the Dues table for each user for the current year. eg.

INSERT INTO dues (user_id, year, created_at, updated_at)
SELECT user_id, YEAR(NOW()), NOW(), NOW()
FROM users;

Solution # 2

Make it so that the unpaid filter does something comparable to this SQL:

SELECT u.first_name, u.last_name, d.date_paid, d.amount_paid
FROM users u
LEFT JOIN dues d ON u.id = d.user_id AND d.year = 2025
WHERE d.date_paid IS NULL
	AND u.created_at <= '2025-12-31 23:59:59'
	AND u.deleted_at IS NULL;

I'm doing the u.created_at <= '2025-12-31 23:59:59' bit because I don't want people whose accounts were created in 2025 to be showing up as not having paid their dues for 2024.

Anyway, this query should work if there's a NULL entry in the dues table for the current year or if there's no entry at all. The problem is that the CRUD table is doing the FROM on the Dues table - not on the Users table.

If I use a RIGHT JOIN instead of a LEFT JOIN I can have the FROM be on the Dues table instead:

SELECT u.first_name, u.last_name, d.date_paid, d.amount_paid
FROM dues d
RIGHT JOIN users u ON u.id = d.user_id AND d.year = 2025
WHERE d.date_paid IS NULL
	AND u.created_at <= '2025-12-31 23:59:59'
	AND u.deleted_at IS NULL;

But how do I write a clause to do that? Is there a way to pass a parameter - the year - to scopeUnpaid?

https://backpackforlaravel.com/docs/6.x/crud-api#custom-advanced-queries talks about whereHas and gives this example:

$this->crud->addClause('whereHas', 'posts', function($query) {
   $query->activePosts();
});

But I feel like that'd prob do a JOIN when what I want to be doing is a RIGHT JOIN.

I guess I could use a magic method. eg. do CRUD::addClause("unpaid$year") and then in the Dues model I have a __call method that extracts the year from the method name that's being called and then uses that as a parameter but that just seems super hackish.

Solution # 3

Make it so that changing non Due fields in the User CRUD does NOT create a NULL entry in the dues table for the current year. Or if you clear the dues data for a member for the current year it does a full on DELETE vs an UPDATE. That would marginally simplify the SQL query to this:

SELECT u.first_name, u.last_name, d.date_paid, d.amount_paid
FROM dues d
RIGHT JOIN users u ON u.id = d.user_id AND d.year = 2025
WHERE u.created_at <= '2025-12-31 23:59:59'
	AND u.deleted_at IS NULL;

This, however, still leaves me with all the same problems that solution # 2 has.

[backpack-ai-bot[bot]]

Based on your question about implementing advanced queries in Backpack CRUD for handling unpaid dues, I can suggest a solution using Backpack's query modification capabilities.

For your unpaid filter scenario, you can use a more advanced query approach with Backpack's addClause() method that accepts a closure. This allows you to create complex queries including the RIGHT JOIN you need.

Here's how you could implement your unpaid filter:

CRUD::filter('unpaid')
    ->label('Unpaid')
    ->type('simple')
    ->whenActive(function() {
        $year = request()->input('year', date('Y'));
        CRUD::addClause(function($query) use ($year) {
            $query->rightJoin('users', function($join) use ($year) {
                $join->on('users.id', '=', 'dues.user_id')
                     ->where('dues.year', '=', $year);
            })
            ->whereNull('dues.date_paid')
            ->where('users.created_at', '<=', $year.'-12-31 23:59:59')
            ->whereNull('users.deleted_at');
        });
    })->else(function() {
        CRUD::addClause('paid');
    });

This approach is supported by Backpack's documentation on custom advanced queries. You can use a closure with addClause() to create complex queries as shown in the CRUD API Cheat Sheet and CRUD API documentation.

For even more complex scenarios, you can completely replace the query using:

$this->crud->query = YourCustomQueryBuilder;

This approach is similar to what was suggested in a community forum discussion for complex nested queries.

Alternatively, if you want to pass parameters to your scope methods, you can use dynamic scopes:

public function scopeUnpaidForYear(Builder $query, $year): void
{
    $query->rightJoin('users', function($join) use ($year) {
        $join->on('users.id', '=', 'dues.user_id')
             ->where('dues.year', '=', $year);
    })
    ->whereNull('dues.date_paid')
    ->where('users.created_at', '<=', $year.'-12-31 23:59:59')
    ->whereNull('users.deleted_at');
}

Then in your filter:

CRUD::addClause('unpaidForYear', request()->input('year', date('Y')));

This approach avoids the need for a cron job (your Solution #1) and provides a cleaner implementation than using magic methods.

ℹ️ 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): [Bug] Custom search logic for computational column
Github Issue (closed): About the capabilities of Backpack
Github Issue (closed): [Bug] I can't get all the deleted data
Github Issue (closed): [Bug] Crud add Clause Bug in combination with join
Filters PRO|Examples
How to show correct filtering values and deal with addBaseClause()
Github Issue (closed): CRUD Filters, Query translation into simple filter?
Github Issue (closed): How to load a 'list' crud view with a filter enabled
CRUD API Cheat Sheet|Custom / Advanced Queries
CRUD API|Custom / Advanced Queries
Github Issue (closed): How can I make custom filters?
Github Issue (closed): BadMethodCallException on simple filter
showing relationships in list operation?
inserting data from a crud from one table into another table

[prescriptionlifeline]

        $year = request()->input('year', date('Y'));
        CRUD::addClause(function($query) use ($year) {
            $query->rightJoin('users', function($join) use ($year) {
                $join->on('users.id', '=', 'dues.user_id')
                     ->where('dues.year', '=', $year);
            })
            ->whereNull('dues.date_paid')
            ->where('users.created_at', '<=', $year.'-12-31 23:59:59')
            ->whereNull('users.deleted_at');
        });

That seems to just be returning the entries in the dues table where date_paid is NULL. People who don't have a dues entry at all aren't being returned. I suppose this could be useful if I combined it with the cron job I mentioned in solution # 1 but if I went that route (1) the code could be simplified to replace the rightJoin with a regular join and (2) I would really rather not do the cron job.

public function scopeUnpaidForYear(Builder $query, $year): void
{
    $query->rightJoin('users', function($join) use ($year) {
        $join->on('users.id', '=', 'dues.user_id')
             ->where('dues.year', '=', $year);
    })
    ->whereNull('dues.date_paid')
    ->where('users.created_at', '<=', $year.'-12-31 23:59:59')
    ->whereNull('users.deleted_at');
}

Same thing.

I even tried commenting out the whenActive bit of my year filter and that didn't help:

        // show the most recent 5 years, starting with 2024
        $years = range('2024', date('Y'));
        $years = array_slice($years, -5);
        $years = array_combine($years, $years);
        CRUD::filter('year')
            ->type('year')
            ->values($years);
            //->whenActive(function($value) {
            //    CRUD::addClause('WHERE', 'year', $value);
            //});

What doesn't make a lot of sense to me is that commenting out the whenActive bit doesn't result in all the dues for 2024 showing up even when 2025 is selected. Like with whenActive commented out shouldn't the query it's doing be this?:

SELECT u.first_name, u.last_name, d.date_paid, d.amount_paid
FROM dues d
JOIN users u ON u.id = d.user_id;

I suppose if I don't get an answer to this question after a few days I can shift my focus to why commenting out the whenActive bit isn't resulting in the behavior I'd expect it to and make a new post about that vs this...

[prescriptionlifeline]

I've mostly gotten this to work by making the model for this CRUD by \App\Models\User vs \App\Models\Due. I'm having one lingering issue that I'll make a new post about.