I am trying to get into queries with inner join, which makes me a bit confused. I managed to implement some code that works:
$sortbytv = "termin";
$datetoday = strftime('%Y-%m-%d');
$c = $modx->newQuery('modResource');
$c->select(array('modResource.id','modResource.pagetitle'));
$c->innerJoin('modTemplateVar', 'TemplateVar2', '`TemplateVar2`.`name` = "'.$sortbytv.'"');
$c->innerJoin('modTemplateVarResource','TemplateVarResources', '`modResource`.`id`=`TemplateVarResources`.`contentid` AND `TemplateVarResources`.`tmplvarid`=`TemplateVar2`.`id`');
$c -> where(array('id:IN' => $termineIds,
'TemplateVarResources.value:>=' => $datetoday,
));
$c->sortby('value','ASC');
$c->limit($limit);
It just sorts content ids by date tvs and selects all with dates higher than today. I would now like to add a second date tv, as an end date that is later than the first. And if set (not emty) and still coming it should be included by the where statement without changing the ordering.
This maby shows somehow, what I want to achive, of course it s not working. I tried several thing but did fail. Any help would be fine.
$sortbytv = "termin";
$datetoday = strftime('%Y-%m-%d');
$c = $modx->newQuery('modResource');
$c->select(array('modResource.id','modResource.pagetitle'));
$c->innerJoin('modTemplateVar', 'TemplateVar2', '`TemplateVar2`.`name` = "'.$sortbytv.'"');
$c->innerJoin('modTemplateVarResource','TemplateVarResources', '`modResource`.`id`=`TemplateVarResources`.`contentid` AND `TemplateVarResources`.`tmplvarid`=`TemplateVar2`.`id`');
$c->innerJoin('modTemplateVar', 'TemplateVar3', '`TemplateVar3`.`name` = "terminEnd"');
$c -> where(array('id:IN' => $termineIds,
'TemplateVarResources.value:>=' => $datetoday,
'AND:TemplateVar3.value:>=' => $datetoday,
));
$c->sortby('value','ASC');
$c->limit($limit);
