How to pre-filter table on last 7 days records?

[artonezero]

Hi
I should like to set up a table pre-filter to restrict public view of records posted within the last 7 days, based on a date field in my table (e.g. 'ReportDate')
I understand that I need to put a formula for evaluation in the 'value' box of the table data pre filter, but I'm not sure what this formula should be?

Could you advise please?
Many thanks
Mike

Using Fabrik 2.0 beta SVN 616

 

[cheesegrits]

Field: ReportDate
Condition: GREATER THAN OR EQUALS
Value: DATE_SUB(CURDATE(), INTERVAL 7 DAY)
Type: text

... should do it. Note that this will ignore any time part of the date, and only operate on the date. If you need to take time of day into account, you'll need to modify the Value according to the time calculations documented here:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

-- hugh

 

[artonezero]

Hi Hugh

Thanks for this input. However I am having a lot of problems getting this to work

It works fine if I put a date in the value field, e.g. 2008-10-21, but not with the expression DATE_SUB(CURDATE(), INTERVAL 7 DAY)

If I turn on debugging in Joomla I get the following error condition

500 - JDatabaseMySQL::query: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-housingmap2008 WHERE ( `jos_fabrik_housingmap2008`.`ReportDate` ' at line 1 SQL=SELECT COUNT(DISTINCT `jos_fabrik_housingmap2008`.`fabrik_internal_id`) AS t FROM jos_fabrik_housingmap2008 WHERE ( `jos_fabrik_housingmap2008`.`ReportDate` > 'DATE_SUB(CURDATE(), INTERVAL 7 DAY)' )

A colleague of mine says that the FROM condition shouldn't be in quotes, so could there be a bug in the Fabrik code??

Thanks
Mike

Fabrik 2.0 beta SVN 616

 

[cheesegrits]

Not sure why it's erroring, but you are right that the DATE_SUB shouldn't be in quotes. We'll have to add another option for the 'type' to prevent this.

Working on it.

-- hugh

 

[cheesegrits]

OK, I've added a 'No quotes' option to the 'type' dropdown. This will prevent Fabrik from adding quotes around your value.

However, I'm still not sure why your query is erroring out. I still don't see anything 'wrong' with it (other than it was quoting the expression).

Anyway, update to latest SVN, set that pre-filter to 'No quotes' and see if it works. I tested at my end, and can filter using the above query successfully.

-- hugh

 

[artonezero]

Fixed!

Hi Hugh

Your 'no quotes' option did the trick, the filter is now working correctly using the DATE_SUB(CURDATE(), INTERVAL 7 DAY) expression!

I think that MySQL is very particular about syntax - hence the reason it didn't work.

Incidentally I saw a note somewhere on the bug report saying you might take this out again as it is covered by 'eval'? Using Eval doesn't work (errors generated) so please don't remove this option certainly until it is tried and tested. I have lots of big datasets to test against and happy to do so if needed.

Many thanks for your assistance resolving this issue - it has been driving me mad for hours!

Cheers
Mike

 

[cheesegrits]

Yeah, I wasn't sure if MySQL subqueries have to be surrounded by () or not ... if they didn't, we could have used that type for any kind of MySQL expression.

Closing.

-- hugh