{"issue":{"created_at":"2011-11-24T12:34:49Z","state":"open","updated_at":"2011-11-24T12:35:10Z","user":{"gravatar_id":"256406243346b9253f8610fb1d1e05f6","id":1215373,"url":"https://api.github.com/users/importer","login":"importer","avatar_url":"https://secure.gravatar.com/avatar/256406243346b9253f8610fb1d1e05f6?d=https://a248.e.akamai.net/assets.github.com%2Fimages%2Fgravatars%2Fgravatar-140.png"},"comments":18,"body":"In DC_Table, the ORDER for a drop-down field is always its value rather then the label in list View, e.g.\n\n**genre** is a drop-down field with options defined\n\n```\n'options' = array (\n'10' => 'Dance',\n'15' => 'Electronica',\n'26' => 'Reggae',\n)\n```\n\nWhen choosing genre as a filter field, TL correctly retrieves the unique remote labels and sorts them in the filter drop-down. BUT when you select this field as a Grouped item (flag = 11, Group and sort ascending), TL fails to sort the field before using it to order it.\n\nThe problem is that the list view is using a simple ORDER BY genre\ninstead of retrieving the label to sort it on. If you are using a remote table and the value contains the ID, then your sort order will always be wrong, because its sorted on ID, not label.\n\nA possible solution would be to do something like this:\n\n```\nORDER BY FIND_IN_SET(genre, 'label,label2,label3')\n```\n\nwhere labels are pre-sorted similar to the filterMenu drop-down section\n\nThis will obviously not be possible for multiple selections (grouping cannot be done, as multiple values exist)\n\nI'm not sure if this is the same (as Google might not be translating very well)\n#88\n\n[Download the attachments](http://www.contao.org/tl_files/tickets/299.zip \"Download the attachments of this ticket\")\n\n--- _Originally created by thyon on November 27th, 2008, at 07:52am (ID 299)_","assignee":{"gravatar_id":"360d94de41513598ac4440d46a3dab1b","id":1192057,"url":"https://api.github.com/users/leofeyer","login":"leofeyer","avatar_url":"https://secure.gravatar.com/avatar/360d94de41513598ac4440d46a3dab1b?d=https://a248.e.akamai.net/assets.github.com%2Fimages%2Fgravatars%2Fgravatar-140.png"},"pull_request":{"patch_url":null,"html_url":null,"diff_url":null},"id":2340907,"labels":[{"name":"backend","color":"e6e6e6","url":"https://api.github.com/repos/contao/issues/labels/backend"},{"name":"Feature","color":"4183c4","url":"https://api.github.com/repos/contao/issues/labels/Feature"}],"number":299,"title":"DC_Table cannot sort its listView() using a foreignkey.field","closed_at":null,"html_url":"https://github.com/contao/issues/issues/299","milestone":{"created_at":"2011-11-24T11:46:48Z","state":"open","due_on":null,"closed_issues":20,"creator":{"gravatar_id":"360d94de41513598ac4440d46a3dab1b","id":1192057,"url":"https://api.github.com/users/leofeyer","login":"leofeyer","avatar_url":"https://secure.gravatar.com/avatar/360d94de41513598ac4440d46a3dab1b?d=https://a248.e.akamai.net/assets.github.com%2Fimages%2Fgravatars%2Fgravatar-140.png"},"number":1,"description":"TYPOlight 2.7.0 (minor release)","title":"2.7.0","url":"https://api.github.com/repos/contao/issues/milestones/1","open_issues":1},"url":"https://api.github.com/repos/contao/issues/issues/299"},"action":"created","comment":{"created_at":"2011-11-24T12:35:10Z","updated_at":"2011-11-24T12:35:10Z","user":{"gravatar_id":"360d94de41513598ac4440d46a3dab1b","id":1192057,"url":"https://api.github.com/users/leofeyer","login":"leofeyer","avatar_url":"https://secure.gravatar.com/avatar/360d94de41513598ac4440d46a3dab1b?d=https://a248.e.akamai.net/assets.github.com%2Fimages%2Fgravatars%2Fgravatar-140.png"},"body":"It seems that `FIND_IN_SET()` is the only possible solution, however, since it is a MySQL specific command, we actually should not use it. On the other hand, TYPOlight will not work on Oracle or PostgreSQL systems without heavy modifications of either the source code or the database drivers, so we might as well allow MySQL specific code.\n\nPlease try the following new code in system/drivers/DC_Table.php around line 2950\n\n```php\n<?php\nif (is_array($orderBy) && strlen($orderByr0))\n{\n\tforeach ($orderBy as $k=>$v)\n\t{\n\t\tif ($GLOBALS['TL_DCA'][$this->strTable]['fields'][$v]['eval']['findInSet'])\n\t\t{\n\t\t\t$keys = $GLOBALS['TL_DCA'][$this->strTable]['fields'][$v]['options'];\n\n\t\t\tif (array_is_assoc($keys))\n\t\t\t{\n\t\t\t\t$keys = array_keys($keys);\n\t\t\t}\n\n\t\t\t$orderBy[$k] = \"FIND_IN_SET(\" . $v . \", '\" . implode(',', $keys) . \"')\";\n\t\t}\n\t}\n\n\t$query .= \" ORDER BY \" . implode(', ', $orderBy);\n}\n?>\n```\n\nTo make this work, you also need to set\n\n```php\n<?php\n$GLOBALS['TL_DCA'][TABLE]['fields'][FIELD]['eval']['findInSet'] = true;\n?>\n```\n\n--- _Originally created on March 14th, 2009, at 01:03pm_","id":2863202,"url":"https://api.github.com/repos/contao/issues/issues/comments/2863202"}}