MODX :: xPDOQuery: 'between' complex condition

13

youroff Reply #1, 4 months, 1 week ago

Link to this post#1

Hi there!

I'm trying to build query to count items that fit complex conditions, like this:

SELECT COUNT(*) FROM `modx_schedule_items`
WHERE '08:00' BETWEEN `start_time` AND `end_time` OR
       '08:30'   BETWEEN `start_time` AND `end_time` OR
      ('08:00' <= `start_time` AND '08:30' >= `end_time`)

I tried to count items using simple queries, like following (and it works fine):

$q = $this->modx->newQuery($this->classKey);
$q->where(array('trainer_id' => $this->getProperty('trainer_id')));
$xcount = $this->modx->getCount('ScheduleItem', $q);

But when I try to put that condition in where clause as a string it doesn't work at all showing me all rows counted:

$q = $this->modx->newQuery($this->classKey);
$q->where(':start_time BETWEEN `start_time` AND `end_time` OR
       :end_time   BETWEEN `start_time` AND `end_time` OR
      (:start_time <= `start_time` AND :end_time >= `end_time`)', array(
     ':start_time' => $some_starttime,
     ':end_time' => $some_endtime
));
$xcount = $this->modx->getCount('ScheduleItem', $q);

How do I set condition as a string and bind values?

13

youroff Reply #2, 4 months, 1 week ago

Link to this post#2

I just made it work using xPDOCriteria and stmt->prepare/execute. So getCount() seems to be useless with complex criteria even though it can recieve it as an argument. Is it right?

9

never give up!

Anton Reply #3, 1 month ago

Link to this post#3

I get the same result (failed) trying to getCount with complex criteria. I was needed to do something like:

....
array('OR:isactive:='=>0,'AND:(total - passed):>='=>$length));
....

and xPDO looks support only single column as a key. In my situation xPDO thought that

(total - passed)

is a column

9,770

- MODX Chief Architect

Jason Coward | MODX Chief Architect

jasoncoward.com | @drumshaman

opengeek Reply #4, 1 month ago

Link to this post#4

This should work fine:

array(
    "OR:isactive:=" => 0,
    "(total - passed) >= {$length}"
);

You cannot use expressions in the "conjunction:column

perator => binding_value" format using PDO parameter bindings. When you need to use complex expressions like this, fall back to a string literal SQL expression.