Column 'account_id' in where clause is ambiguous

We have a pretty annoying situation: a basic Filter for Account Name does not work anymore: we filter by an existing Account name, but see no results

It was sure working in version 7, then it was working on 8, then after some upgrade it stopped.

We have: Version 8.8.0

The Mysql error is pretty evident in the logs

Tue Apr 22 2025 at 10:40:33 [1112][3d34fae9-afbb-2322-3036-5ed106ce103e][FATAL] Mysqli_query failed.
Tue Apr 22 2025 at 10:40:33 [1112][3d34fae9-afbb-2322-3036-5ed106ce103e][FATAL]  Query Failed:  SELECT  opportunities.id , opportunities.modified_user_id , opportunities.created_by , opportunities.assigned_user_id , opportunities.campaign_id , opportunities.currency_id , opportunities_cstm.account_id_c , opportunities_cstm.lead_id_c , opportunities.name , opportunities.date_entered , opportunities.date_modified  , jt0.user_name modified_by_name , jt0.created_by modified_by_name_owner  , 'Users' modified_by_name_mod , jt1.user_name created_by_name , jt1.created_by created_by_name_owner  , 'Users' created_by_name_mod, opportunities.description , opportunities.deleted  , jt2.user_name assigned_user_name , jt2.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, opportunities.opportunity_type  , accounts.name account_name, jtl3.account_id account_id , jt4.name campaign_name , jt4.assigned_user_id campaign_name_owner  , 'Campaigns' campaign_name_mod, opportunities.lead_source , opportunities.amount , opportunities.amount_usdollar , opportunities.date_closed , opportunities.next_step , opportunities.sales_stage , opportunities.probability , opportunities_cstm.last_activity_c  , jt5.name bwres_projects_opportunities_name, jtl5.bwres_projects_opportunitiesbwres_projects_ida bwres_projects_opportunitiesbwres_projects_ida , jt6.name bwadd_workcode_opportunities_name, jtl6.bwadd_workcode_opportunitiesbwadd_workcode_ida bwadd_workcode_opportunitiesbwadd_workcode_ida, opportunities_cstm.license_expire_c , opportunities_cstm.todo_c , opportunities_cstm.partner_lead_ref_c , jt7.name sales_broker_c , opportunities_cstm.platform_c , opportunities_cstm.interest_c , opportunities_cstm.opportunity_subtype_c , opportunities_cstm.lead_sub_source_c , opportunities_cstm.opportunity_name_c , opportunities_cstm.todo_date_c , opportunities_cstm.conv_wf_done_c , opportunities_cstm.lead_source_description_c , opportunities_cstm.sales_tracking_c , LTRIM(RTRIM(CONCAT(IFNULL(jt8.first_name,''),' ',IFNULL(jt8.last_name,'')))) lead_from_c , opportunities_cstm.partner_notes_c , opportunities_cstm.status_description_c , opportunities_cstm.workcode_gen_c , opportunities_cstm.jjwg_maps_address_c , opportunities_cstm.jjwg_maps_geocode_status_c , opportunities_cstm.jjwg_maps_lat_c , opportunities_cstm.jjwg_maps_lng_c , opportunities_cstm.opportunity_description_c  FROM opportunities  LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c   LEFT JOIN  users jt0 ON opportunities.modified_user_id=jt0.id AND jt0.deleted=0

 AND jt0.deleted=0  LEFT JOIN  users jt1 ON opportunities.created_by=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0  LEFT JOIN  users jt2 ON opportunities.assigned_user_id=jt2.id AND jt2.deleted=0

 AND jt2.deleted=0  LEFT JOIN  accounts_opportunities jtl3 ON opportunities.id=jtl3.opportunity_id AND jtl3.deleted=0

 LEFT JOIN  accounts accounts ON accounts.id=jtl3.account_id AND accounts.deleted=0
 AND accounts.deleted=0  LEFT JOIN  campaigns jt4 ON opportunities.campaign_id=jt4.id AND jt4.deleted=0

 AND jt4.deleted=0  LEFT JOIN  bwres_projects_opportunities_c jtl5 ON opportunities.id=jtl5.bwres_projects_opportunitiesopportunities_idb AND jtl5.deleted=0

 LEFT JOIN  bwres_projects jt5 ON jt5.id=jtl5.bwres_projects_opportunitiesbwres_projects_ida AND jt5.deleted=0
 AND jt5.deleted=0  LEFT JOIN  bwadd_workcode_opportunities_c jtl6 ON opportunities.id=jtl6.bwadd_workcode_opportunitiesopportunities_idb AND jtl6.deleted=0

 LEFT JOIN  bwadd_workcode jt6 ON jt6.id=jtl6.bwadd_workcode_opportunitiesbwadd_workcode_ida AND jt6.deleted=0
 AND jt6.deleted=0 LEFT JOIN accounts jt7 ON opportunities_cstm.account_id_c = jt7.id AND jt7.deleted=0  LEFT JOIN leads jt8 ON opportunities_cstm.lead_id_c = jt8.id AND jt8.deleted=0  where ((accounts.name like 'TEST%' ) AND ( account_id like '3aee9fbe-7a75-9cf5-73b3-67a0cb88c215%')) AND opportunities.deleted=0 LIMIT 0,101: MySQL error 1052: Column 'account_id' in where clause is ambiguous
