xPDO Getting results for Many to Many table with multiple matches [SOLVED]

14 Posts

Thomas Reply #1, 14 years, 4 months ago

I have a many-to-many database structure that matches "classifieds" to "categories" through "a classifieds_categories" join table.

This works for matching any of the category ids (searchArray are the category ids) and gives results that either match ID 2 or ID 24.

$searchArray = array(2,24);
$search = array('CtcmCategories.id:IN' => $searchArray);
$classifieds = $modx->getCollectionGraph('CtcmClassifieds', '{"CtcmClassifiedsCategories":{"CtcmCategories":{"CtcmCategoryTypes":{}}}}', $search);


foreach ($classifieds as $classified) {
    $output .=  $classified->get('title').' ('.$classified->get('id').')
';
    foreach ($classified->getMany('CtcmClassifiedsCategories') as $classifiedCategories) {
      $output .=  "A Classified with a catgory of " . $classifiedCategories->getOne('CtcmCategories')->get('label') . " and a type of ".$classifiedCategories->getOne('CtcmCategories')->getOne('CtcmCategoryTypes')->get('label')." was found.
";
    }
}

Here is the problem:

I need to get only the records that match all the categories - so results that have both a category ID 2 AND a category ID 24 in the join table.

This works in the MySQL database:

SELECT * FROM `modx_ctcm_classifieds`
INNER JOIN (
  SELECT classified_id
  FROM `modx_ctcm_classifieds_categories`
  WHERE category_id IN (2,24)
  GROUP BY classified_id
  HAVING count(*) > 1
) valid ON `modx_ctcm_classifieds`.id = valid.classified_id

How do I get this into xPDO efficiently?? Does anyone have any pointers? I have a feeling that I'm missing something simple here...this shouldn't be that difficut!

Any help is greatly appreciated!!

Thomas [ed. note: twincreekmedia last edited this post 14 years, 4 months ago.]

5,888 Posts

Bruno17 Reply #2, 14 years, 4 months ago

not sure, if something like this will do it, its untested:

$c = $modx->newQuery('CtcmClassifiedsCategories');
$c->select('classified_id');
$c->where(array('category_id:IN'=>$searchArray));
$c->prepare();
$subquery = $c->toSql();

$c = $modx->newQuery('CtcmClassifieds');
$c->where('EXISTS('.$subquery.')', xPDOQuery::SQL_AND);
$collection = $modx->getCollection('CtcmClassifieds',$c);

[ed. note: Bruno17 last edited this post 14 years, 4 months ago.]

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

14 Posts

Thomas Reply #3, 14 years, 4 months ago

Thanks Bruno, but that doesn't solve the problem unfortunately (returns all rows). The "EXISTS" part seems to match any of the IDs in that table. I made some modifications from your suggestings.

The $subquery works by itself. I'm stuck at attaching it to and INNER JOIN as it seems that xPDO->innerJoin needs a class to work and can't handle a direct subquery.

$c = $modx->newQuery('CtcmClassifiedsCategories');
$c->select('classified_id');
$c->where(array('category_id:IN'=>$searchArray));
$c->groupby('classified_id');
$c->having('count(*) > 1');
$c->prepare();
$subquery = $c->toSql();

$c = $modx->newQuery('CtcmClassifieds')
//$c->where('EXISTS('.$subquery.')', xPDOQuery::SQL_AND);
$c->innerJoin($subquery,'valid',array('`CtcmClassifieds`.id = valid.classified_id'));
$collection = $modx->getCollection('CtcmClassifieds',$c);

Problem is that this doesn't work:

$c->innerJoin($subquery,'valid',array('`CtcmClassifieds`.id = valid.classified_id'));

Any DB wizards know if there is another way of accomplishing this??

88 Posts

lazylegs Reply #4, 14 years, 4 months ago

At the moment there is no possible way to have subquery as table (with methods) like in this scenario where the inner join's table is the subquery.

What you can do is to use new xPDOCriteria which accepts raw sql string as query and no need to use xPDOQuery. xPDOQuery is an derivate from xPDOCriteria so in short terms you would just skip one level of abstraction using this method.

The syntax for the query would be :

$query = "SELECT * FROM `modx_ctcm_classifieds`
INNER JOIN (
  SELECT classified_id
  FROM `modx_ctcm_classifieds_categories`
  WHERE category_id IN (2,24)
  GROUP BY classified_id
  HAVING count(*) > 1
) valid ON `modx_ctcm_classifieds`.id = valid.classified_id";

$c = new xPDOCriteria($modx, $query);

$results = $modx->getCollection('CtcmClassifieds', $c); // or $modx->getIterator('CtcmClassifieds', $c);

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

14 Posts

Thomas Reply #5, 14 years, 4 months ago

Perfect, that works! Thanks!!