Symphony.

A new extension, "Union Datasource" is now available for download. Comments and feedback can be left here but if you discover any issues, please post it on the issue tracker.

What is it?

A union datasource allows you to combine multiple datasources to output as a single datasource for the primary purpose of a unified pagination.

For instance, you have may have two sections, News and Tweets that you'd like to display as a single stream on the frontend. At the moment, this is difficult as the pagination for both these datasources is different and will start to lead to unpredictable results as you page through.

This extension allows you to create your two datasources as you normally would, say 'Read News by Date' and 'Read Tweets by Date' complete with their own Filtering and Included Elements and then combine the two datasources together. A third datasource is created which will control your Pagination and use it's name as the datasource root element in your XML.

This is relatively untested at the moment, so be cautious before deploying to a production site.

Out. Standing.

large-intake-of-breath-then-giggles-with-excitement

This is really sweet. Thanks Brendo!

Union Datasource updated to version 0.3 on 12th of April 2011

Hey, Just tried this out and followed all the instructions to the letter (I think) but am getting a Fatal Error.

I have three datasources 'articles', 'links', 'quotes' that I'm pulling into one 'Full Feed' data source.

The three original datasources are all filtered by date using earlier than {$today} {$current-time} - I'm not sure that's relevant but thought I'd mention it. They are also sorted by date.

I don't have any filtering or sorting in the union data source (although I would like to sort by date for all of them - not sure if that will do that automatically as each datasource is already filtered by date? )

I'm pretty rubbish at php but think it's ok - you can view my data source file here: http://d.pr/DlnO

Here's a screenshot of the database error I'm getting: http://cl.ly/62rX

Any help very much appreciated. Cheers!

Extremely odd! Haven't come across that one before.

Could you add the following lines to the lib/class.uniondatasource.php file. It'll help track down where the bug is coming from.

At line #362, add var_dump($this->data, $sql);exit; and then pastie the response please. That'll help :)

not sure if that will do that automatically as each datasource is already filtered by date?

Yeah, it'll abide to the sorting of the first datasource specified.

What came from that isn't very long so proabbly doesn't need pastie - hopefully this makes some kind of sense?

array(3) { ["section"]=> array(1) { [0]=> bool(false) } ["sort"]=> array(3) { [0]=> NULL [1]=> NULL > [2]=> NULL } ["sql"]=> array(3) { [0]=> NULL [1]=> NULL [2]=> NULL } } string(34) " UNION ALL > UNION ALL LIMIT 0, 20"

Can you post one of your other datasources?

It looks like it's failing very early in the code.

Could you also add $this->datasources to the var_dump?

Hey

Other datasource here: http://d.pr/Y9dO They are all untouched - so should be just whatever is the default

Here's what I get when I swap out $this->data for $this->datasources: [redacted]

Thanks for the help - I have a horrible feeling it's something i"m doing wrong but I have no idea how to figure out what.

Cheers,

T

If you swap earlier than {$today} {$current-time} for earlier than now, does anything change?

Everything looks fine judging by that datasource, and the dump. The only unusual thing is that in the dump, the filter becomes "dsParamFILTERS"]=> array(1) { [14]=> string(14) "earlier than " }, which is omitting the {$today} and {$current-time} parts.

