-
☆ A M B ☆
- 24,524 Posts
That should do the job, yes.
...it doesn’t work, so it must be the MySQL bug then. I checked with my host (1&1) and they’re not offering MySQL 6 (which has bug fixed) yet. Thanks for all that bug info. so I could find that out!
Is there any other way I can get around this instead? I know from your bug link it’s the groupby clause, but of course I’m not manually coding the groupby, I take it it’s generated by the Ditto call? Someone on that bug thread suggested "...removing the GROUP BY clause. Using GROUP BY on a
unique index (a Primary Key, of course, is a unique index) is pointless as each row will by definition be its own group."
EDIT: Just want to be sure you saw my earlier edit, as I was editing while you were replying - you’d said
"The query is trying to sort by sc.event_date, but event_date is not in the sc (site_content) table. You need to specify the name of the tv to sort by in the Ditto call"
..I did have it in the call, but did I also need to add it to the site_content table?
cheers,
Tracy
-
☆ A M B ☆
- 24,524 Posts
Make sure you’ve got the latest version of Ditto installed.
I believe some people have gone through the ditto.class.inc.php file and removed the GROUP BY clause; around line
912
$sql = "SELECT DISTINCT $fields FROM $tblsc sc
LEFT JOIN $tbldg dg on dg.document = sc.id
WHERE sc.id IN (" . join($ids, ",") . ") $published AND sc.deleted=$deleted $where
".($public ? 'AND ('.$access.')' : '')." GROUP BY sc.id" .
($sort ? " ORDER BY $sort" : "") . " $limit ";
Thanks Susan - researched Ditto upgrade there and think I need to upgrade to MODx 0.9.6. Not sure what risks are of data loss or whatever, so need to look into that first, then follow upgrade instructions for Ditto. Thanks a mill for all your help today, much appreciated!
Tracy
This is how I solve it.
edditing assets\snippets\ditto\classes\ditto.class.inc.php line 915
$sql = "SELECT DISTINCT $fields FROM $tblsc sc
LEFT JOIN $tbldg dg on dg.document = sc.id
WHERE sc.id IN (" . join($ids, ",") . ") $published AND sc.deleted=$deleted $where
".($public ? ’AND (’.$access.’)’ : ’’)." GROUP BY ".($sort ? " $sort " : " sc.id ") .
($sort ? " ORDER BY $sort" : ""). " $limit ";
the bold part was : ".($public ? ’AND (’.$access.’)’ : ’’)." GROUP BY sc.id" . --->So always was grouping by id