Symphony.

Thanks, I'll check that out later.

One question: By which field did you sort when system:id and system:date were buggy? Is it possibe to filter by something else?

Hm, now I've got this:

The used SELECT statements have a different number of columns
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`.`gmt` FROM `sym_entries` AS `e` LEFT OUTER JOIN `sym_entries_data_40` AS `ed` ON (`e`.`id` = `ed`.`entry_id`) WHERE `e`.`section_id` = 2 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` WHERE `e`.`section_id` = 7 ORDER BY `gmt` desc LIMIT 0, 20

I just sorted by whatever field in the datasources, so I used the core Date field.

All datasources in the UNION need to be sorted on the same field type, so if one is sorting by system:date they all have to be, likewise if one is sorting on Date field, they all need to be.

That makes sense and I got it working now. Thanks for your help, Brendan.

Question: Does it have to be the same field type or is it sufficient when the field store their data in the same format?

To ask more precisely: Would it be possible to have some data sources filtered by the core date field and others by Date and Time?

All this table joining is a bit over my head so I don't know where to dig in myself.

If the sort column is the same name, and the same type in the database table, theoretically, it should work.

What's happening behind the scenes is that each datasource is called just enough to get the SQL for each. The sorting field for the datasource is added to the SELECT columns. Once each datasource has built it's SQL, it's basically joined with UNION ALL.

Sorting is then applied to the entire SQL statement, using the sort field from the field datasource, psuedo SQL: SELECT a, b, c, handle FROM blah UNION ALL SELECT a, b, c, handle FROM blah2 ORDER BY handle

So if I was sorting on a Text Input field and a Select Box, because both have handle, the sort should work.

I haven't tested it, but in theory ;)

Okay, thanks :)

It's me again :)

I switched all my section to use the Date and Time field and made sure to sort by that field in all my data sources (I've got three). Now I see this result:

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 OUTER JOIN sym_entries_data_45 AS dt ON (' at line 2
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`, FROM `sym_entries` AS `e` LEFT OUTER JOIN `sym_entries_data_45` AS `dt` ON (`e`.`id` = `dt`.`entry_id`) WHERE `e`.`section_id` = 8 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 OUTER JOIN `sym_entries_data_50` AS `dt` ON (`e`.`id` = `dt`.`entry_id`) WHERE `e`.`section_id` = 2 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 OUTER JOIN `sym_entries_data_51` AS `dt` ON (`e`.`id` = `dt`.`entry_id`) WHERE `e`.`section_id` = 7 ORDER BY `dt`.`start` desc LIMIT 0, 50

Help!

I have a feeling I know what this is about. I've assumed ed as the default table definition:

preg_match('/ORDER BY (`ed`..*) (ASC|DESC)$/i', $data['sort'], $sort_field);

So that's not working as the DateTime uses dt

Is this set somewhere in the extension or is this definition created automatically?

I found the dt in the Date and Time source. Changing it to ed solves the issue. Thanks Brendan for the tip! I will push this to the Date and Time 2 branch.

I think there is an unwritten convention for e to be entries and ed to be entry data. I could probably brush up on my regex and make it support anything though

Union Datasource updated to version 0.4 on 9th of May 2011

• Implemented an interface to build Union Datasources without having to hack any PHP
• Implement DatasourceEntriesBuilt delegate
• Support RAND() ordering
• Add support for Associated Entry Counts
• Display correct XML result when there are no entries found
• Sorting by system:id and system:date should work correctly

Hello brendo,

as soon as I’ve seen the random ordering was supported (yay! ^_^) I’ve installed the latest version of your extension both in a website I’m working on and in a fresh Symphony installation – this latter one has the default workspace installed – but as soon as I tried to create a new union datasource I was redirected to a blank screen.

So I’ve taken note of the errors, in the first case Symphony said:

Fatal error: Class 'SectionManager' not found in /Applications/MAMP/htdocs/showcase/extensions/uniondatasource/content/content.datasources.php on line 223

In the second one – and here the blank screen appeared when I clicked on the Union Datasource entry in the menu – it said:

Fatal error: Class 'FrontendPageNotFoundException' not found in /Applications/MAMP/htdocs/prova/symphony/lib/toolkit/class.datasource.php on line 226

I hope the previous annotations can come in handy for the development of this extension, which promises to be a fine one!

Union Datasource updated to version 0.4.1 on 10th of May 2011

• Don't $process_param in the backend context, fixes issue with required parameters
• Ensure SectionManager and FieldManager classes are included

@theBigMandarino This should resolve those issues you encountered

You’re right @brendo, I’ve tried/stressed out Union Datasource a little and now it works like a charm, thanks!

The only issue I’ve met so far it has happened when the unified datasources were randomly sorted: inevitably the page which the union datasource is attached to was broken.

Union Datasource updated to version 0.4.2 on 11th of May 2011

• Fix RAND() sorting bug
• Ensure only valid Datasources are includable

Whoa, now all looks perfect! @brendo thank you very much for this extension!

No worries, glad it proved useful :)

You = my hero.

Thanks.