Prefilter produces inconsistent results [SOLVED]

[Stevebenson223]

Hi Fabrikers

I'm having a strange problem with a prefilter.

I'm building a membership app. My main tables are a members table and a subscriptions table. Members can purchase subscriptions, which expire after one year.

I have two main reports: Current Members and Expired Members.
Current Members is based on Members list and linked to Subscriptions list; it has a prefilter to display only those records where Subscriptions.Expiry Date is greater than today.
Expired Members is the same but prefilter says:
WHERE Subscriptions.Purchase Date IN SELECT MAX(subscriptions.date_time) FROM subscriptions WHERE 1 GROUP BY member AND Subscriptions.Expiry Date is less than today.
(NOTE: Subscriptions.PurchaseDate (label) is subscriptions.date_time element)

Both these reports have successfully shown the correct information for dummy test data so far. However, this morning I added some more test data, setting up some new members and a current subscription for each. Both reports were correct. Then I created some "past" subscriptions for previous years, just to have some subscription histories. The Expired Members report then incorrectly showed two of the members on the Expired Members report even though they also have a current subscription (and are correctly shown on the Current Members report as well).

All the other members are on the correct reports, even those with subscription histories. I can see no difference between these two members and the others. I have checked the database and all the data (expiry date, etc) is correct, so I can only conclude there is something wrong with my prefilter condition.

I tried deleting the two members from Joomla, then reinstating them and their subscription histories. At first all was correct, then ONE of them showed up again on the Expired report with an old subscription, even though they were still showing correctly on the Current report.

To summarise:
Member A has 3 subscriptions: 2019, 2020 and 2021. He correctly shows on the Current Members report;
Member B has 3 subscriptions: 2019, 2020 and 2021. He correctly shows on the Current Members report AND his 2019 subscription shows on the Expired Members report... incorrectly.

Can anyone spot any errors with my prefilter conditions which might be causing these apparently random inconsistencies?

Thanks,
Steve

 

[Stevebenson223]

I am now thinking it may be caused by not grouping my prefilter conditions. I still don’t understand why it only affects certain records but I’ll revisit the wiki and try switching on some grouping.

 

[troester]

You can enable Fabrik debug in Fabrik options and then add &fabrikdebug=1 to your URL. GetData should show you the generated query


Gesendet von meinem SM-G930F mit Tapatalk

 

[Stevebenson223]

Thanks @troester, I’ll try that.

 

[juuser]

Can you post a screenshot of your pre-filter screen.

 

[Stevebenson223]

The first condition (which I didn't mention above) looks for all subscriptions which expire (some subscription types do not).
I'm now thinking the 3rd condition needs to be grouped with the 2nd?

Thanks,
Steve

 

[juuser]

Hmm, I'm not sure I'm getting this right, but if you have a "subscription" expiry date in Subscriptions table, why do you even need the second pre-filter row?

To display expired members, just "WHERE expiry_date < NOW()" should do.

And if there is some point of having the second pre-filer clause, the "IN" condition seems suspicious. "IN" means the element should be exactly the same as some value(s) returned by the query. In your case the date element value should be exactly the same including the time part of the date. E.g element with a value of "2021-06-20 12:00:00" and query result "2021-06-20 12:01:00" for IN condition are not returning any values.

 

[Stevebenson223]

Sorry @juuser, that's because I have given the list a badly-worded title. It's actually a list of members who have no current subscription. So the prefilter condition should be looking for any subscriptions whose latest purchase date has an expiry date of less than today. Hence the need for the second line.

I found the code in a 2016 forum post and as it worked I didn't investigate the SQL query any further. I've not used the IN condition before. I should have checked.

I've checked the database though and I still can't see any reasons why certain records are showing on both lists. The date/time is 00:00:00 for all the subscription records.

Is there a better way to word the SQL query than using the IN condition, assuming that is causing the problem?

Thanks,
Steve

 

[juuser]

I'm still not 100% sure I get the whole idea, but you should probably change the "IN" condition to "LESS THAN OR EQUAL".

 

[Stevebenson223]

Unfortunately, that's generated an incorrect query. I think if I change the query as you suggest, I need to also change the wording of the rest of the query in the VALUE field. Just not sure how.

So what I'm trying to achieve is to display those members whose subscription has expired and have not yet purchased a subscription for the current year; i.e. whose latest subscription has expired. If I used "WHERE expiry_date < NOW()" it would display all expired subscriptions for all members, including those who have purchased a current subscription. The purpose of the list is to allow a membership manager to decide whether to contact those members, to block them (you helped me create a BLOCK button) or to delete them (to comply with GDPR regulations).

Regards,
Steve

 

[juuser]

Oops, didn't notice the GROUP BY part which of course eliminates possibility to use "less or equal".

I think you need to check the generated query according to Troester's instructions above.

 

[juuser]

Digging into it more deeply, you could probably change the second pre-filter row like:

field: your_member_id
condition: IN
query: SELECT member_id FROM subscriptions WHERE expiry_date < NOW()

 

[Stevebenson223]

That gives me a list of all subscriptions, including current ones where expiry_date > today! Crazy!

Nevertheless, I don't understand how that query can differentiate between old subscriptions and current ones?

I couldn't get the debug instructions to work. I set Allow fabrikDebug to Yes in the debugging tab of Fabrik options and added &fabrikdebug=1 to the end of the URL on the page displaying the list. All I got was an error message "404 component not found".

 

[Stevebenson223]

I think I've got it... thanks to you @juuser

I revisited the original post from 2016 and found it used the subscriptions id in the query rather than the purchase date. Mindful of your comments above about the date, I rewrote the prefilter query:


I then deleted all the subscriptions test data and reinput it in the correct order as they would be input in a real world situation (i.e. the id and purchase date are in the same order).

And this all works correctly now!

So once again, thank you @juuser. As you correctly identified, it must have been the combination of the IN condition on the date field which was causing the inconsistent problems.

(Still don't know how to properly run the debug though!)

Best regards
Steve

 

[troester]

Debug: maybe you have SEF URLs without any parameter in your URL? Then you have to add
?fabrikdebug=1
(instead of &...)


Gesendet von meinem SM-G930F mit Tapatalk

 

[Stevebenson223]

That worked! Thanks @troester

(Not sure what it tells me but there's an awful lot of info there! I'll revert to the previous, incorrect, query and see what it tells me.)

Thank you!