xPDOQuery: 'between' complex condition

13 Posts

youroff Reply #1, 14 years, 9 months ago

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 Posts

youroff Reply #2, 14 years, 9 months ago

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?

12 Posts

Anton Reply #3, 14 years, 5 months ago

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

never give up!

MODX Staff
10,725 Posts

opengeek Reply #4, 14 years, 5 months ago

This should work fine:

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

You cannot use expressions in the "conjunction:column:operator => binding_value" format using PDO parameter bindings. When you need to use complex expressions like this, fall back to a string literal SQL expression. [ed. note: opengeek last edited this post 14 years, 5 months ago.]

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

12 Posts

Anton Reply #5, 14 years, 5 months ago

Great! Works! Thanks!

only one thing:

"OR:isactive" => 0,

here

"OR:isactive:=" => 0,

When conjunction is used operator should be explicitly specified, otherwise xPDO take OR as a operator and breaks down the query structure

never give up!

MODX Staff
10,725 Posts

opengeek Reply #6, 14 years, 5 months ago

Thanks, original post updated...

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek