DBAPI Query Help

I'm working on a custom snippet for my site that essentially finds all resources where a specified template variable equals X (where X is a user submitted value). For instance, if a user selected "Mexico" from a drop down the query would fetch all resources with a specific TV set to "Mexico".

I've been reading about DBAPI query as well as the template variable tables but I'm still a bit confused. I'm a bit shaky on how these tables relate and what my query should look like. Could someone point me in the right direction?

I'm using Modx Evolution 1.0.6

hi,

quite easy actually, you'll just have to play with 2 tables max, one being enough if you use tv id instead of it's name
yourprefixe_site_tmplvar_contentvalues
is the table where tv values are stored, relations are
- contentid, the ressource id
- tmplvarid the tv id
- value, well do i need to explain
then will return all the ressource ids you're looking for

now if you prefer to work with tv names, you'll just have to add a sub select to your request

should do the trick (yourprefixe_site_tmplvars being the table where all yout tv's id, name, type an so on are stored)

have swing

Thanks, that works really well.

How could I further refine this by using additional TVs? For instance, finding all documents with tv1="Mexico" and tv2="Hotels"? I was able to get it to return them by using this:


...but, as expected, this just returns all document ids that meet either criteria individually. I suppose I could search through the results and find the overlap but that seems really clunky.

Should I continue to use the OR or is there a good way to just return docs with tv1="X" and tv2="Y"?