How to select multiple TVs values without many LEFT JOINs to the same table? (modx_site_tmplvar_contentvalues)
I need to get IDs of resources with certain TV values:
Resources,
WHERE TV “feedback” value is 1
AND
WHERE TV “date” value is not empty (exists in modx_site_tmplvar_contentvalues)
I have this at now:
SELECT * FROM `modx_tickets_threads` threads LEFT JOIN `modx_site_tmplvar_contentvalues` tv_feedback ON threads.resource=tv_feedback.contentid LEFT JOIN `modx_site_tmplvar_contentvalues` tv_date ON tv_feedback.contentid=tv_date.contentid WHERE threads.resource IN (...) AND tv_feedback.tmplvarid={$tv_feedback} AND tv_feedback.value=1 AND tv_date.value NOT NULL
But there are two LEFT JOINs to the same table (modx_site_tmplvar_contentvalues).
How to make it right and optimize this highload/hardcoded/overloaded query?
I also won’t to use MODX for this task, because it will use PHP which will use redundant/excess/unnecessary server resources (RAM/CPU), when this can be achieved by MySQL itself (without creating, store and handle a bunch of PHP Objects, on which MODX is based). It’s better to make some optimized queries and delete MODX cache folders of needed resources.