XPDO templatevar question

- 394 Posts
b03tz Reply #1, 16 years, 2 months ago
Imagine, i have 2 template variables: color and price.

Now imagine i want to build a query...that does the following:

"Fetch all resource ID’s that have color red and price below 10".

This is a problem since template variables do not have actual column
names. I’ve been breaking my head over this...this is what i have:
```
$query = $modx->newQuery('modTemplateVarResource');
$query->where(array(
    array('tmplvarid' => 1,
          'value' => 'Red', 
          array('AND:tmplvarid:=' => 2, 
                'AND:value:<' => 10)
    )    
));
```
But as you can see, this is a huge problem because no row will have
tmplvarid 1 AND 2...Will i have to execute multiple queries for this?
Or am i missing something...

Thanks in advance.

[edit]
I have something working here...but still the question remains: "Is this the way to do it in XPDO?"
```
$query = $modx->newQuery('modTemplateVarResource');
$query->innerJoin('modTemplateVarResource', 'tplVar', 'modTemplateVarResource.contentid = tplVar.contentid');
$query->where(array(
    'modTemplateVarResource.tmplvarid' => 5,
    'modTemplateVarResource.value' => 'rood',
    'tplVar.tmplvarid' => 7,
    'tplVar.value' => 'aardbei'
));
```
It takes quite some time (1,8 seconds) for 3,5 million rows.
[/edit]
Follow me on twitter: @b03tz
Follow SCHERP Ontwikkeling on twitter: @scherpontwikkel
CodeMaster
- 4,149 Posts
splittingred Reply #2, 16 years, 2 months ago
Quote from: b03tz at Jul 06, 2010, 07:30 AM

Imagine, i have 2 template variables: color and price. Now imagine i want to build a query...that does the following: "Fetch all resource ID’s that have color red and price below 10". This is a problem since template variables do not have actual column
names. I’ve been breaking my head over this...this is what i have:

Try this:
```
$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','Color','`Color`.`contentid` = `modResource`.`id` AND `Color`.`tmplvarid` = 1');
$c->innerJoin('modTemplateVarResource','Price','`Price`.`contentid` = `modResource`.`id` AND `Price`.`tmplvarid` = 2');
$c->where(array(
    'Color.value' => 'Red',
    'Price.value:<' => 10,
));
$resources = $modx->getCollection('modResource',$c);
```
shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com