SQL error when sorting channel entries by a custom field.

[jonathandixon]

Description of the problem
When using a custom field in the orderby option of the exp:channel:entries the generated SQL is incorrect. Also, there is a notice that a PHP variable is not defined.

How To Reproduce
Steps to reproduce the behavior:

1. Install EE with the default theme.
2. Enable Site Manager (under settings).
3. Create a new site.
4. Edit the default_site/home.group/index.html template the following channel entries tag for the list of blog entries. (The important part is adding orderby='seo_title' site="not random" as parameters to the channel entries tag.)
5. Visit the site home page.

{exp:channel:entries orderby='seo_title' site="not random" channel='{ch}' disable='{ch_disable}' limit='4'}
  {!-- listing as a snippet, as it's used through more than one template --}
  {snp_blog_list}
  {!-- no results --}
  {if no_results}
    <div class="alert warn no-results">
      <p>{gv_entries_none}</p>
    </div>
  {/if}
{/exp:channel:entries}

Error Messages

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Array' in 'field list':
SELECT DISTINCT t.entry_id , exp_channels.channel_id , t.sticky , Array FROM exp_channel_titles AS t LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id LEFT JOIN exp_channel_data_field_72 ON exp_channel_data_field_72.entry_id = t.entry_id WHERE t.entry_id != '' AND t.site_id IN ('1','2') AND t.entry_date < 1550500263 AND (t.expiration_date = 0 OR t.expiration_date > 1550500263) AND t.channel_id IN (7) AND exp_categories.cat_id IN ('9','10','11','12','13','14') AND t.status = 'open' AND t.status != 'closed'

#0 ee/legacy/database/drivers/mysqli/mysqli_driver.php(112): CI_DB_mysqli_connection->query('SELECT DISTINCT...')
#1 ee/legacy/database/DB_driver.php(270): CI_DB_mysqli_driver->_execute('SELECT DISTINCT...')
#2 ee/legacy/database/DB_driver.php(180): CI_DB_driver->simple_query('SELECT DISTINCT...')
#3 ee/EllisLab/Addons/channel/mod.channel.php(2320): CI_DB_driver->query('SELECT DISTINCT...')
#4 ee/EllisLab/Addons/channel/mod.channel.php(246): Channel->build_sql_query()
#5 ee/legacy/libraries/Template.php(1845): Channel->entries()
#6 ee/legacy/libraries/Template.php(1480): EE_Template->process_tags()
#7 ee/legacy/libraries/Template.php(576): EE_Template->tags()
#8 ee/legacy/libraries/Template.php(232): EE_Template->parse('{!-- ra:0000000...', false, 1, false)
#9 ee/legacy/libraries/Template.php(165): EE_Template->fetch_and_parse('news-and-events', Array, false)
#10 ee/legacy/libraries/Core.php(661): EE_Template->run_template_engine('news-and-events', 'news-list')
#11 ee/legacy/controllers/ee.php(63): EE_Core->generate_page()
#12 [internal function]: EE->index()
#13 ee/EllisLab/ExpressionEngine/Core/Core.php(241): call_user_func_array(Array, Array)
#14 ee/EllisLab/ExpressionEngine/Core/Core.php(110): EllisLab\ExpressionEngine\Core\Core->runController(Array)
#15 ee/EllisLab/ExpressionEngine/Boot/boot.php(151): EllisLab\ExpressionEngine\Core\Core->run(Object(EllisLab\ExpressionEngine\Core\Request))
#16 index.php(179): require_once('...')
#16 index.php(179): require_once('...')

Environment Details:

• Version: 5.0.1
• PHP Version 7.2
• MySQL Version 5.7
• OS: Linux Docker Containers on MacOS
• Web Server: Apache + PHP-fpm

Possible Solution

Implode the field list on these two lines

$end .= "CONCAT(".implode(', ', $field_list).")";
$distinct_select .= ', '.implode(', ', $field_list).' ';

And change the concatenation assignment to just an assignment on this line

$join = "LEFT JOIN exp_channel_data_field_{$field_id} ON exp_channel_data_field_{$field_id}.entry_id = t.entry_id ";

[kevincupp]

The steps to reproduce this seem very incomplete. The code you're referencing only runs when pulling entries from multiple sites and/or ordering by multiple fields, and the query in your error is pulling from multiple sites, but there's no mention of that in the instructions and your channel entries tag is very simple. Is this really all that's needed to reproduce the issue from a fresh install? I can't reproduce, and I imagine we would have heard about this by now if it were that simple to reproduce, ordering by a custom field is a very common task and this code rarely changes. But, I could see there being problems if you had a more narrow use case like ordering by fields from different sites.

[jonathandixon]

Sorry for the incomplete steps to reproduce. I've updated the instructions to make more sense.

[kevincupp]

Thanks, that was helpful! Your recommended solution seems good as well, I can do it, but consider opening a PR when you think you know of a fix.