How to do (A and B and C) or (D and E or F) or (G and H and I) with XPDO?

59 Posts

rich Reply #1, 9 years, 9 months ago

Hi all, I'm struggling a bit with using the OR condition with XPDO to return a list of persons:

In SQL I would simple write:

select * from persons where (personLastName='Doe' and personBirthYear=1980) 
                             or (fatherLastName='Doe' and fatherBirthYear=1980) 
                             or (motherLastName='Doe' and motherBirthYear=1980)

But I can't for the life of me figure out how to do this with XPDO.

I have tried the following (model somewhat simplified):

$personQuery = array('personLastName' => $lastName, 'personBirthYear' => $birthyear);
$fatherQuery = array('fatherLastName' => $lastName, 'fatherBirthYear' => $birthyear);
$motherQuery = array('motherLastName' => $lastName, 'motherBirthYear' => $birthyear);

$query->where($personQuery);
$query->orCondition($fatherQuery);
$query->orCondition($motherQuery);

// also tried this
$query->where(array(
   $personQuery,
   $fatherQuery,
   $motherQuery
), xPDOQuery::SQL_OR);

The problem seems to be that the query returns to many post, so it seems like there is a wrong conjunction somewhere.

I have spent so much time on this no so it's quite tempting to just write reqular SQL, but if someone here on these forums can help me it would be great.

Help me MODX Forum users, you're my only hope.

(I' using MODX 2.5.4.)

This question has been answered by Jako. See the first response.

discuss.answer

☆ A M B ☆
1,031 Posts

Jako Reply #2, 9 years, 9 months ago

Have you created $query? This should work somehow:

$query = $modx->newQuery('whateverClass')
$c->where(array(
    array('personLastName' => $lastName, 'personBirthYear' => $birthyear),
    array('OR:fatherLastName' => $lastName, 'fatherBirthYear' => $birthyear),
    array('OR:motherLastName' => $lastName, 'motherBirthYear' => $birthyear)
));

But adding xPDOQuery::SQL_OR should work similar.

You could check the built query with:

$c->prepare();
die($c->toSql());

59 Posts

rich Reply #3, 9 years, 9 months ago

Thanks for the reply Jako. Yes I have created the query, everything works as long as I just search the main-person; but when also querying against mother/father the results are wrong.

BUT when I check the query the way you suggested, I can see that the query is wrong. So I will try to tweak some more.
Will also try the XPDO you suggest.

Will let you know how it goes.

Thanks again Jako.

59 Posts

rich Reply #4, 9 years, 9 months ago

Your solution seemed to work perfectly. Many thanks Jako!

Answered How to do (A and B and C) or (D and E or F) or (G and H and I) with XPDO?