Howto combine xPDOQuery::SQL_OR & xPDOQuery::SQL_AND ?

370 Posts

Ralph Zelf Reply #1, 13 years, 1 month ago

Hi all i'm struggling wit this.

i need to get some resources where the template = 4
AND
a keyword is in either the pagetitle OR the page content

this does not seem to work (code is simplified)

$criteria = $modx->newQuery('modResource');

$criteria->where(array('published' => 1,
    'template' => 4,
    'deleted' => 0
),xPDOQuery::SQL_AND);


if(isset($_POST["keyword"]) && $_POST["keyword"] != ''){
    $criteria->where(array('modResource.content:LIKE' => '%' . $_POST["keyword"] . '%'),xPDOQuery::SQL_OR);
    $criteria->where(array('modResource.pagetitle:LIKE' => '%' . $_POST["keyword"] . '%'),xPDOQuery::SQL_OR);
}

$criteria->sortby('menuindex', 'ASC');
$docArray = $modx->getCollection('modResource', $criteria);

because it gives me a resource where the keyword is correct but the template != 4

how can i combine the 2 OR statements?
to get something like this

SELECT * FROM modResource
WHERE Template='4'
AND (pagetitle LIKE %'$keyword'%  OR content LIKE %'$keyword'%);

thanks for the help

Rdg

☆ A M B ☆
54 Posts

labr1005 Reply #2, 13 years, 1 month ago

Can you try

$criteria->where(array(
    array(
        'template' => 4,
        'published' => 1,
        'deleted' => 0
    ),
    array(
        'pagetitle:LIKE' => '%'.$_POST['keyword'].'%',
        'OR:content:LIKE' => '%'.$_POST['keyword'].'%'
    ),
));

? [ed. note: labr1005 last edited this post 13 years, 1 month ago.]

☆ A M B ☆
54 Posts

labr1005 Reply #3, 13 years, 1 month ago

There was a little error above. I fixed that and now it should work. The prepared query looks like this:

... WHERE ( ( `modResource`.`template` = 4 AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 ) AND ( `modResource`.`pagetitle` LIKE '%hello%' OR `modResource`.`content` LIKE '%hello%' ) )

370 Posts

Ralph Zelf Reply #4, 13 years, 1 month ago

Hi,
I tried your first attempt as well. it looks like its from Bob's book (page 363). I'm very curious why it doesn't work. I realy like the clean way of building up the where clause with arrays (is that typicaly xPDO, i don't know)
I don't understand your second example? how would i get that in my $criteria ?
$criteria->( WHERE ( ( `modResource`.`template` = 4 AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 ) AND ( `modResource`.`pagetitle` LIKE '%hello%' OR `modResource`.`content` LIKE '%hello%' ) ));
doesn't seem to work [ed. note: rdaneeel last edited this post 13 years, 1 month ago.]

☆ A M B ☆
54 Posts

labr1005 Reply #5, 13 years, 1 month ago

Sorry, I should have explained my second post. It's not an example, but part of the actual query as a string. Example:

$criteria = $modx->newQuery('modResource');
$criteria->where(array(
    array(
        'template' => 1,
        'published' => 1,
        'deleted' => 0
    ),
    array(
        'pagetitle:LIKE' => '%'.$_POST['keyword'].'%',
        'OR:content:LIKE' => '%'.$_POST['keyword'].'%'
    ),
));
// Debug Start
$criteria->prepare();
return $criteria->toSQL();
// Debug End

With ->prepare() and ->toSQL() your snippet will create the query but not execute it so you can debug and check if you're on the right track.

And yes, building the where clause with nested arrays is totally xPDO and an alternative to andCondition/orCondition. Take a look at the third example on this page
http://rtfm.modx.com/xpdo/2.x/class-reference/xpdoquery/xpdoquery.where
or the "Another Example" on this page
http://rtfm.modx.com/xpdo/2.x/class-reference/xpdoquery/xpdoquery.orcondition [ed. note: labr1005 last edited this post 13 years, 1 month ago.]

160 Posts

insomnix Reply #6, 13 years, 1 month ago

I put this together a while back to help a friend of mine, maybe it might help someone else:

http://www.seknetsolutions.com/tutorials/modx-xpdo-and-the-where-condition