We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 40949
    • 20 Posts
    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.
      • 4172
      • 5,888 Posts
        -------------------------------

        you can buy me a beer, if you like MIGX

        http://webcmsolutions.de/migx.html

        Thanks!
        • 40949
        • 20 Posts
        You.

        Are.

        Awesome.


        Seriously, thank you very much. I got the function in my class, and it works laugh