calculated fields error

[ststoddard]

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.

???

[shannah]

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

[ADobkin]

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?

[shannah]

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