Administrator com_plugins issue/bug (?) - Error Column 'type' in where clause is ambiguous

Question

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?

Answer 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')

Answer 2

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.