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

Order By exception when order using view_count_one #2621

Closed
ccbangler opened this issue Dec 8, 2022 · 0 comments · Fixed by #2632
Closed

Order By exception when order using view_count_one #2621

ccbangler opened this issue Dec 8, 2022 · 0 comments · Fixed by #2632
Labels
Bug: Accepted Bug has been confirmed, is reproducible, and ready to work on.

Comments

@ccbangler
Copy link

EE 6.4.2
This functionality works fine on the live version which runs using MariaDb.

The error is on the local version using MAMP with MySQL 5.7.34 and php 7.4.21.

This is to show a list of the top 5 trending entries for the specific category being viewed in the last week (start on tweaked in dev environment because entries are older)

URL
localhost:8888/site/news/category/category-name

{exp:channel:entries channel="news" status="open" orderby="view_count_one" sort="desc" start_on="-5 weeks" search:image_1="not IS_EMPTY" limit="5" sticky="n" dynamic="yes"}

For some reason the orderby="view_count_one" is not being accepted.

Exception Caught SQLSTATE[HY000]: General error: 3065 Expression #2 of ORDER BY clause is not in SELECT list, references column 'ajot_ee.t.entry_date' which is not in SELECT list; this is incompatible with DISTINCT: SELECT DISTINCT t.entry_id , exp_channels.channel_id , t.view_count_one 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 wd.entry_id = t.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 WHERE t.entry_id != '' AND t.site_id IN ('1') AND t.entry_date <= 1670518054 AND (t.expiration_date = 0 OR t.expiration_date > 1670518054) AND t.channel_id IN (1,2,23,24) AND t.entry_date >= '1657295256' AND exp_categories.cat_id = '1' AND t.status IN ('open','featured') AND t.status != 'closed' AND ( (wd.site_id=1 AND wd.field_id_4!="" AND wd.site_id=1 AND wd.field_id_4 IS NOT NULL) )ORDER BY t.view_count_one desc, t.entry_date desc, t.entry_id desc LIMIT 0, 5

ee/legacy/database/drivers/mysqli/mysqli_connection.php:114
Stack Trace: Please include when reporting this error

#0 ee/legacy/database/drivers/mysqli/mysqli_driver.php(112): CI_DB_mysqli_connection->query('SELECT DISTINCT...')
#1 ee/legacy/database/DB_driver.php(260): CI_DB_mysqli_driver->_execute('SELECT DISTINCT...')
#2 ee/legacy/database/DB_driver.php(175): CI_DB_driver->simple_query('SELECT DISTINCT...')
#3 ee/ExpressionEngine/Addons/channel/mod.channel.php(2039): CI_DB_driver->query('SELECT DISTINCT...')
#4 ee/ExpressionEngine/Addons/channel/mod.channel.php(211): Channel->build_sql_query()
#5 ee/legacy/libraries/Template.php(1763): Channel->entries()
#6 ee/legacy/libraries/Template.php(1439): EE_Template->process_tags()
#7 ee/legacy/libraries/Template.php(603): EE_Template->tags()
#8 ee/legacy/libraries/Template.php(236): EE_Template->parse('{!-- ra:0000000...', false, 1, true)
#9 ee/legacy/libraries/Template.php(993): EE_Template->fetch_and_parse('layout', '_channel', false, 1, true)
#10 ee/legacy/libraries/Template.php(649): EE_Template->process_layout_template('{!-- ra:0000000...', Array)
#11 ee/legacy/libraries/Template.php(236): EE_Template->parse('{!-- ra:0000000...', false, 1, false)
#12 ee/legacy/libraries/Template.php(166): EE_Template->fetch_and_parse('', Array, false)
#13 ee/legacy/libraries/Core.php(768): EE_Template->run_template_engine('', '')
#14 ee/legacy/controllers/ee.php(53): EE_Core->generate_page()
#15 [internal function]: EE->index('air-cargo')
#16 ee/ExpressionEngine/Core/Core.php(266): call_user_func_array(Array, Array)
#17 ee/ExpressionEngine/Core/Core.php(122): ExpressionEngine\Core\Core->runController(Array)
#18 ee/ExpressionEngine/Boot/boot.php(161): ExpressionEngine\Core\Core->run(Object(ExpressionEngine\Core\Request))
#19 index.php(171): require_once('...')
#19 index.php(171): require_once('...') 
@intoeetive intoeetive added the Bug: Accepted Bug has been confirmed, is reproducible, and ready to work on. label Dec 9, 2022
intoeetive added a commit that referenced this issue Dec 10, 2022
… when used on category page could throw error on MySQL 5.7
intoeetive added a commit that referenced this issue Dec 10, 2022
… when used on category page could throw error on MySQL 5.7
matthewjohns0n added a commit that referenced this issue Dec 12, 2022
…count

Resolved #2621 where `{exp:channel:entries orderby="view_count_one"}` when used on category page could throw error on MySQL 5.7
matthewjohns0n added a commit that referenced this issue Dec 12, 2022
…count

Resolved #2621 where `{exp:channel:entries orderby="view_count_one"}` when used on category page could throw error on MySQL 5.7
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Bug: Accepted Bug has been confirmed, is reproducible, and ready to work on.
Projects
None yet
2 participants