Tue Apr 22 2025 at 10:40:36 [1129][-none-][WARN] ModuleNameMapper | mapName | 'chimp_export' not mapped to 'frontend'
Tue Apr 22 2025 at 10:40:36 [1129][-none-][WARN] ModuleNameMapper | mapName | 'bwres_external_reports' not mapped to 'frontend'
Tue Apr 22 2025 at 10:40:36 [1129][-none-][WARN] ModuleNameMapper | mapName | 'bwres_contracts' not mapped to 'frontend'
Tue Apr 22 2025 at 10:40:36 [1129][-none-][WARN] ModuleNameMapper | mapName | 'bwres_projects' not mapped to 'frontend'
Tue Apr 22 2025 at 10:40:36 [1129][-none-][WARN] ModuleNameMapper | mapName | 'bwres_deliveries' not mapped to 'frontend'
Tue Apr 22 2025 at 10:40:36 [1129][-none-][WARN] ModuleNameMapper | mapName | 'bwres_products' not mapped to 'frontend'
Tue Apr 22 2025 at 10:40:36 [1129][-none-][WARN] ModuleNameMapper | mapName | 'bwres_licenses' not mapped to 'frontend'
Tue Apr 22 2025 at 10:40:36 [1129][-none-][WARN] ModuleNameMapper | mapName | 'bwadd_messenger' not mapped to 'frontend'
Tue Apr 22 2025 at 10:40:36 [1129][-none-][WARN] ModuleNameMapper | mapName | 'bwadd_workcode' not mapped to 'frontend'
Tue Apr 22 2025 at 10:40:36 [1129][-none-][WARN] ModuleNameMapper | mapName | 'bwadd_contract_number' not mapped to 'frontend'
Tue Apr 22 2025 at 10:40:36 [1129][3d34fae9-afbb-2322-3036-5ed106ce103e][ERROR] Unable to load custom logic file: modules/AOD_Index/AOD_LogicHooks.php
Tue Apr 22 2025 at 10:40:36 [1129][3d34fae9-afbb-2322-3036-5ed106ce103e][ERROR] Unable to load custom logic file: modules/AOD_Index/AOD_LogicHooks.php

If I execute that Mysql query and remove the final

AND ( account_id like '3aee9fbe-7a75-9cf5-73b3-67a0cb88c215%')

the query works

it also works if I replace in the query

where ( account_id like '3aee9fbe-7a75-9cf5-73b3-67a0cb88c215%')

with

where ( jtl3.account_id like '3aee9fbe-7a75-9cf5-73b3-67a0cb88c215%')

which solves the ambiguity

does anybody have an idea on how to fix this ambiguity?

When you use above query; MySQL isn’t sure which table’s account_id you’re referring to—especially if multiple joined tables include a column with that name.

In above query; you’re clearing up the confusion by telling MySQL, "Use the account_id column from the jtl3 table

What is your doubt? :thinking: