SQL query to retrive Template Variables

chris.dempsey78 · April 24, 2020, 4:27pm

Summary

I need to retrieve the value of two template variables along with select Resource fields (id, pagetitle) from the site_content table.

Details

To retrieve the values I have an SQL query however the result includes extra rows. This happens because the Resources involved have values set other template variables and I don’t know how to exclude these in the query.

SELECT DISTINCT does not work because the values returned for tv_videoID and tv_videoValue do not match.

I tried the native method (I don’t know the correct terminology) $c = $modx->newQuery(‘modResource’) but struggled to build the query, most likely as I’m doing it incorrectly.

The Template Variables are
#10 video-src
#11 video-poster

The SQL query is
SELECT content.id,
content.pagetitle,
tvvalues.value as tv_videoID,
(SELECT value FROM rap2_site_tmplvar_contentvalues
WHERE rap2_site_tmplvar_contentvalues.contentid = tvvalues.value AND rap2_site_tmplvar_contentvalues.tmplvarid = 10) as tv_videoValue,

       (SELECT value FROM rap2_site_tmplvar_contentvalues 
		 		WHERE rap2_site_tmplvar_contentvalues.contentid = tvvalues.value AND rap2_site_tmplvar_contentvalues.tmplvarid = 11) as tv_videoPoster
FROM   rap2_site_content as content
       LEFT JOIN rap2_site_tmplvar_contentvalues as tvvalues
               ON content.id = tvvalues.contentid 
WHERE  content.parent = 65
       AND class_key != 'CollectionContainer'
       AND content.published = 1
       AND content.hidemenu = 0;

The result is

In this example I want just the first row. In the second row the tv_videoID value is from the same Resource as row 1 but for Template Variable #9 which isn’t needed.

Can anyone help me rewrite the SQL query or suggest an alternative approach?

Thanks

bruno17 · April 24, 2020, 8:10pm

you could have a look into pdoResources, how it is done there.
maybe, you could just use the pdoFetch - class do get what you need.

chris.dempsey78 · April 24, 2020, 8:39pm

Thanks @bruno17, believe it or not I had the same thought at 0300 this morning but didn’t write it down and promptly forgot.

Any luck I can figure it out with &showLog and looking at the query generated by pdoResources.

Unfortunately I’m not smart enough to work directly with the pdoFetch class.

UPDATE: I spoke too soon, it’s pretty straight forward given the example in the docs.

bobray · April 25, 2020, 7:26pm

Another way to go would be to just get the modTemplateVarResource objects (rap2_site_tmplvar_contentvalues) that match your criteria for both contentid and tmplvarid, and iterate through them (i.e., no join).

I would use xPDO for this, if the performance is acceptable, but your method would likely be faster.

chris.dempsey78 · April 25, 2020, 7:49pm

Thanks @bobray, I think I’m going to have to query the rap2_site_tmplvar_contentvalues table for each resource in the loop. I still can’t figure how to do it with a single query.

Reading the original post again I didn’t make clear the TV values I need to retrieve belong to a child resource container (#159) not the parent resource (#65). They are tied together as the TVs contain the ID of the related resource with parent #65.

I realise I’m not explaining this particularly well.

bobray · April 25, 2020, 10:06pm

It sounds complicated. Can you describe the selection process in pseudo-code?

chris.dempsey78 · April 27, 2020, 4:19pm

@bobray - thanks for making me look at the pdoFetch class, it will be useful in future and much less complex than I expected.

Also, despite not being able to describe this properly I found a solution - posted below in case it helps anyone in future.

long winded version

I have two parent resources (Industries 65 and Videos 159) which act as containers. These are used to display a list of Industries from #65, some of which will have an associated Video from tv.primary-video and tv.video-poster, optionally some will also have tv.video-related.

The resource setup in MODX is

Industries (65)

Maritime
Pharmaceutical
etc.

Each child of Industries has 3 TVs

video-primary (8) - File to select video file from Media Browser (child of #159)
video-poster (11) - File to select video poster image from Media Browser
video-related (9) - Multi select to select related videos (children of #159)

Videos (159)

Client 1
Client 2
etc.

I wanted a single sql query to retrieve the 18x Industries from #65 and at the same time retreive related Video data into the same table.

My problem is that tbl.site_tmplvar_contentvalues has multiple entries for each child of Videos (159).

I tried using sub queries to grab the 3x TVs into their own column eg.

SELECT content.id, content.pagetitle,
	<snip>
	(SELECT value FROM modx_site_tmplvar_contentvalues 
		 		WHERE modx_site_tmplvar_contentvalues.contentid = tvvalues.value AND modx_site_tmplvar_contentvalues.tmplvarid = 8) as tv_videoValue

But this doesn’t work because the SELECT statement is executed once, not for each row returned from modx_site_content with parent = 65.

This left me with a few options but none were tidy eg. I could retreive Children of #65, loop through the set and perform more sql queries to find the tv values required.

Fortunately now have a neat way to do this from an idea in one of the search results thrown up during this process.

Using LEFT JOIN to grab all the TVs relating to each child of #65 and the CONCAT function to compile these into a single column and format them as JSON. Then the snippet to display the results just needs to decode the JSON and retreive values for video-primary, video-poster and video-related.

SELECT c.id, c.pagetitle, c.content,
    CONCAT("[",GROUP_CONCAT("{\"",tvs.name, "\":\"", 
        REPLACE(REPLACE(tvvalue.value, "\"", "\\\""), "\n", ""),"\"}"),"]") as tv_json
FROM rap2_site_content AS c
    LEFT JOIN rap2_site_tmplvar_contentvalues AS tvvalue ON tvvalue.contentid = c.id
    LEFT JOIN rap2_site_tmplvars AS tvs ON tvs.id = tvvalue.tmplvarid
WHERE c.parent = 65
       AND c.class_key != 'CollectionContainer'
       AND c.published = 1
       AND c.hidemenu = 0
GROUP BY c.id;

bobray · April 27, 2020, 5:22pm

I’m glad you got it sorted. I wonder if it would make sense to just put all the video files in one directory and name them using the alias of the resource they go with. That would simplify things and you wouldn’t need any TVs.

chris.dempsey78 · April 27, 2020, 6:55pm

That would work if it was just the video file I needed but longtitle, introtext and thumbnail.jpeg are also displayed for each video.

Plus some videos can be relevant to more than one resource, and not keen to store multiple copies of the same file as some are over 250mb.

Would have been much simpler if they were hosted on YouTube as already have the code for that, thumbnails are automatically generated and ask if need to store is the YouTube ID.

system · April 29, 2020, 6:55pm

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.