Grab all Resources with TV = X

87 Posts

webandflow Reply #1, 16 years, 5 months ago

So I’m trying to wrap my head around Revo and xpdo. I love it - looks great but my brain is having a hard time making the transition.

I’m trying to grab all the resources on my site that have a template var checkbox checked. I’m guessing I have to start with $modx->newQuery() and figure that I should probably join a Template Variable table or something but I’m drawing a blank as to where to go from there.

And I’ve been trying to read through the API stuff but haven’t connected all the dots.

So, how should I grab a bunch of IDs in Revo of resources that have a TV (checkbox) set to yes????

Thanks!

Des
webandflowdesign.com

5,888 Posts

Bruno17 Reply #2, 16 years, 5 months ago

you can see an example in getResources-snippet under /* build query */ .

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

87 Posts

webandflow Reply #3, 16 years, 5 months ago

Thanks Bruno - I’m sure this will be helpful.

However, I’m actually trying to right a snippet and so would like to know to do this from PHP - not from a snippet.

My apologies - I left that part out.

Des
webandflowdesign.com

24,544 Posts

BobRay Reply #4, 16 years, 5 months ago

Quote from: webandflow at May 25, 2010, 12:44 AM

Thanks Bruno - I’m sure this will be helpful.

However, I’m actually trying to right a snippet and so would like to know to do this from PHP - not from a snippet.

My apologies - I left that part out.

Bruno17 is saying to look at the getResources *code* to see how to query the DB in Revolution (from PHP).

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

5,888 Posts

Bruno17 Reply #5, 16 years, 5 months ago

here is the part (little bit modified), which you can use:

<?php

$debug=1;
$sortby='pagetitle ASC';
$tvFilters[]='TV==X';
//$parents=array('11');

$contextResourceTbl = $modx->getTableName('modContextResource');
$context = empty($context) ? $modx->quote($modx->context->get('key')) : $modx->quote($context);
$queryarray['deleted']='0';
$queryarray['published']='1';
//$queryarray[]="`modResource`.`parent` IN (" . implode(',', $parents) . ")";
$queryarray[]="(`modResource`.`context_key` = {$context} OR EXISTS(SELECT 1 FROM {$contextResourceTbl} `ctx` WHERE `ctx`.`resource` = `modResource`.`id` AND `ctx`.`context_key` = {$context}))";

$criteria = $modx->newQuery('modResource', $queryarray);
if (empty($showHidden)) {
    $criteria->andCondition(array('hidemenu' => '0'));
}
if (!empty($hideContainers)) {
    $criteria->andCondition(array('isfolder' => '0'));
}
if (!empty($tvFilters)) {
    $tmplVarTbl = $modx->getTableName('modTemplateVar');
    $tmplVarResourceTbl = $modx->getTableName('modTemplateVarResource');
    $conditions = array();
    foreach ($tvFilters as $fGroup => $tvFilter) {
        $filterGroup = count($tvFilters) > 1 ? $fGroup + 1 : 0;
        $filters = explode(',', $tvFilter);
        foreach ($filters as $filter) {
            $f = explode('==', $filter);
            if (count($f) == 2) {
                $tvName = $modx->quote($f[0]);
                $tvValue = $modx->quote($f[1]);
                $conditions[$filterGroup][] = "EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} `tvr` JOIN {$tmplVarTbl} `tv` ON `tvr`.`value` LIKE {$tvValue} AND `tv`.`name` = {$tvName} AND `tv`.`id` = `tvr`.`tmplvarid` WHERE `tvr`.`contentid` = `modResource`.`id`)";
            } elseif (count($f) == 1) {
                $tvValue = $modx->quote($f[0]);
                $conditions[$filterGroup][] = "EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} `tvr` JOIN {$tmplVarTbl} `tv` ON `tvr`.`value` LIKE {$tvValue} AND `tv`.`id` = `tvr`.`tmplvarid` WHERE `tvr`.`contentid` = `modResource`.`id`)";
            }
        }
    }
    if (!empty($conditions)) {
        foreach ($conditions as $cGroup => $c) {
            if ($cGroup > 0) {
                $criteria->orCondition($c, null, $cGroup);
            } else {
                $criteria->andCondition($c);
            }
        }
    }
}

$total = $modx->getCount('modResource', $criteria);
$modx->setPlaceholder($totalVar, $total);

$criteria->sortby($sortby, $sortdir);

if (!empty($limit)) $criteria->limit($limit, $offset);
$columns = $includeContent ? '*' : $modx->getSelectColumns('modResource', 'modResource', '', array('content'), true);
$criteria->select($columns);
if (!empty($debug)) {
    $criteria->prepare();
    echo $criteria->toSQL();
}
$collection = $modx->getCollection('modResource', $criteria);

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

4,149 Posts

splittingred Reply #6, 16 years, 5 months ago

Quote from: webandflow at May 24, 2010, 11:15 PM

So, how should I grab a bunch of IDs in Revo of resources that have a TV (checkbox) set to yes????

Since it’s a checkbox, that’s a bit trickier:

For a TV with ID 123:

$value = 1;
$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->where(array(
     'TemplateVarResources.tmplvarid' => 123,
     '"'.$value.'" IN (TemplateVarResources.value)',
     'modResource.deleted' => false,
));
$resources = $modx->getCollection('modResource',$c);

I had to do the IN statement because Checkbox TVs store their value as a comma-separated list. Otherwise you could just do ’TemplateVarResources.value’ => $value. You could also use LIKE as shown above.

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

87 Posts

webandflow Reply #7, 16 years, 5 months ago

All . . . Thank you.

@splittingred - fantastic! that did it! Appreciate your help.

Des
webandflowdesign.com

☆ A M B ☆
339 Posts

gallenkamp Reply #8, 12 years, 7 months ago

Sorry to dig that old topic out. That snippet above does only work for choices like "yes" or "no". It cant handle checkboxes for "blue||green||red", right?

5,888 Posts

Bruno17 Reply #9, 12 years, 7 months ago

you need a LIKE - query to get resources, which contain the query-string

$value = 'red';
$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->where(array(
     'TemplateVarResources.tmplvarid' => 123,
     'TemplateVarResources.value:LIKE' => '%' . $value . '%',
     'modResource.deleted' => 0,
     'modResource.published' => 1,
));
$resources = $modx->getCollection('modResource',$c);

but this would also find values like 'orange-red'
better would be, you make sure to have the douplepipes also at front and at end of all tags, like:

||blue||green||red||orange-red||

its possible to add them by a plugin for the system-event OnDocFormSave

than you can do the query like that:

$value = 'red';
$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->where(array(
     'TemplateVarResources.tmplvarid' => 123,
     'TemplateVarResources.value:LIKE' => '%||' . $value . '||%',
     'modResource.deleted' => 0,
     'modResource.published' => 1,
));
$resources = $modx->getCollection('modResource',$c);

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

☆ A M B ☆
339 Posts

gallenkamp Reply #10, 12 years, 7 months ago

I looked inti the database, they are not doublepiped at the end. But it will work even without as those values in the checkboxes are not that close to each other.

My goal was to get all possible values (read the TV) and count their appearance in their ressources checkboxes (iterate array elements over all entered TVs). In the end I need a catalogue based on those checkboxes.