How to select multiple TVs values without many LEFT JOINs to the same table...?

Development
1

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.

1 Like
2

Joins are the only way to achieve that as far as I know. All relevant columns should be part of the index, so it should be fairly performant (at least given the restraints of how TVs are modeled in the database).

Using joins also offloads the heavy lifting to MySQL rather than MODX, per your request.

To really optimise things, you should not be using resources and TVs for anything that requires a dedicated data model. Create a custom table with the right indexes and optimised model and query that instead. If you don’t expect to go beyond, say, 5000 items (*non scientific estimate) you’re filtering through, then the database queries wont necessarily be a major bottleneck.

2 Likes