Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL error when searching channel by custom field across multiple sites. #134

Closed
jonathandixon opened this Issue Feb 18, 2019 · 0 comments

Comments

Projects
None yet
2 participants
@jonathandixon
Copy link

jonathandixon commented Feb 18, 2019

Description of the problem
In a multi-site setup using the search:my_date_field=">={current_time format='%U'}" option to search entries across sites results in a SQL error. The generated SQL attempts to join the channel data field table multiple times.

How To Reproduce
Steps to reproduce the behavior:

  1. Create Multiple Sites
  2. Create a custom date field.
  3. Create a channel that uses the custom field (only has to be on one site).
  4. In a template use the code below.

Error Messages

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'exp_channel_data_field_111':
SELECT t.entry_id , exp_channels.channel_id 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 LEFT JOIN exp_channel_data_field_111 ON exp_channel_data_field_111.entry_id = t.entry_id LEFT JOIN exp_channel_data_field_111 ON exp_channel_data_field_111.entry_id = t.entry_id LEFT JOIN exp_channel_data_field_105 ON exp_channel_data_field_105.entry_id = t.entry_id WHERE t.entry_id != '' AND t.site_id IN ('1','2') AND t.entry_date < 1550512174 AND (t.expiration_date = 0 OR t.expiration_date > 1550512174) AND t.channel_id IN (18) AND t.status = 'open' AND t.status != 'closed' AND (( wd.site_id = 1 AND exp_channel_data_field_111.field_id_111 >= 1550512174) OR ( wd.site_id = 2 AND exp_channel_data_field_111.field_id_111 >= 1550512174))ORDER BY t.sticky desc, CONCAT(exp_channel_data_field_105.field_id_105, exp_channel_data_field_105.field_id_105) asc, t.entry_id asc LIMIT 0, 1

#0 ee/legacy/database/drivers/mysqli/mysqli_driver.php(112): CI_DB_mysqli_connection->query('SELECT t.entry_...')
#1 ee/legacy/database/DB_driver.php(270): CI_DB_mysqli_driver->_execute('SELECT t.entry_...')
#2 ee/legacy/database/DB_driver.php(180): CI_DB_driver->simple_query('SELECT t.entry_...')
#3 ee/EllisLab/Addons/channel/mod.channel.php(2412): CI_DB_driver->query('SELECT t.entry_...')
#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...', true, 1, false)
#9 ee/legacy/libraries/Template.php(1133): EE_Template->fetch_and_parse('home', '_news-events', true, 1)
#10 ee/legacy/libraries/Template.php(1014): EE_Template->process_sub_templates('{!-- ra:0000000...')
#11 ee/legacy/libraries/Template.php(623): EE_Template->process_layout_template('{!-- ra:0000000...', NULL)
#12 ee/legacy/libraries/Template.php(232): EE_Template->parse('{!-- ra:0000000...', false, 1, false)
#13 ee/legacy/libraries/Template.php(165): EE_Template->fetch_and_parse('home', Array, false)
#14 ee/legacy/libraries/Core.php(661): EE_Template->run_template_engine('home', 'index')
#15 ee/legacy/controllers/ee.php(63): EE_Core->generate_page()
#16 [internal function]: EE->index()
#17 ee/EllisLab/ExpressionEngine/Core/Core.php(241): call_user_func_array(Array, Array)
#18 ee/EllisLab/ExpressionEngine/Core/Core.php(110): EllisLab\ExpressionEngine\Core\Core->runController(Array)
#19 ee/EllisLab/ExpressionEngine/Boot/boot.php(151): EllisLab\ExpressionEngine\Core\Core->run(Object(EllisLab\ExpressionEngine\Core\Request))
#20 index.php(179): require_once('...')
#20 index.php(179): require_once('...')

Template Code

{exp:channel:entries channel="events"
        search:common_end_time=">={current_time format='%U'}"
        site="not random"}
    {title}
{/exp:channel:entries}

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
Only do the LEFT JOIN if the table is not already joined. See, https://github.com/ExpressionEngine/ExpressionEngine/blob/master/system/ee/EllisLab/Addons/channel/mod.channel.php#L2026

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.