search

symphonists / uniondatasource

A union datasources allows you to combine multiple datasources to output a single datasource for the primary purpose of a unified pagination.
13 stars 11 forks source link

Front-end: "You have an error in your SQL syntax" (Symphony 2.3) #12

Closed nilshoerrmann closed 12 years ago

nilshoerrmann commented 12 years ago

When using Union Data Source on Symphony 2.3 (integration branch of this repository), I get the following error on the front-end:

Symphony Fatal Database 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 'FROM `sym_entries` AS `e` LEFT JOIN `sym_entries_data_112` AS `t112` ON `e' at line 17

An error occurred while attempting to execute the following query

SELECT SQL_CACHE `e`.id as id, `e`.section_id, e.`author_id`, UNIX_TIMESTAMP(e.`creation_date`) AS `creation_date`, `ed`.`start` FROM `sym_entries` AS `e` LEFT JOIN `sym_entries_data_111` AS `t111` ON (`e`.`id` = `t111`.entry_id) LEFT JOIN `sym_entries_data_62` AS t62_1 ON (e.id = t62_1.entry_id) LEFT OUTER JOIN `sym_entries_data_61` AS `ed` ON (`e`.`id` = `ed`.`entry_id`) WHERE `e`.`section_id` = 2 AND `t111`.relation_id IN ('2') AND ( t62_1.value IN ('Veröffentlicht') OR t62_1.handle IN ('Veröffentlicht') ) UNION ALL SELECT `e`.id as id, `e`.section_id, e.`author_id`, UNIX_TIMESTAMP(e.`creation_date`) AS `creation_date`, FROM `sym_entries` AS `e` LEFT JOIN `sym_entries_data_112` AS `t112` ON `e`.`id` = `t112`.entry_id LEFT OUTER JOIN `sym_entries_data_112` AS `ed` ON (`e`.`id` = `ed`.`entry_id`) WHERE `e`.`section_id` = 16 AND (((`t112`.start BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59') OR (`t112`.end BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59') OR (`t112`.start < '2012-01-01 00:00:00' AND `t112`.end > '2012-12-31 23:59:59'))) ORDER BY `start` desc

The Data Source is based on two other Data Sources that are sorted by a Date and Time field – both work just fine when not they are not united.

nilshoerrmann commented 12 years ago

@brendo: My SQL skills are rather limited – is this error most likely caused by this or by my own Date and Time extension?

brendo commented 12 years ago

Having a look at it now, it's something to do with the sorting detection.

brendo commented 12 years ago

Hmm, I can't get the same thing to happen:

SELECT SQL_CACHE `e`.id as id, `e`.section_id, e.`author_id`, UNIX_TIMESTAMP(e.`creation_date`) AS `creation_date`, `ed`.`start` FROM `default_entries` AS `e` LEFT OUTER JOIN `default_entries_data_72` AS `ed` ON (`e`.`id` = `ed`.`entry_id`) WHERE `e`.`section_id` = 3 UNION ALL SELECT `e`.id as id, `e`.section_id, e.`author_id`, UNIX_TIMESTAMP(e.`creation_date`) AS `creation_date`, `ed`.`start` FROM `default_entries` AS `e` LEFT JOIN `default_entries_data_5` AS t5_1 ON (e.id = t5_1.entry_id) LEFT OUTER JOIN `default_entries_data_71` AS `ed` ON (`e`.`id` = `ed`.`entry_id`) WHERE `e`.`section_id` = 1 AND (t5_1.value IN ('yes')) ORDER BY `start` desc

EDIT, Ah adding a filter triggers it :)

brendo commented 12 years ago

Right, so Union Datasource relies on a bit of a convention where most fields don't change the ed table alias. DateTime uses t31 instead (where 31 is a field ID) when filtering. That's fine, it's UD's problem :)

nilshoerrmann commented 12 years ago

DateTime uses t31 instead (where 31 is a field ID) when filtering.

Interesting – I wasn't aware that Date and Time does anything special here. Thanks for the fix, will try it out now :)

nilshoerrmann commented 12 years ago

Works perfectly – thanks!