Hey guys,
I've got kind of a problem. I have a resource with 3 template variables, now I want to filter resources on the frontend depending these 3 tv values. I've come pretty far with this, finding the results for one tv, but when adding the second tv in the mix, no results get found.
Here's the facts:
The resource has 3 template variables, one for an award, one for a subject and one for a subject area.
TV values in this resource:
Award( id:21 ): 151
Subject( id:26 ): 100
Subject Area( id:28 ): 14
Now here is how I build my Query:
$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->where(array('template'=>6, 'modResource.deleted' => false));
if($awardGET != '')
{
$a = $modx->getObject('Awards', array('title'=>$awardGET));
$aID = $a->get('id');
$c->where(array(
'TemplateVarResources.tmplvarid' => 21,
'TemplateVarResources.value:LIKE' => strval($aID)
));
}
// not used in this particular case, but is a filter that I want to use too
if($subjectGET != '')
{
$sa = $modx->getObject('Subject', array('title'=>$subjectGET));
$saID = $sa->get('id');
$c->where(array(
'TemplateVarResources.tmplvarid' => 26,
'TemplateVarResources.value:LIKE' => strval($saID)
));
$c->orCondition(array(
'TemplateVarResources.value:LIKE' => strval($saID).',%'
));
$c->orCondition(array(
'TemplateVarResources.value:LIKE' => '%,'.strval($saID)
));
}
if($subjectareaGET != '')
{
$ar = $modx->getObject('SubjectArea', array('title'=>$subjectareaGET));
$arID = $ar->get('id'); // resulting in 14 in this case
$c->where(array(
'TemplateVarResources.tmplvarid' => 28,
'TemplateVarResources.value:LIKE' => $arID,
));
}
$c->sortby('pagetitle','ASC');
$c->prepare();
$instCourses = $modx->getCollection('modResource', $c);
This results in the following SQL Query:
SELECT `modResource`.`id` AS `modResource_id`, `modResource`.`type` AS `modResource_type`, `modResource`.`contentType` AS `modResource_contentType`, `modResource`.`pagetitle` AS `modResource_pagetitle`, `modResource`.`longtitle` AS `modResource_longtitle`, `modResource`.`description` AS `modResource_description`, `modResource`.`alias` AS `modResource_alias`, `modResource`.`link_attributes` AS `modResource_link_attributes`, `modResource`.`published` AS `modResource_published`, `modResource`.`pub_date` AS `modResource_pub_date`, `modResource`.`unpub_date` AS `modResource_unpub_date`, `modResource`.`parent` AS `modResource_parent`, `modResource`.`isfolder` AS `modResource_isfolder`, `modResource`.`introtext` AS `modResource_introtext`, `modResource`.`content` AS `modResource_content`, `modResource`.`richtext` AS `modResource_richtext`, `modResource`.`template` AS `modResource_template`, `modResource`.`menuindex` AS `modResource_menuindex`, `modResource`.`searchable` AS `modResource_searchable`, `modResource`.`cacheable` AS `modResource_cacheable`, `modResource`.`createdby` AS `modResource_createdby`, `modResource`.`createdon` AS `modResource_createdon`, `modResource`.`editedby` AS `modResource_editedby`, `modResource`.`editedon` AS `modResource_editedon`, `modResource`.`deleted` AS `modResource_deleted`, `modResource`.`deletedon` AS `modResource_deletedon`, `modResource`.`deletedby` AS `modResource_deletedby`, `modResource`.`publishedon` AS `modResource_publishedon`, `modResource`.`publishedby` AS `modResource_publishedby`, `modResource`.`menutitle` AS `modResource_menutitle`, `modResource`.`donthit` AS `modResource_donthit`, `modResource`.`privateweb` AS `modResource_privateweb`, `modResource`.`privatemgr` AS `modResource_privatemgr`, `modResource`.`content_dispo` AS `modResource_content_dispo`, `modResource`.`hidemenu` AS `modResource_hidemenu`, `modResource`.`class_key` AS `modResource_class_key`, `modResource`.`context_key` AS `modResource_context_key`, `modResource`.`content_type` AS `modResource_content_type`, `modResource`.`uri` AS `modResource_uri`, `modResource`.`uri_override` AS `modResource_uri_override`, `modResource`.`hide_children_in_tree` AS `modResource_hide_children_in_tree`, `modResource`.`show_in_tree` AS `modResource_show_in_tree`, `modResource`.`properties` AS `modResource_properties` FROM `modx_site_content` AS `modResource` JOIN `modx_site_tmplvar_contentvalues` `TemplateVarResources` ON `modResource`.`id` = `TemplateVarResources`.`contentid` WHERE ( ( `modResource`.`template` = 6 AND `modResource`.`deleted` = 0 ) AND ( `TemplateVarResources`.`tmplvarid` = '21' AND `TemplateVarResources`.`value` LIKE '151' ) AND ( `TemplateVarResources`.`tmplvarid` = '28' AND `TemplateVarResources`.`value` LIKE '14' ) ) ORDER BY pagetitle ASC
This setup yields no results, this while it should find at least my test resource.
I hope you guys can help me with this.