4

On a site that I have been working on for a long time, I suddenly came across this issue. When I went to com_plugins to change settings on my plugins, I got the error below and the plugins list didn't show.

Error

Column 'type' in where clause is ambiguous

SQL=SELECT COUNT(*) FROM `#__extensions` AS a LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access WHERE `type` = 'plugin' AND (a.enabled IN (0, 1)) AND a.state >= 0

Column 'type' in where clause is ambiguous

SQL=SELECT a.extension_id , a.name, a.element, a.folder, a.checked_out, a.checked_out_time, a.enabled, a.access, a.ordering,uc.name AS editor,ag.title AS access_level FROM `#__extensions` AS a LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access WHERE `type` = 'plugin' AND (a.enabled IN (0, 1)) AND a.state >= 0 ORDER BY `folder` asc,a.ordering ASC LIMIT 0, 20

Column 'type' in where clause is ambiguous

SQL=SELECT COUNT(*) FROM `#__extensions` AS a LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access WHERE `type` = 'plugin' AND (a.enabled IN (0, 1)) AND a.state >= 0

The where type= 'plugin' clause is ambiguous. I checked the plugins model of the com_plugins in a clean joomla installation, as well as in a few older versions of J3. The part of the code in question is at about line 211, in the getListQuery() method.

// Select the required fields from the table.
    $query->select(
        $this->getState(
            'list.select',
            'a.extension_id , a.name, a.element, a.folder, a.checked_out, a.checked_out_time,' .
                ' a.enabled, a.access, a.ordering'
        )
    )
        ->from($db->quoteName('#__extensions') . ' AS a')
        ->where($db->quoteName('type') . ' = ' . $db->quote('plugin'));

All installations come like this and the strange thing is that it was always working fine in any Joomla site, but suddenly not on this one.

Running the same query directly in Joomla databases, it works for other Joomla db installations but not on this one, where it also gives the same error about the ambiguous column 'type' in the where.

Changing the query in the method from where type = 'plugin' to where a.type = 'plugin', which makes sense since this is a joined list, and this way the type field is referencing its table, the issue is gone.

Has anyone notice this kind of issue with the com_plugins?

  • Could you also confirm you MySQL version? – Lodder Nov 28 '14 at 11:44
  • @Lodder: MySQL 5.5.36 on the live server and also 5.5.29 on my localost. – FFrewin Nov 28 '14 at 11:54
1

For aliasing a table, you replace your current code:

->from($db->quoteName('#__extensions') . ' AS a')

with:

->from($db->quoteName('#__extensions', 'a'))

This will automatically generate AS a.

Now seeing that you're using an alias, you need to replace:

->where($db->quoteName('type')

with:

->where($db->quoteName('a.type')
  • The thing is that it's not me that is using it, but it's in the core. – FFrewin Nov 28 '14 at 11:56
  • Can you check your joomla plugins model file line 211 and tell me what you see there? – FFrewin Nov 28 '14 at 12:01
  • Yup, will do in a bit – Lodder Nov 28 '14 at 12:12
  • @FFrewin - It's the same as as the code you've provided but I don't seems to get any errors. Try going to Extensions >> Extensions Manager >> Database and see if there is anything to fix. I have tried setting my error reporting to maximum and my MySQL version is 5.5.24 which is close to yours but I don't get any errors – Lodder Nov 28 '14 at 12:39
  • I have done so (fixing database)... I also don't get any errors on any other Joomla. Even on this website everything was ok a few days ago. – FFrewin Nov 28 '14 at 13:07
0

Did you check to confirm that this one site DID NOT have a type column in the #__users and/or #__viewlevels tables?

The only possible cause that I can fathom is that one (or both, but I doubt it) of those tables gained a type column as an unintended fluke of development. Is there any chance that you loaded an extension that made a change to one of these core tables?

The ambiguous column error can only be generated as a result of multiple tables containing the same column.

As Lodder has stated, the way to alleviate the error without altering the database is to make explicit declarations of the table name (or table alias) from which the target column belongs.

That said, because Joomla's core files are not written to nominate a tablename before the type column, it would be advisable to seek out the table with the extra type column (save a backup of the table), remove the rogue type column from the table, then run everything and see what breaks when the unexpected column is missing. This forensic process will be integral in discovering the source of the issue. Then again, if nothing breaks after removing the column, then the cause will be a mystery, but then the error should be permanently removed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.