Subquery in Where Clause

80 Posts

smg6511v2 Reply #1, 7 years, 10 months ago

I'm working on a relatively complex query and am having trouble figuring out how to add the following into my where clause:

&& EXISTS (
	SELECT 1
	FROM TaggerTagResources ttr 
	WHERE ttr.tag IN (96,100,101) AND ttr.resource = modResource.id 
	)

I know how to do this by assembling the whole sql query and passing it into xPDOCriteria, but I'd like to know if it's possible to do with the typical modx methods. Thanks for any insights you can offer!

128 Posts

Michael Engel Reply #2, 7 years, 10 months ago

I have created some query`s like the following.

    $c = $modx->newQuery('Deals');

    $subquery = $modx->newQuery('Adressen');
    $subquery->select('1');
    $subquery->where(array('Deals.id = Adressen.deal_id'));
    $subquery->limit(1);
    $subquery->prepare();

    $c->where(array('(' . $subquery->toSQL() . ') > 0'));

Build the Subquery with xPDO and use ->toSQL() inside an where. When you are not passing key => value where conditions are directly used as condition. No Shure if this is the best way, but it works quite good.

80 Posts

smg6511v2 Reply #3, 7 years, 10 months ago

Thanks for your response, Michael. I'd also found that, after looking more closely into modx's query classes, I could just pass a string into the where method. The documentation leads you to believe you have to pass a key-value in an array, but that's not the case. Here's what I ended up using:

$c->where("EXISTS (SELECT 1  FROM {$modx->getTableName('TaggerTagResource')} TaggerTagResource WHERE TaggerTagResource.tag IN (96,100,101) && TaggerTagResource.resource = modResource.id)");

Note that you cannot assign a custom alias to the table queried, as I'd originally done (ttr). It must be the one that would've been retrieved from its class (TaggerTagResource), particularly if that class is being used elsewhere in your query.

Also, this portion of the query handles cases where a subcategory of tags is chosen in addition to a main category. (A record for each should exist for both to return a result.) Tagger doesn't have a data structure that allows you to simply query "category IN(a,b,c) AND subcategory IN(d,e,f)." For this reason, it was important to use EXISTS when a subcategory was added to the query. [ed. note: smg6511v2 last edited this post 7 years, 10 months ago.]