Symphony.

Is it possible to tell a datasource to order the resultset by two columns (the way MySQL lets me do it: ORDER BY sticky ASC, datetime DESC)?

Hmm not that I know of. If you want to get dirty in the code:

class.entrymanager.php and fetch() does the sorting magic, but it doesn’t look like it’s easy to modify for multiple ordering.

You can do this in XSLT however:

<xsl:apply-templates select="">
    <xsl:sort select="sticky" type="text" order="ascending"/>
    <xsl:sort select="concat(translate(date,'-',''),translate(date/@time,':',''))" type="number" order="descending"/>
</xsl:apply-templates>

First orders by sticky then converts a date node value to a number (YYYYMMDDHHMM) and sorts on that after.

You can do this in XSLT however:

Thanks, now I get how xsl:sort works. :-)

But it will not provide the same behaviour as two ORDER BY fields. You see, MySQL orders first, then limits. Using xsl:sort would limit first, then sort.

In this case: Sticky-threads will appear first, but only if they are on the first page. If there were enough threads to push the stickies to the next page, they will appear on top of the next page.

Ah yes, I forgot to clarify that if you are using pagination then the XSLT way won’t be what you’re after.

Things like these strengthen the feeling that Symphony’s database scheme and -accessing possibilities aren’t properly thought through.

Whilst there may be good reasons to use the current scheme (a minimalistic section table with all other fields being tables joined into the result), it is missing the easy accessibility that “regular schemes” have.

This might be resolved with a class like EntryManager but it also requires the class to be able to do anything a regular SQL statement could do (more flexible conditions, more flexible joins, more flexible sorting options).

Same goes for events (although the possibility to filter INSERT or UPDATE statements by more than System:ID may be there, I don’t know without a proper documentation).

Right now, for people who know the capability and performance of SQL, the possibilities in Symphony are really disappointing.

Right now, for people who know the capability and performance of SQL, the possibilities in Symphony are really disappointing.

That’s what comes from abstractions. Data Sources and Events shield the user from the complexities of SQL, and they don’t attempt to fully recreate its full functionality.

It’s not the database schema that stops this sort of thing being possible, it’s more that having multiple sort fields was not considered in the Data Source edit page. Because it’s so seldom used, adding this functionality adds UI complexity that 99.9% of users would never use (imagine the need for JavaScript to allow a developer to add/remove and re-order sort fields and their direction).

The fetch/fetchByPage methods of the EntryManager accept a string of joins and where conditions, and so could also be modified to accept an array of sort fields I would think. A Data Source PHP file would then store its sort fields as an array instead of a string, so allow for multiple sorts.

This could go in as a feature request for a future version. I except perhaps it could be implemented code-wise and the UI left as-is. Advanced users who need this specific functionality occasionally would be able to modify the Data Source PHP and mark it as a custom-DS so that the Data Source editor doesn’t resave over their changes.

Things like these strengthen the feeling that Symphony’s database scheme and -accessing possibilities aren’t properly thought through.

I’ll disagree on that front and say they’ve been thought through very well. As I say, the user interface for querying data is so simple that designers and information achitects can use it and build websites. 99.9% of the time I don’t need anything more than the standard functionality. Remember at its core Symphony is a CMS and not a programming framework — the functionality it provides is tailored for the day-to-day content management requirements we have for websites. Building something like a forum (more of a web application) is naturally complex, and so may require your own customisations.

It’s not the database schema that stops this sort of thing being possible, it’s more that having multiple sort fields was not considered in the Data Source edit page. Because it’s so seldom used, adding this functionality adds UI complexity that 99.9% of users would never use.

I don’t ask for the possibility to do so in the Datasource editor. I am happy to edit the PHP-file myself. However it seems that you can only pass one field as a sort param.

Remember at its core Symphony is a CMS and not a framework

I am afraid I have to disagree as well. While being a thing of taste and philosophy, I think a CMS should offer versioning and a way to moderate the authoring process (at least “save as draft”). Of course, Symphony offers the latter but you’ll have to implement it yourself (create a checkbox-field, put a published = yes condition in your DS and so on). This makes it more like a framework with a very mighty scaffolding-tool.

Anyways, this is going off topic. Being a matter of taste and philosophy, will fall into a type of discussion that is deprived of finding consensus anyways. :-)

What I am asking for is a well documented EntryManager-class that can do everything a SQL statement could do, in a similar fashion or syntax. I do not ask for a crazily complex DS editor, but when opening the PHP file, I want to have full control over my data source.

What I am asking for is a well documented EntryManager-class that can do everything a SQL statement could do, in a similar fashion or syntax. I do not ask for a crazily complex DS editor, but when opening the PHP file, I want to have full control over my data source.

I don’t think this is unreasonable; in fact you’ll find that’s the intention.