modResource query - sorting by TV

[excavator]

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');
$query->where([
	'template' => 5
]);
$query->leftJoin('modTemplateVarResource', 'TV_date', 'tmplvarid=14 AND modResource.id = TV_date.contentid');
$query->sortby('(' . time() . ' < TV_date)', 'ASC');
...

[halftrainedharry]

@excavator

time()

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.

[excavator]

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?

[halftrainedharry]

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');