[#17363] - Contacts editor / tag ordering generated SQL ambiguous column name error (reopening #12933)
- Closed
- 31 Jul 2017
- Medium
- Build: staging
- # 17363
Steps to reproduce the issue
Create some contacts with ad associated tag.
Create a menu-item of Tagged elements (blog) type.
If you want the blog-view to be able to show some additional contact info (i.e. telephone number) you have to modify the model, either deriving the class model or via a model-override, using MVC plugin, to join the additional fields you need.
Let's use the MVC plugin; copy components/com_tags/models/tag.php file in /code dir and add these lines @ row 157 to join the telephone-number field:
$typesarray = $tagsHelper::getTypes('assocList', $typesr, false);
foreach ($typesarray as $type) {
if ('com_contact.contact' == $type['type_alias']) {
$query->join('LEFT', $this->_db->quoteName('#__contact_details', 'cd') . ' ON (' . $this->_db->quoteName('c.core_title') . ' = ' . $this->_db->quoteName('cd.name') . ')');
$query->select('cd.name AS cd_name, cd.telephone AS cd_telephone, ');
break;
}
}
Extend the view to show the telephone number
Expected result
The matching contacts should be shown with telephone number info
Actual result
Nothing is shown and the query will fail with error: Column 'name' in group statement is ambiguous.
System information (as much as possible)
Joomla 3.7.4 PHP any version from 5.3.10 onwards including PHP 7.
Additional comments
BAD PRACTRICE is in /libraries/cms/helper/tags.php line 622.
Group clause for SQL select statement should qualify field names with table aliases or use the renamed field-name.
Error is solved by adding table alias 'ua' to field 'name' i.e. 'ua.name' or by using the renamed fild-name 'author' in libraries/cms/helper/tags.php @ row 637
| Labels |
Added:
?
|
||
BAD PRACTRICE is in /libraries/cms/helper/tags.php line 622.
Please double check that line as this contains nothing in 3.7.4: https://github.com/joomla/joomla-cms/blob/3.7.4/libraries/cms/helper/tags.php#L622
Here is the PR for the proposed change: https://github.com/joomla/joomla-cms/pull/17364/files
| Category | ⇒ | com_contact |
| Closed_By | franz-wohlkoenig | ⇒ | joomla-cms-bot |
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2017-07-31 15:40:37 |
| Closed_By | ⇒ | franz-wohlkoenig |
Set to "closed" on behalf of @franz-wohlkoenig by The JTracker Application at issues.joomla.org/joomla-cms/17363
closed as having PR #17364
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/17363.
Yes, at the moment the problem won't arise in standard joomla installation.
It arises only in a slightly extended Joomla scenario, in a just non-trivial Joomla application (when a class is derived or the tags model is overriden via MVC plugin to show some additional contacts field).
Anyway it seems that is NOT A GOOD PRACTICE to have a SELECT correctly renaming a field (as in libraries/cms/helper/tags.php @ row 599: "...ua.name END AS author") and later on using the non-renamed and not-table-qualified column 'name'.
It is an ERROR WAITING TO ARISE.
For a better practice the group clause in libraries/cms/helper/tags.php @ row 637 should use the field "author" not "name"; it should read:
->group('m.type_alias, m.content_item_id, m.core_content_id, core_modified_time, core_created_time, core_created_by_alias, author, author_email');
At the moment, if you override the model as described above, inserting a join such as the proposed one, you have to fix the query error via a text substitution, immediately after the above join:
$query->setQuery(str_replace(' name, ', ' author, ', $query));
I think this should be 'fixed'