LIMIT in Data - Where query for dbjoin element

[aksmith]

Adding LIMIT to the end of a WHERE query in the databasejoin element, e.g.
WHERE {thistable}.`user_id` = {$my->id}
ORDER BY {thistable}.`nextWeek` DESC
LIMIT 1

gets rendered in the wrong order (in front of ORDER BY) and throws an error:
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 'ORDER BY `#__allcards_by_week`.`nextWeek` DESC' at line 7 SQL=SELECT DISTINCT(`#__allcards_by_week`.`id`) AS value, `nextWeek` AS text FROM `#__allcards_by_week` AS `#__allcards_by_week` WHERE `#__allcards_by_week`.`user_id` = 42 LIMIT 1 ORDER BY `#__allcards_by_week`.`nextWeek` DESY.

Same issue when using LIMIT without ORDER BY.

Thanks!
-Alan

 

[cheesegrits]

I can't seem to replicate this.

Ah HAH, yes I can.

Try putting your query in a single line, no line breaks.

The code that parses out the ORDER BY so it can re-attach it to the end of the query after we've finished adding our own modifiers, is using a regex that isn't ignoring line breaks. I'm changing that now, but I need to run it here for a few days before committing it, as that code is very brittle, and I'm not sure if there would be any other side effects form doing that.

-- hugh

 

[cheesegrits]

Actually I think that's safe, so I committed the code:

https://github.com/Fabrik/fabrik/commit/0993d45634566b624c69f6cea40776bdac1c2908

So it should now work with multiple lines.

However, I think a LIMIT without a GROUP BY probably won't work, I'd have to add some code to account for that.

-- hugh