Tim Moore asked 3 years ago
Anyone know how to keep filters working using this or similar query?
$g->select_command = “SELECT * FROM tbl_customer
LEFT JOIN (SELECT fk_customer, count(*) as total
FROM tbl_job GROUP BY fk_customer) as t ON fk_customer = customer_id
LEFT JOIN (SELECT fk_customer_id, count(*) as tcontact
FROM tbl_contacts GROUP BY fk_customer_id) as tc ON fk_customer_id = customer_id
“;
it tries to use the joins in the filter: below is the error
Couldn’t execute query. Unknown column ‘company_name’ in ‘where clause’ – SELECT count(*) as c FROM (SELECT * FROM tbl_customer LEFT JOIN (SELECT fk_customer, count(*) as total FROM tbl_job WHERE 1=1 AND ( `company_name` LIKE ‘%d%’ ) GROUP BY fk_customer) as t ON fk_customer = customer_id LEFT JOIN (SELECT fk_customer_id, count(*) as tcontact FROM tbl_contacts GROUP BY fk_customer_id) as tc ON fk_customer_id = customer_id ) pg_tmp
Thanks,
Tim
Abu Ghufran Staff answered 3 years ago
I’ll be updating here again after regenerating this query testcase.
Abu Ghufran Staff answered 3 years ago
Hello,
Apologies for the extreme delay.
I tested this query case and it works with the latest build. I saw that You recently updated the subscription and the updated build should solve it.
v2.8 output:
SELECT count(*) as c FROM (SELECT * FROM tbl_customer LEFT JOIN (SELECT fk_customer, count(*) as total FROM tbl_job GROUP BY fk_customer) as t ON fk_customer = customer_id LEFT JOIN (SELECT fk_customer_id, count(*) as tcontact FROM tbl_contacts GROUP BY fk_customer_id) as tc ON fk_customer_id = customer_id WHERE 1=1 AND ( `company_name` LIKE ‘%jo%’ )) pg_tmp
