“Failed parsing SQL” when I use fields.ini sql directive

Archived from the Xataface Users forum.

FractalizeR — Fri Oct 07, 2011 12:49 am

Hello.

I have a table with user log data. And I want user email to be displayed in list view next to each record. I’ve defined SQL query in fields.ini

Code: Select all

__sql__ = "SELECT UL.*, U.email FROM user_log UL LEFT JOIN user U ON UL.user_id=U.id"

The query looks to me just like this one, but I get an error

Fatal error: Failed parsing SQL query on select: SELECT UL.*, U.email FROM user_log UL LEFT JOIN user U ON UL.user_id=U.id . The Error was Parse error: Unexpected clause on line 1 SELECT UL.*, U.email FROM user_log UL LEFT JOIN user U ON UL.user_id=U.id ^ found: “user” in T:\home\smartgsm.local\www\xataface\lib\SQL\Parser.php on line 1752

I’ve tried also using a delegate method, but I have the same error. MySQL itself executes this query perfectly.

Code: Select all

__sql__ = "SELECT * FROM user_log"

works BTW.

---

ADobkin — Fri Oct 07, 2011 4:56 am

The word “user” is a reserved word in the Xataface MySQL parser. Can you change your table name to something other than just “user”?

Alan

---

FractalizeR — Fri Oct 07, 2011 5:26 am

Well, enclosing user into backticks like this

Code: Select all

__sql__ = "SELECT UL.*, U.email FROM user_log UL LEFT JOIN `user` U ON UL.user_id=U.id"

worked. Thanks for hint!

But that should be documented, I think.