$query->andCondition error or do I not understand it?

☆ A M B ☆
181 Posts

jgulledge19 Reply #1, 15 years, 6 months ago

Here is the code I am using:

<?php

$query->where(array('slideshow_album_id' => $album_id ) );
switch ($sort_type) {
    case 'tbd':
        $query->where(array(
            'slide_status' => 'tbd' )
            );
        $query->sortby('edit_time','DESC');
        break;
    case 'archive':
        $query->where(array(
            'slide_status' => 'archive' )
            );
        $query->sortby('end_date','DESC');
        break;
    case 'future':
        $today = date("Y-m-d");
        /* @link http://rtfm.modx.com/display/xPDO20/xPDOQuery */
        $query->andCondition(array(
            array('AND:slide_status:IN' => array('future', 'replace' ) ),
            array(
                'OR:slide_status:=' => 'live',
                'AND:start_date:>' => $today
                ),
            array(
                'OR:slide_status:=' => 'live',
                'AND:end_date:<' => $today
                )
            ));
        $query->sortby('start_date','ASC');
        break;
    case 'current':
    default:
        $today = date("Y-m-d");
        $query->where(array(
            'slide_status' => 'live',
            'start_date:<=' => $today,
            'end_date:>=' => $today)
            );
        $query->sortby('sequence','ASC');
        break;
}
?>

For case future, the WHERE returned is:

WHERE ( `jgSlideshowSlide`.`slideshow_album_id` = 1 OR ( `jgSlideshowSlide`.`slide_status` IN ('future','replace') OR ( `jgSlideshowSlide`.`slide_status` = 'live' AND `jgSlideshowSlide`.`start_date` > '2011-03-25' ) OR ( `jgSlideshowSlide`.`slide_status` = 'live' AND `jgSlideshowSlide`.`end_date` < '2011-03-25' ) ) ) ORDER BY start_date ASC

The problem is I want that first OR to be an AND how can I do this? What am I missing?

Thanks for any help.

MODX ebook | My Site

MODX Staff
730 Posts

netProphET Reply #2, 15 years, 6 months ago

Josh, try explicitly setting the join condition in your first statement:

$query->where(array('slideshow_album_id' => $album_id ), xPDOQuery::SQL_AND );

Funny though, I thought it should default to AND.

Mike Schell
Lead Developer, MODX Cloud
Email: [email protected]
GitHub: https://github.com/netProphET/
Twitter: @mkschell

☆ A M B ☆
181 Posts

jgulledge19 Reply #3, 15 years, 6 months ago

I tried that and did not seem to change. It works as I expected for the current case:

WHERE ( `jgSlideshowSlide`.`slideshow_album_id` = 1 AND 
   ( `jgSlideshowSlide`.`slide_status` = 'live' AND 
      `jgSlideshowSlide`.`start_date` <= '2011-03-25' AND 
      `jgSlideshowSlide`.`end_date` >= '2011-03-25' 
    )
)
ORDER BY sequence ASC

But thanks anyway

MODX ebook | My Site

MODX Staff
10,725 Posts

opengeek Reply #4, 15 years, 6 months ago

Instead of using where() (you don’t really need to use andCondition/orCondition at all—explanation follows...) try building a nested array that reflects the structure of clauses you are building. Then pass that array to where() all at once. This should get the results you are looking for, though I’m wondering exactly why the OR’s are occurring myself in this situation...(might be a bug with using andCondition?) undecided

Anyway, using your example, try something like this:

<?php
$conditions = array('slideshow_album_id' => $album_id );
switch ($sort_type) {
    case 'tbd':
        $conditions['slide_status'] = 'tbd';
        $query->where($conditions)
        $query->sortby('edit_time','DESC');
        break;
    case 'archive':
        $conditions['slide_status'] = 'archive';
        $query->where($conditions);
        $query->sortby('end_date','DESC');
        break;
    case 'future':
        $today = date("Y-m-d");
        /* @link http://rtfm.modx.com/display/xPDO20/xPDOQuery */
        $conditions[] = array(
            array('AND:slide_status:IN' => array('future', 'replace' )),
            array(
                'OR:slide_status:=' => 'live',
                'AND:start_date:>' => $today
            ),
            array(
                'OR:slide_status:=' => 'live',
                'AND:end_date:<' => $today
            )
        ));
        $query->where($conditions);
        $query->sortby('start_date','ASC');
        break;
    case 'current':
    default:
        $today = date("Y-m-d");
        $conditions[] = array(
            'slide_status' => 'live',
            'start_date:<=' => $today,
            'end_date:>=' => $today
        ));
        $query->where($conditions);
        $query->sortby('sequence','ASC');
        break;
}
?>

Does that get the expression you want? It should be a matter of just nesting the conditions properly. The first condition at a specific level will always determine how is joined (i.e. which conjunction is used—AND/OR).

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

☆ A M B ☆
181 Posts

jgulledge19 Reply #5, 15 years, 6 months ago

Thanks Jason, I copied and pasted your code and fixed a parse error with it and the same result. I also tried removing the IN and put that like this:

<?php
            // array('AND:slide_status:IN' => array('future', 'replace' )),
            array( 'AND:slide_status:=' => 'future', 
                'OR:slide_status:=' => 'replace' ),
?>

It did not effect the query either, still getting the OR.

MODX ebook | My Site