modResource query - sorting by TV

I have a modResource with multiple TV’s (start_date, exp_date, is_finished).
My old SQL query (before migration) was:

ORDER BY ('.time().' < start_date) DESC, ('.time().' > start_date AND '.time().' < (exp_date + 60*60*24)) DESC, is_finished, start_date DESC

and it worked as expected. Now I’ve got no clue how to reproduce this in MODX.
I’ve been trying like this, but I can’t get sorting to work using joined tables. Any suggestions?

$query = $modx->newQuery('modResource');
	'template' => 5
$query->leftJoin('modTemplateVarResource', 'TV_date', 'tmplvarid=14 AND = TV_date.contentid');
$query->sortby('(' . time() . ' < TV_date)', 'ASC');

The TV values in MODX are always stored as strings.
A date/time value is also stored as a string in a specific format.
time() gives you a timestamp. You can’t compare it directly with the TV value. Either convert it to the same string format or use a CAST for the TV value.

1 Like

Understandable, but this query is throwing:
Unknown column 'TV_date' in 'order clause'.

I guess I can’t use joined values inside a ORDER BY statement. Is there anything I could use in MODX, other then having to make multiple DB queries?

You can do it with one query. But you probably have to use select() to add a column to sort by. Something like this might work:

$query->select($modx->getSelectColumns('modResource', 'modResource')); // All columns from modResource
$query->select("CAST(TV_date.value AS DATETIME) AS TV_date"); // The TV value casted as a datetime
$query->sortby("TV_date", 'ASC');
1 Like

This topic was automatically closed 2 days after discussion ended and a solution was marked. New replies are no longer allowed. You can open a new topic by clicking the link icon below the original post or solution and selecting “+ New Topic”.