Searching on a Virtual Field

Need Help
1

Hi all

I have a members table that includes a member’s date of birth, and from this I create a virtual field that is the member’s current age.

Reading the cookbook I see that it states "Do bear in mind that virtual fields cannot be used in finds’

I have a report that I need to create for all members over a certain age however this doesn’t work

        $query = $membersTable->find('search', ['search' => $this->request->getQuery()])
            ->contain(['CurrentMemberUnits.Units'])
            ->where([
                'age >= ' => 18,
                'Units.lft >=' => $unit->lft,
                'Units.rght <=' => $unit->rght,
            ]);

Age is available in the query once returned if I leave out the condition.

Any suggestion workarounds for searching based on this virtual field?

Only thing I can think of is to use the data of birth instead

        $query = $membersTable->find('search', ['search' => $this->request->getQuery()])
            ->contain(['CurrentMemberUnits.Units'])
            ->where([
                '(TIMESTAMPDIFF(MONTH, date_of_birth, CURDATE())/12) > ' => 18,
                'Units.lft >=' => $unit->lft,
                'Units.rght <=' => $unit->rght,
            ]);

But it screams “inelegant hack” at me…

2

Can’t you just get today’s date, subtract from that the age you want, and pass that to find all records where date_of_birth is equal or less than that? And not even try to use the virtual field.

Note this does not answer the question on how to search a virtual field, just whether its needed in this specific case.

3

Age is not “available in the query”, it’s available as a field calculated on the fly in PHP through the entity. As you quoted from the manual, virtual fields cannot be used in finds.

I always do what @Jawfin suggests, using conditions like 'date_of_birth <=' => FrozenDate::now()->subYears(18). This has the added advantage that you can control what FrozenDate::now() returns when doing unit tests.

4

It’s a bit more complicated :confused:

The app provides a permanent record of all members and volunteers for a not for profit youth group running now for many decades. We did have some older volunteers who are now deceased and we’re required to retain these records. A simple date diff to calculate age would mean these members keep getting older!

The logic behind the age virtual field will check for a date_of_passing and calculate either a “current age” or “age at passing”, and also limit the latter records to senior staff visibility only.

There are a few reports that need a query based on age, although these only run on current members and volunteers so the solution I’ve come up with above should work.

5

You’re up for making the query more complex by factoring date_of_death in the clause, or convert age to a real value instead of virtual. You may even be able to do that without coding by using SQL triggers (I like triggers - keeps the workload within the SQL and away from the program). That also means you can put an index on it - very useful for age where we often need to handle them in ranges.

Edit: On reflection a trigger may not help as data would need to be updated as time progressed - maybe a daily SQL procedure - which may require crontab or whatever to launch - so is now becoming rather less friendly!

6

The queries based on age will only apply to active members - its generally to trigger things like notification that a youth member is soon turning 18 and will need to apply for the relevant working with children checks in their state.

The fun “length of time” value we have is length_of_service - used to recognise people for their volunteer work with awards

We often get a situation where someone might join as a youth member say in 2000, then leave 5 years later due to schooling or joining the workforce. Much later they might rejoin as an adult volunteer say in 2020. We can’t just look at date_of_joining and say “22 years service” - we take in to account the breaks and say “you’ve have 7 years of service”.

We’re good at making things complex for ourselves… :roll_eyes:

7

Ouch!

I’d go with a real field and maintain it after each relevant edit - and run a daily routine to check if the years of service and age need ticking over.

If you prefer not (or can’t) then whatever formula you’re applying to work out age will need to be duplicated in the querying SQL. And if you need to sort by that, you may need to do a query on that query result for sorting - or a PHP array sort.