I have a report that includes join to a custom module. The alias generated in the sql seems to be wrong causing query to fail. I saw this was reported in other thread but not sure where to put the fix. Below is the SQL. It works fine if prop_property.assigned_user_id =‘xxxxx’ replaced with leads:prop_property_leads.assigned_user_id =‘xxxxx’
So the problem is Alias is not being used but rather just table name. somewhere in AOR_Report.php we need to use right alias . Can someone please point to solution.
SELECT `leads:prop_property_leads`.name AS 'Name0', `leads`.last_name AS 'Last_Name1', `leads`.first_name AS 'First_Name2', `leads`.status AS 'Status3', `leads`.status_description AS 'Status_Description4', `leads`.id AS 'leads_id', `leads:prop_property_leads`.id AS 'leads:prop_property_leads_id' FROM `leads` LEFT JOIN prop_property_leads_c `leads|leads:prop_property_leads` ON `leads`.id=`leads|leads:prop_property_leads`.prop_property_leadsleads_idb AND `leads|leads:prop_property_leads`.deleted=0
LEFT JOIN prop_property `leads:prop_property_leads` ON `leads:prop_property_leads`.id=`leads|leads:prop_property_leads`.prop_property_leadsprop_property_ida AND `leads:prop_property_leads`.deleted=0
AND ( prop_property.assigned_user_id ='xxxxx' or EXISTS (SELECT 1
FROM securitygroups secg
INNER JOIN securitygroups_users secu
ON secg.id = secu.securitygroup_id
AND secu.deleted = 0
AND secu.user_id = 'xxxxx'
INNER JOIN securitygroups_records secr
ON secg.id = secr.securitygroup_id
AND secr.deleted = 0
AND secr.module = 'prop_Property'
WHERE secr.record_id = `leads:prop_property_leads`.id
AND secg.deleted = 0) ) WHERE leads.deleted = 0 AND ( leads.assigned_user_id ='xxxxx' or EXISTS (SELECT 1
FROM securitygroups secg
INNER JOIN securitygroups_users secu
ON secg.id = secu.securitygroup_id
AND secu.deleted = 0
AND secu.user_id = 'xxxxx'
INNER JOIN securitygroups_records secr
ON secg.id = secr.securitygroup_id
AND secr.deleted = 0
AND secr.module = 'Leads'
WHERE secr.record_id = leads.id
AND secg.deleted = 0) ) LIMIT 0,20