Multiple TV searching via xPDO?

54 Posts

someone Reply #1, 15 years, 9 months ago

Heya,

I’m trying to get resources based on two template variables, and I’m having trouble joining and searching.... Any help is greatly appreciated!

$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->rightJoin('modTemplateVar','TemplateVar1','`TemplateVar1`.`id` = `TemplateVarResources`.`tmplvarid` AND `TemplateVar1`.`id` = 14');
$c->andCondition(array('TemplateVarResources.value:<' => '5'));
$c->leftJoin('modTemplateVar','TemplateVar2','`TemplateVar2`.`id` = `TemplateVarResources`.`tmplvarid` AND `TemplateVar2`.`id` = 41'); 
$c->andCondition(array('TemplateVarResources.value:LIKE' => '%'));

$c->where(array(
    'template' => $modx->resource->get('template'),
    'deleted' => false,
    'hidemenu' => false,
    'published' => true
));

$resources = $modx->getCollection('modResource',$c);

The first join is fine... The second join works as long as I use ’LIKE %’ if I try and do anything else, it returns nothing.... Does anyone see anything wrong with this, or can anyone provide a place to look for similar code? TIA,

*

MODx 2.0.6
PHP 5.3.3
MySQL 5.1.50

54 Posts

someone Reply #2, 15 years, 9 months ago

Also multiple andConditions on the first join also work fine...

$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->rightJoin('modTemplateVar','TemplateVar1','`TemplateVar1`.`id` = `TemplateVarResources`.`tmplvarid` AND `TemplateVar1`.`id` = 14');
$c->andCondition(array('TemplateVarResources.value:<' => '5'));
$c->andCondition(array('TemplateVarResources.value:>' => '3'));
$c->leftJoin('modTemplateVar','TemplateVar2','`TemplateVar2`.`id` = `TemplateVarResources`.`tmplvarid` AND `TemplateVar2`.`id` = 41'); 
$c->andCondition(array('TemplateVarResources.value:LIKE' => '%'));

$c->where(array(
    'template' => $modx->resource->get('template'),
    'deleted' => false,
    'hidemenu' => false,
    'published' => true
));

$resources = $modx->getCollection('modResource',$c);

Can anyone lend a hand?

MODx 2.0.6
PHP 5.3.3
MySQL 5.1.50

54 Posts

someone Reply #3, 15 years, 9 months ago

Anyone can help with this? I’m desperate! sad

MODx 2.0.6
PHP 5.3.3
MySQL 5.1.50

MODX Staff
10,725 Posts

opengeek Reply #4, 15 years, 9 months ago

The tvFilters property in getResources provides an example of multiple conditions like this. I will note however that searches on TV fields like this are not recommended because the fields are not indexed for this kind of searching. If you need to do efficient searching of data, you will be much better off in the long run creating a custom data model tailored to your needs.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

MODX Staff
10,725 Posts

opengeek Reply #5, 15 years, 9 months ago

Also, please do not double-post.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

54 Posts

someone Reply #6, 15 years, 9 months ago

OpenGeek,

Thanks for the help.. I’m a bit past the point of re-architecting this and would only do so if lousy performance makes me do it.. Given that, I’m going to keep beating my head against the wall with this. I may need to solve this problem another way and/or resort to using a more direct form of querying. I’d prefer not to do that as I’m trying to get a grip on (x)PDO but, sometimes we do what we have to do.. Also, sorry for the double post, I was worried that my post here was not getting seen.

Can you explain at all why it doesn’t work as I am trying to do it? Also, if I get any traction I will post back here in the interest of helping others.

Cheers,
S

MODx 2.0.6
PHP 5.3.3
MySQL 5.1.50

MODX Staff
10,725 Posts

opengeek Reply #7, 15 years, 8 months ago

Quote from: someone at Jan 02, 2011, 04:24 PM

Can you explain at all why it doesn’t work as I am trying to do it?

It’s going to be difficult without knowing the details of your TV data. The like => % is going to match nothing or everything, not sure which, but otherwise, I don’t see anything obvious.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

15 Posts

anto-fle Reply #8, 15 years, 6 months ago

Quote from: someone at Jan 02, 2011, 04:24 PM

... I’d prefer not to do that as I’m trying to get a grip on (x)PDO but, sometimes we do what we have to do.

Exactly!
I’m having the same problem trying to get a list of resources belonging to the same folder: each resources may have 2 tv and I need to list those who have tv1=’something’ AND tv2!=’something else’ but
$items_to_list_query->andCondition(array(
’id:IN’ => $children_of_this_folder,
’published’ => true,
’deleted’ => false,
’hidemenu’ => false,
’isfolder’ => false,
’TemplateVar.name’ => ’tv1’,
’TemplateVarResources.value’ => ’something’,
//
// ’TemplateVar.name’ => ’tv2’,
// ’TemplateVarResources.value:!=’ => ’something else’
));
If I uncomment the last condition the query doesn’t work. I guess there’s something wrong in this code but how should I do? I think that this may be a useful answer for other people too, since I can’t get any similar example in modx forum or documentation and working with multiple tv might be quite popular...
Thanks in advance.

MODX Staff
10,725 Posts

opengeek Reply #9, 15 years, 6 months ago

Because the conditions are stored as an associative array, you cannot use the exact same key twice, as in:

’TemplateVar.name’ => ’tv1’ and
’TemplateVar.name’ => ’tv2’

Only one will then work. You can avoid this by using nested array conditions, e.g.

$query->where(array(
    array(
        'id:IN' => $children_of_this_folder,
        'published' => true,
        'deleted' => false, 
        'hidemenu' => false,
        'isfolder' => false,
    ),
    array(
        array(
            'TemplateVar.name' => 'tv1',  
            'TemplateVarResources.value' => 'something',
        ),
        array(
            'TemplateVar.name' => 'tv2',
            'TemplateVarResources.value:!=' => 'something else'
        )
    )
));

However, I think your logic is flawed because you need separate joins to check each of those conditions in isolation. IOW, TemplateVar.name is never going to equal tv1 and tv2 at the same time for each match.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

15 Posts

anto-fle Reply #10, 15 years, 6 months ago

Ok, many thanks for your quick reply.

Before you write I was already trying something similar, like this

----------------------------
$items_to_list_query->innerJoin(’modTemplateVar’, ’TemplateVar’, ’`TemplateVar`.`id` = `TemplateVarResources`.`tmplvarid`’);
$items_to_list_query->innerJoin(’modTemplateVar’, ’TemplateVar2’, ’`TemplateVar2`.`id` = `TemplateVarResources`.`tmplvarid`’);
[...]
$items_to_list_query->andCondition(array(
’TemplateVar.name’ => ’tv1’,
’TemplateVarResources.value’ => ’something’
));
$items_to_list_query->andCondition(array(
’TemplateVar2.name’ => ’tv2’,
’TemplateVarResources.value:!=’ => ’something else’
));
----------------------------

...but obviously I didn’t understand how xPDO query syntax work.

I’ll try your code.