You might also want to change your MySQL password as that pastie has included all your Configuration details (it's not your fault, it's just tied to the Datasource object, apologies for not mentioning it earlier, I didn't realise)

Funnily enough that fixed it - Turns out it doesn't like those params.

And earlier than now is much tidier (didn't know you could use 'now').

I'll have a good tinker around with this tonight but judging by the XML it's all looking good now.

Thanks Brendo!

And earlier than now is much tidier (didn't know you could use 'now').

Yeah it's a little known secret, but the Date field accepts anything that strtotime accepts, so you can use all sorts of things like 'next week' or 'yesterday' for example.

Nice! That will prove handy I'm sure.

Hey um another problem - the 'Full-Feed' datasource from above is not not showing any pagination.

I've checked that it thinks it should paginage in the file: public $dsParamPAGINATERESULTS = 'yes';

But there is no pagination data in the XML.

When I initially set up the three feeder data sources I didn't originally set them to have pagination in their XML... I've changed that now so they do but wondered if that could have anything to do with it?

Any ideas?

Thanks again!

On your union datasource have you selected the pagination element in the included elements? I've also pushed a fix to github for the previous issue. The datasource would fail when parameters where used in filtering

Brendan, this seems to be a very helpful extension but I'm not able to get it working.
I'm getting this error:

Unknown column 'i' in 'field list'
An error occurred while attempting to execute the following query

SELECT SQL_CACHE `e`.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, `e`.section_id, e.`author_id`, UNIX_TIMESTAMP(e.`creation_date`) AS `creation_date` , i FROM `sym_entries` AS `e` WHERE `e`.`section_id` = 7 ORDER BY `gmt` desc LIMIT 0, 20

Backtrace:
[/Users/nilshoerrmann/Sites/Webseiten/nilshoerrmann.de/symphony/lib/toolkit/class.mysql.php:458] MySQL->__error();
[/Users/nilshoerrmann/Sites/Webseiten/nilshoerrmann.de/symphony/lib/toolkit/class.mysql.php:604] MySQL->query();
[/Users/nilshoerrmann/Sites/Webseiten/nilshoerrmann.de/extensions/uniondatasource/lib/class.uniondatasource.php:363] MySQL->fetch();
[/Users/nilshoerrmann/Sites/Webseiten/nilshoerrmann.de/extensions/uniondatasource/lib/class.uniondatasource.php:68] UnionDatasource->fetchByPage();
[/Users/nilshoerrmann/Sites/Webseiten/nilshoerrmann.de/symphony/lib/toolkit/class.frontendpage.php:827] UnionDatasource->grab();
[/Users/nilshoerrmann/Sites/Webseiten/nilshoerrmann.de/symphony/lib/toolkit/class.frontendpage.php:407] FrontendPage->processDatasources();
[/Users/nilshoerrmann/Sites/Webseiten/nilshoerrmann.de/symphony/lib/toolkit/class.frontendpage.php:201] FrontendPage->__buildPage();
[/Users/nilshoerrmann/Sites/Webseiten/nilshoerrmann.de/symphony/lib/core/class.frontend.php:110] FrontendPage->generate();
[/Users/nilshoerrmann/Sites/Webseiten/nilshoerrmann.de/index.php:25] Frontend->display();

This is how my data source looks like:

<?php

    require_once(EXTENSIONS . '/uniondatasource/lib/class.uniondatasource.php');
    require_once(TOOLKIT . '/class.datasource.php');

    Class datasourcestream extends UnionDatasource{

        public $dsParamROOTELEMENT = 'stream';
        public $dsParamORDER = 'desc';
        public $dsParamPAGINATERESULTS = 'yes';
        public $dsParamLIMIT = '20';
        public $dsParamSTARTPAGE = '1';
        public $dsParamREDIRECTONEMPTY = 'no';
        public $dsParamSORT = 'system:id';
        public $dsParamASSOCIATEDENTRYCOUNTS = 'no';
        public $dsParamUNION = array(
            'tweets',
            'links'
        );

        public function __construct(&$parent, $env=NULL, $process_params=true){
            parent::__construct($parent, $env, $process_params);
            $this->_dependencies = array();
        }

        public function about(){
            return array(
                'name' => 'Stream',
                'author' => array(
                    'name' => 'Nils Hörrmann',
                    'website' => 'http://webseiten/nilshoerrmann.de',
                    'email' => 'post@nilshoerrmann.de'),
                'version' => '1.0',
                'release-date' => '2011-04-19T15:57:37+00:00'
            );
        }

        public function getSource(){
            return '8';
        }

    }

Any ideas where to look for the error?
Thanks!

PS: My original data sources that I'm trying to unify work without error.

Thanks Brendo,

Yeah - you're right - I didn't include the system pagination when I created the union data source.

Editing the Union datasource by adding in:

public $dsParamINCLUDEDELEMENTS = array(
                'system:pagination',
        );

fixed up up and I now have a unified datasource with pagination! Brilliant.

Thanks for all your work on this - will prove to be a very handy extension I'm sure!

Last quick question - does grouping work in a union data source? Ie group by date?

Cheers,

T

Nils,

Are your original datasources (that you are 'Unifying') being filtered by any parameters in filtering?

That was the issue above which Brendo just pushed a fix for above.

Aside from that issue I've now got it working perfectly with three data sources all filtered by date and ordered by date in one unified datasource.

@Nils, there's a bug when filtering by system:id, I imagine this extends to system:date as well. I was holding off on 0.4 until I fixed this, I should have it in the next few days.

@timchesney, I haven't tested Grouping at all, it is also on the todo list

@Nils pushed a commit that should resolve the system:id/system:date issue.