[com_menus] - wrong group by when multilanguages enabled - postgresql

[alikon]

Pull Request for fix regression from #12051 .
redo of #12676 for fix conflicts

Summary of Changes

fixed wrong group by when multilanguages enabled

Testing Instructions

• latest staging fresh multilanguage install

• Go to Menu -> All menu items
  

• postgresql - got sql error wrong group by

• mysql - should work as before

[andrepereiradasilva]

can we reproduce this in mysql with different sql_mode so we can make a test with mysql?

[andrepereiradasilva]

i tried doing the same changes done in https://github.com/joomla/joomla-cms/pull/12494/files#diff-21345ddf44224657f5e2f7a0ea63d7d5 so mysqli behaves more or less like postgresql and sqlsrv but i get another error: a.menutype

-- 'joomla-staging.a.menutype' isn't in GROUP BY SQL=
SELECT `a`.`id`,`a`.`menutype`,`a`.`title`,`a`.`alias`,`a`.`note`,`a`.`path`,`a`.`link`,`a`.`type`,`a`.`parent_id`,`a`.`level`,`a`.`published` AS `a.published`,`a`.`component_id`,`a`.`checked_out`,`a`.`checked_out_time`,`a`.`browserNav`,`a`.`access`,`a`.`img`,`a`.`template_style_id`,`a`.`params`,`a`.`lft`,`a`.`rgt`,`a`.`home`,`a`.`language`,`a`.`client_id`,CASE WHEN a.type = 'component' THEN a.published+2*(e.enabled-1) WHEN a.type = 'url'AND a.published != -2 THEN a.published+2 WHEN a.type = 'url'AND a.published = -2 THEN a.published-1 WHEN a.type = 'alias'AND a.published != -2 THEN a.published+4 WHEN a.type = 'alias'AND a.published = -2 THEN a.published-1 WHEN a.type = 'separator'AND a.published != -2 THEN a.published+6 WHEN a.type = 'separator'AND a.published = -2 THEN a.published-1 WHEN a.type = 'heading'AND a.published != -2 THEN a.published+8 WHEN a.type = 'heading'AND a.published = -2 THEN a.published-1 END AS published ,l.title AS language_title, l.image AS language_image, l.sef AS language_sef,u.name AS editor,c.element AS componentname,ag.title AS access_level,`mt`.`id` AS `menutype_id`,`mt`.`title` AS `menutype_title`,COUNT(asso2.id)>1 as association,e.name AS name
FROM `#__menu` AS a
LEFT JOIN `#__languages` AS l ON l.lang_code = a.language
LEFT JOIN `#__users` AS u ON u.id = a.checked_out
LEFT JOIN `#__extensions` AS c ON c.extension_id = a.component_id
LEFT JOIN #__viewlevels AS ag ON ag.id = a.access
LEFT JOIN `#__menu_types` AS `mt` ON `mt`.`menutype` = `a`.`menutype`
LEFT JOIN #__associations AS asso ON asso.id = a.id AND asso.context='com_menus.item'
LEFT JOIN #__associations AS asso2 ON asso2.key = asso.key
LEFT JOIN #__extensions AS e ON e.extension_id = a.component_id WHERE a.id > 1 AND a.client_id = 0 AND (a.published IN (0, 1)) AND a.menutype IN('mainmenu','mainmenu-en-gb','mainmenu-fr-fr','mainmenu-pt-pt')
GROUP BY a.id, e.enabled, l.title, l.image, u.name, c.element, ag.title, e.name, mt.id, mt.title
ORDER BY a.lft ASC
LIMIT 20

-- 'joomla-staging.a.menutype' isn't in GROUP BY SQL=
SELECT `a`.`id`,`a`.`menutype`,`a`.`title`,`a`.`alias`,`a`.`note`,`a`.`path`,`a`.`link`,`a`.`type`,`a`.`parent_id`,`a`.`level`,`a`.`published` AS `a.published`,`a`.`component_id`,`a`.`checked_out`,`a`.`checked_out_time`,`a`.`browserNav`,`a`.`access`,`a`.`img`,`a`.`template_style_id`,`a`.`params`,`a`.`lft`,`a`.`rgt`,`a`.`home`,`a`.`language`,`a`.`client_id`,CASE WHEN a.type = 'component' THEN a.published+2*(e.enabled-1) WHEN a.type = 'url'AND a.published != -2 THEN a.published+2 WHEN a.type = 'url'AND a.published = -2 THEN a.published-1 WHEN a.type = 'alias'AND a.published != -2 THEN a.published+4 WHEN a.type = 'alias'AND a.published = -2 THEN a.published-1 WHEN a.type = 'separator'AND a.published != -2 THEN a.published+6 WHEN a.type = 'separator'AND a.published = -2 THEN a.published-1 WHEN a.type = 'heading'AND a.published != -2 THEN a.published+8 WHEN a.type = 'heading'AND a.published = -2 THEN a.published-1 END AS published ,l.title AS language_title, l.image AS language_image, l.sef AS language_sef,u.name AS editor,c.element AS componentname,ag.title AS access_level,`mt`.`id` AS `menutype_id`,`mt`.`title` AS `menutype_title`,COUNT(asso2.id)>1 as association,e.name AS name
FROM `#__menu` AS a
LEFT JOIN `#__languages` AS l ON l.lang_code = a.language
LEFT JOIN `#__users` AS u ON u.id = a.checked_out 
LEFT JOIN `#__extensions` AS c ON c.extension_id = a.component_id
LEFT JOIN #__viewlevels AS ag ON ag.id = a.access
LEFT JOIN `#__menu_types` AS `mt` ON `mt`.`menutype` = `a`.`menutype`
LEFT JOIN #__associations AS asso ON asso.id = a.id AND asso.context='com_menus.item'
LEFT JOIN #__associations AS asso2 ON asso2.key = asso.key
LEFT JOIN #__extensions AS e ON e.extension_id = a.component_id
WHERE a.id > 1 AND a.client_id = 0 AND (a.published IN (0, 1)) AND a.menutype IN('mainmenu','mainmenu-en-gb','mainmenu-fr-fr','mainmenu-pt-pt')
GROUP BY a.id, e.enabled, l.title, l.image, u.name, c.element, ag.title, e.name, mt.id, mt.title
ORDER BY a.lft ASC

[brianteeman]

I have tested this item ✅ successfully on 4dd07e3

Confirmed issue in postgres
PR fixes issue in postgres and mysql still works as expected

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/12999.}

[waader]

I have tested this item ✅ successfully on 4dd07e3

Thanks alikon!

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/12999.}

[brianteeman]

RTC

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/12999.}