Document tree not sorted

194 Posts

Pandy06269 Reply #1, 16 years, 3 months ago

Hi,

I have a weird problem with 0.9.6.1 that I’ve not seen before, and couldn’t come across anything similar on the forums.

My MODx site used to be on FreeBSD (Apache 2.2.6, PHP 5.2.3, MySQL 5.0.45)
I’ve since moved it onto a Debian Etch server with Apache 2.2.8, PHP 5.2.5, MySQL 5.0.51. When I moved it, I backed up and restored the MySQL database onto the new server, and copied all files across, then cleared the cache.

The whole site works, except the document tree is sorted by document ID, and the sorting button (sort the tree -> folder/document title/etc) doesn’t take effect - it refreshes but stays in the same order.

This was fine on the FreeBSD server - it sorted by menu index. The DocManager module still shows the documents in the correct order.

Nothing in the configuration / files have changed - just moved servers.

Any ideas?

194 Posts

Pandy06269 Reply #2, 16 years, 3 months ago

After some more debugging, I see the following:

- add "echo $sql;" to line 106 of manager/frame/nodes.php and I see the following SQL query:

SELECT DISTINCT sc.id, pagetitle, parent, isfolder, published, deleted, type, menuindex, hidemenu, alias, contentType, privateweb, privatemgr, IF(1=0 OR sc.privatemgr=0, 1, 0) AS has_access FROM `aseu_live`.`modx_site_content` AS sc LEFT JOIN `aseu_live`.`modx_document_groups` dg on dg.document = sc.id WHERE (parent=0) GROUP BY sc.id ORDER BY pagetitle ASC

so the correct SQL query is being passed by MODx.

It turns out this is a MySQL bug in 5.0.51, fixed in 5.0.52.

ORDER BY not working with GROUP BY.

The fix until 5.0.52 is available is as noted in the above bug report (this is not ideal, and only temporary):

Rewrite the SQL query (lines ~99-105) to:

$sql = "SELECT * FROM
(
SELECT DISTINCT sc.id, pagetitle, parent, isfolder, published, deleted, type, menuindex, hidemenu, alias, contentType, privateweb, privatemgr,
IF(1={$mgrRole} OR sc.privatemgr=0" . (!$docgrp ? "":" OR dg.document_group IN ({$docgrp})") . ", 1, 0) AS has_access
FROM {$tblsc} AS sc
LEFT JOIN {$tbldg} dg on dg.document = sc.id
$access
WHERE (parent={$parent})
GROUP BY sc.id
) subquery
ORDER BY {$orderby};";

This works for me.

1,039 Posts

MadeMyDay Reply #3, 16 years, 2 months ago

Fix for Wayfinder, assets/wayfinder/wayfinder.inc.php:

Line 344:

$sort = 'sc.'.implode(',sc.',preg_replace("/^\s/i","",explode(',',$this->_config['sortBy'])));

change to

$sort = implode(',sc.',preg_replace("/^\s/i","",explode(',',$this->_config['sortBy'])));

Line 351 sql-Query

$sql = "SELECT DISTINCT {$fields} FROM {$tblsc} sc LEFT JOIN {$tbldg} dg ON dg.document = sc.id WHERE sc.published=1 AND sc.deleted=0 AND ({$access}){$menuWhere} AND sc.id IN (".implode(',',$ids).") GROUP BY sc.id ORDER BY {$sort} {$this->_config['sortOrder']} {$sqlLimit};";

change to:

$sql = "SELECT * FROM
			(
			SELECT DISTINCT {$fields} FROM {$tblsc} sc LEFT JOIN {$tbldg} dg ON dg.document = sc.id WHERE sc.published=1 AND sc.deleted=0 AND ({$access}){$menuWhere} AND sc.id IN (".implode(',',$ids).") GROUP BY sc.id
			) subquery ORDER BY {$sort} {$this->_config['sortOrder']} {$sqlLimit};";

thx andyccn!

MODx deutsch | Webdesign Karlsruhe | Ressourcen für Webdesign

49 Posts

koshaque Reply #4, 16 years, 1 month ago

My friends, the solution for document tree in manager works perfectly, thanks!
But the solution for Wayfinder do not work, website becomes invisible..
Maybe someone konws why?

4,205 Posts

dev_cw Reply #5, 16 years, 1 month ago

I haven’t looked at the code, but usually when the site goes blank it is due to a missing closing " or ) or ; in the code.

EDIT: OK I tool a peek, it is most likely the last bit on the sql statement the reads: {$sqlLimit};"; Notice that there is a open " and an extra ;

I don’t know if those are out of context and should be there but they do not seem to be part of the actual code. Maybe try removing the final ";

[font=Verdana]Shane Sponagle | [wiki] Snippet Call Anatomy | MODx Developer Blog | [nettuts] Working With a Content Management Framework: MODx

Something is happening here, but you don't know what it is.
Do you, Mr. Jones? - [bob dylan]

264 Posts

qlworx Reply #6, 15 years, 10 months ago

Thank you, Marc. Works like a charm on an 1und1 server with 5.0.51

194 Posts

Pandy06269 Reply #7, 15 years, 10 months ago

Quote from: yoomai at Jun 07, 2008, 09:56 AM

Thank you, Marc. Works like a charm on an 1und1 server with 5.0.51

Argh! Don’t get me started on what a bunch of clowns 1&1 are (at least their UK outfit anyway!)

264 Posts

qlworx Reply #8, 15 years, 10 months ago

Quote from: Pandy06269 at Jun 07, 2008, 10:44 AM

Argh! Don’t get me started on what a bunch of clowns 1&1 are (at least their UK outfit anyway!)

Same here in Germany. That’s globalization.

59 Posts

endyzer Reply #9, 15 years, 1 month ago

I got the sorting problem in manager only, but your suggestion didn’t worked for me.
Any other ideas?
ModX 0.9.6.3 rev 4565
My MYSQL-Version: 5.0.32

MODX Staff
10,725 Posts

opengeek Reply #10, 15 years, 1 month ago

Quote from: Mike at Mar 17, 2009, 08:25 AM

I got the sorting problem in manager only, but your suggestion didn’t worked for me.
Any other ideas?
ModX 0.9.6.3 rev 4565
My MYSQL-Version: 5.0.32

This is related to an issue with a specific version of MySQL, 5.0.51 -- if you are not using that version of MySQL then your sorting problem lies elsewhere and you’ll need to provide more information.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek