calculated fields error

Archived from the Xataface Users forum.

ststoddard — Fri Nov 16, 2007 12:55 pm

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 — Fri Nov 16, 2007 1:47 pm

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 — Mon Nov 26, 2007 2:02 pm

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 — Mon Nov 26, 2007 2:08 pm

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