Can I sortbyTV from different levels with getResources?

I need to sort and order a list of webinars (documents) and webinar series (containers) based on “eventStart” TV found either on level 1 or level 2.

Level 1: Single Webinar 1 (eventStart TV)
Level 1: Alpha Webinar Series (Container, no TVs)
_______Level 2: First Webinar of Alpha Series (eventStart TV)
_______Level 2: Second Webinar of Alpha Series (eventStart TV)
Level 1: Single Webinar 2 (eventStart TV)
Level 1: Omega Webinar Series (Container, no TVs)
_______Level 2: First Webinar of Omega Series (eventStart TV)
_______Level 2: Second Webinar of Omega Series (eventStart TV)

Output: Should only show items from Level 1 but use child TV’s when necessary to sort and order.

maybe it would be better, you would have allways a webinar container with webinar - children no matter, if there is one or many events.
Then you could do a join with pdoResources or migxLoopCollection to the child - resources for sorting and for showing the first event date or something.

The only way I can think of to achieve this with getResources and without changing the structure, is to use &sortby=`FIELD(...)` and then use a custom snippet to first determine the sorting order before calling getResources.

The getResources call would look like this:

[[getResources?
    &parents=`11`
	&depth=`0`
    &limit=`0`
    &sortby=`FIELD(modResource.id, [[getSortOrder]] )`
    &sortdir=`DESC`
	...
]]

and the custom snippet getSortOrder something like this:

<?php
$stmt = $modx->prepare("
SELECT id FROM
(
	SELECT `modResource`.id,
		(SELECT value FROM `modx_site_tmplvar_contentvalues` tv WHERE `modResource`.id = tv.contentid AND tv.tmplvarid = :tvid) AS tv_self,
		(SELECT MAX(tv.value)
			FROM `modx_site_content` AS r
			INNER JOIN `modx_site_tmplvar_contentvalues` tv ON (tv.contentid = r.id AND tv.tmplvarid = :tvid)
			WHERE r.parent = `modResource`.id
			GROUP BY r.parent
		) AS tv_children
	FROM `modx_site_content` AS `modResource` 
	WHERE  (`modResource`.parent IN (:parentid))
) AS T
WHERE IFNULL(tv_self,tv_children) IS NOT NULL
ORDER BY IFNULL(tv_self,tv_children)
");
$stmt->bindValue(':parentid', 11); //value of &parents in the getResources call
$stmt->bindValue(':tvid', 22); //ID of the TV 'eventStart'
$stmt->execute();
$ids = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
return implode(",",$ids);
  • Change :parentid and :tvid in the code to the proper values.
  • Maybe MAX(tv.value) has to be changed to MIN(tv.value) to work for your purposes.
  • The query doesn’t work correctly if your TV has a default value.

Thank you Bruno but the site already has an existing structure as detailed above.

Thanks Harry, interesting approach but it didn’t work. Here’s the incorrect output using &sortdir=ASC

Item 1: Sept 15 (Container)
Item 2: April 30
Item 3: April 22 (Container)

Here’s what my getResources looks like:

[[getResources?
    &parents= `[[*parent]]`
    &resources=`-[[*id]]`
    &depth= `0`
    &tpl= `webinarFlexCard`
    &limit= `3`
    &includeTVs= `1`
    &tvPrefix=``
    &sortby=`FIELD(modResource.id, [[webinarSortOrder]] )`
    &sortdir=`ASC`]]

As I don’t have your data, it is not obvious to me what is incorrect with this output.

  • Which date should be first? The earliest or the latest?
  • When a container has multiple children, which date should be used for the container? The earliest or the latest?

To debug, it’s probably best to call [[webinarSortOrder]] without the surrounding getResources-call (or alternatively copy the query into phpMyAdmin) and make sure the snippets returns the right values before using it. The return value should be a comma-separated list of the resource-ids in the right order.

If the order is not correct, then you have to tweak the SQL query.
Maybe you have to cast the values so that it works properly with datetimes:

...
(SELECT CAST(tv.value AS DATETIME) FROM `modx_site_tmplvar_contentvalues` tv WHERE `modResource`.id = tv.contentid AND tv.tmplvarid = :tvid) AS tv_self,
(SELECT MAX(CAST(tv.value AS DATETIME)) ...

Thank you. Regarding your questions.

  • The earliest results should show first.
  • Container with multiple children should use the child with earliest date (eventStart) as reference.

I think you have to change MAX(...) to MIN(...) in the SQL query then:

SELECT id FROM
(
	SELECT `modResource`.id,
		(SELECT CAST(tv.value AS DATETIME) FROM `modx_site_tmplvar_contentvalues` tv WHERE `modResource`.id = tv.contentid AND tv.tmplvarid = :tvid) AS tv_self,
		(SELECT MIN(CAST(tv.value AS DATETIME))
			FROM `modx_site_content` AS r
			INNER JOIN `modx_site_tmplvar_contentvalues` tv ON (tv.contentid = r.id AND tv.tmplvarid = :tvid)
			WHERE r.parent = `modResource`.id
			GROUP BY r.parent
		) AS tv_children
	FROM `modx_site_content` AS `modResource` 
	WHERE  (`modResource`.parent IN (:parentid))
) AS T
WHERE IFNULL(tv_self,tv_children) IS NOT NULL
ORDER BY IFNULL(tv_self,tv_children)
1 Like

Thank you Harry, your solution worked very nicely. I changed MAX(…) to MIN(…) as you suggested.