We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Answered Rest API - Mutiple TV's for a collection of resources

    • 53573
    • 10 Posts
    otemua Reply #1, 7 years, 1 month ago
    Hi,

    Was hoping one of the wise members of the modx community could help me, I have two TV's for a template (tv-eample1 and tv-example2). I Was wondering if anyone knows how to get these two different TV's from each resource, I am able to get the TV's using innerJoin but as not all resources will use these TV's, I need to be able to achieve this using left join, although left join does return the TV's the values are wrong, it would use exactly the same value for tv-eample1 and tv-example2 when there both would be different.

    here is my code, maybe am going about this wrong, this is for my rest service so won't be using any modx extra as it does use custom tables, but I have removed them to simplify the issue:

    Version:MODX Revolution 2.5.7-pl
    		
		$myIds = $this->modx->getChildIds(2,2);
		$query = $this->modx->newQuery('modResource'); 
		$query->select(array(
		  'modResource.id','modResource.pagetitle','tvv.contentid as tvvcontentid','tvv.value as example1','tvv2.value as example2','tv.name as tvname','tv2.name as tvname2','tv.id as tvid','tv2.id as tvid2'
		));
		$query->leftJoin('modTemplateVarResource', 'tvv', array(
		    'tvv.contentid = modResource.id'
		));
		$query->leftJoin('modTemplateVarResource', 'tvv2', array(
		    'tvv2.contentid = modResource.id'
		));
		$query->leftJoin('modTemplateVar', 'tv', array('tv.id = tvv.tmplvarid','tv.name' => 'tv-eample1'));
		$query->leftJoin('modTemplateVar', 'tv2', array('tv2.id = tvv2.tmplvarid','tv2.name' => 'tv-example2'));	
		$query->where(array('id:IN' => $myIds));
		$query->groupby('id');
		$items = $this->modx->getCollection('modResource', $query);


    Any help will be appreciated.

    Thanks

    This question has been answered by multiple community members. See the first response.

    • discuss.answer
      • 3749
      • 24,544 Posts
      BobRay Reply #2, 7 years, 1 month ago
      I'm not completely clear on what you're trying to do and why, but here are some thoughts:

      Maybe I'm misunderstanding your code (or some code is missing), but tvv and tvv2 are not MODX objects so I would think it should be 'modTemplateVarResource.contentid' and 'modTemplateVarResource.value', unless those are the objects from your custom table(s).

      Also, you should be aware that when a TV is set to its default value, there is no record in the modTemplateVarResource table, so a query using that table will only work if no TV is set to its default value.

      If you're not too concerned about speed or there aren't too many resources, you could loop through the resources and use $resource->getTVValue(), or (better) if you can use the raw values of the TVs, get the two TV objects with getObject() and use $tv->getValue($resourceId) and $tv2->getValue($resourceId).

      Another way to go would be to use getCollectionGraph() rather then using joins. This will connect the TV values to the resources.

      Yet another way to go would be to create your own custom table with the same structure as the modTemplateVarResource table (modx_site_tmplvar_contentvalues) and use a plugin to save the resource ID, TVid, and TV value there whenever a resource containing those TVs is saved. Then you could use a join to that table, or (if the table object is xPDO-friendly), use getCollectionGraph().

      I think what I would probably do, is extend modResource to add the two extra fields and not use TVs at all. The two fields would show up on the Create/Edit Resource panel.
        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
      • discuss.answer
        • 53573
        • 10 Posts
        otemua Reply #3, 7 years, 1 month ago
        Hi Bob,

        Thanks for all the great information and fast response, I have actually seen many of your posts in the forum and there have helped me out many times smiley so thanks a lot for that. I would of responded earlier but virgin media was down for like a week.

        Anyway back to the matter at hand, I probably maybe didn't explain it well, as quite new to Modx so still got so much to learn, anyway after reading your post I did some more analysing of the generated SQL from xPDO and managed to get it fixed, I just needed to add:

        		$query->leftJoin('modTemplateVarResource', 'tv', array(
		    'modResource.id = tv.contentid', 'tv.tmplvarid' => '1'
		));
		$query->leftJoin('modTemplateVarResource', 'tv2', array(
		    'modResource.id = mtvr2.contentid', 'tv2.tmplvarid' => '2'
		));


        The query gets confused on table `modx_site_tmplvar_contentvalues` due to many matches on 2nd join but can't work out which to use as the criteria is based on the 3rd join, by adding the tmplvarid this forces the table to select the correct row.

        To avoid using hard coded values, I know it can be achieved using a subquery:

        LEFT JOIN `modx_site_tmplvar_contentvalues` `tvv` ON modResource.id = tvv.contentid and tvv.tmplvarid=(select id from `modx_site_tmplvars` where NAME = 'tv-eample1')
LEFT JOIN `modx_site_tmplvar_contentvalues` `tvv2` ON modResource.id = tvv2.contentid and tvv2.tmplvarid=(select id from `modx_site_tmplvars` where NAME = 'tv-eample2')


        However not looked at subqueries with xPDO yet, so will need to look into that, as that be better than hard coding the 'tmplvarid'

        Thanks for the help Bob you put me on the right path, not sure if this makes any more sense or if I have made this thread even more confusing haha, but the matter is now resolved wink

          • 3749
          • 24,544 Posts
          BobRay Reply #4, 7 years, 1 month ago
          I'm glad you got it sorted. smiley

          I'm not that much of an expert on queries with joins for TVs, because I almost never use TVs to store data that will be used for searching and sorting. It tends to be slow and unreliable. The main purpose of TVs is to store resource-specific information that will be displayed with the resource.

          If you look at the TVFilters code in the getResources extra, you can see what tortuous hoops you have to jump through to use them for searching and sorting, and why it's so slow. You have to make shoehorn in the default values of all the TVs because a TV set to its default value has no record in the modTemplateVarResource table. You also have to "process" each TV whether it needs it or not.
            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