Today I came across something interesting related related fields under opportunities and I think it’s related to this topic.
For convenience I added relate field MyGroup(mygroup_c) (a relate field to security_groups) on Contacts to allow me to filter records based on my security groups. When filtering, the system created the proper join to the security_groups table based on the securitygroup_id_c field from table contacts_cstm (This is the field Studio created when I created my relate field MyGroup). All works fine when filtering.
I created field MyGroup(mygroup_c) on Opportunities as well. Studio created field securitygroup_id_c on table opportunities_cstm as espected. Then I added the field MyGroup to the Filter view. Now, when trying to filter based MyGroup, the query the system generates does not create the proper JOIN to the security_groups table.
This the query in log:
Tue Jul 16 11:29:45 2019 [INFO] Query:SELECT opportunities.id ,opportunities_cstm.our_op_type_c, opportunities.name , accounts.name account_name, jtl0.account_id account_id, opportunities.sales_stage , opportunities.opportunity_type , opportunities.amount , opportunities.currency_id , opportunities.date_closed , opportunities.date_entered , opportunities.assigned_user_id FROM opportunities LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c LEFT JOIN accounts_opportunities jtl0 ON opportunities.id=jtl0.opportunity_id AND jtl0.deleted=0 LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0 where ((mygroup_c like '620%')) AND opportunities.deleted=0 ORDER BY opportunities.name ASC LIMIT 0,51
Tue Jul 16 11:29:45 2019 [FATAL] Mysqli_query failed.
I tried that on version Version 7.10.13.
If you have more insides please let me know.