Xataface Forums

Hello,

I am attempting a calculated field using the __sql__ construct in the fields.ini file:

Code: Select all

__sql__ = "SELECT p.*,datediff(curdate(),birthdate) as age from Participants as p"


However, I get the following error:

Fatal error: Cannot use object of type PEAR_Error as array in /var/www/private/dataface/Dataface/Table.php on line 1789

If I just use curdate() it works, however other expressions (e.g. curdate()-birthdate) do not.

???

Hi Stephen,

Unfortunately dataface uses an SQL parser based on the PEAR SQL_Parser class. I have made numerous improvements to it to support most cases, however it doesn't handle arithmetic in the SQL query... it's on a todo list, but I'm not looking forward to re-opening that can of worms...

Best regards

Steve

What is the best workaround in this situation? Should we use views instead, and let MySQL do the calculations there, or is there a better method?

Certainly using a view is one workaround. As Stephen mentioned, using the datediff() SQL function should work. It is just the use of arithmetic operators (e.g. +, *, /, -) that the parser doesn't handle currently.

Another workaround is to just use a dummy field and then override the xxx__display() method to display the calculation that you require.

http://xataface.com/forum/viewtopic.php?t=4057#20393

-Steve