_or() and _useTableQuery() chaining generates wrong query #794

Open
iBeb opened this Issue Nov 20, 2013 · 0 comments

Comments

Projects
None yet
1 participant

iBeb commented Nov 20, 2013

I'm facing a weird behavior when I'm combining _or() and _useTableQuery().

BookingQuery::create()
->filterByDomainId($domainIds)
->filterByKey($query.'%')
->_or()->useContactQuery()->filterByKey($query.'%')->endUse()
->filterByArrivalDate(array('min'=>new DateTime()));

generates this code:

SELECT COUNT(*) FROM booking LEFT JOIN contact ON (booking.CONTACT_ID=contact.CONTACT_ID) WHERE
booking.DOMAIN_ID IN (1,3,8,5,9,13)
AND (
(booking.KEY LIKE 'beu%' OR contact.KEY LIKE 'beu%')
OR booking.ARRIVAL_DATE>='2013-11-20'
)

when it should be:

SELECT COUNT(*) FROM booking LEFT JOIN contact ON (booking.CONTACT_ID=contact.CONTACT_ID) WHERE
booking.DOMAIN_ID IN (1,3,8,5,9,13)
AND (booking.KEY LIKE 'beu%' OR contact.KEY LIKE 'beu%')
AND booking.ARRIVAL_DATE>='2013-11-20'

I've tried this:
BookingQuery::create()
->filterByDomainId($domainIds)
->filterByKey($query.'%')
->useContactQuery()->endUse() // DOES nothing, except LEFT JOIN
->_or()->filterByKey($query.'---%');
->filterByArrivalDate(array('min'=>new DateTime()));

And the result is:
SELECT COUNT(*) FROM booking LEFT JOIN contact ON (booking.CONTACT_ID=contact.CONTACT_ID) WHERE
booking.DOMAIN_ID IN (1,3,8,5,9,13)
AND (booking.KEY LIKE 'beu%' OR booking.KEY LIKE 'beu---%')
AND booking.ARRIVAL_DATE>='2013-11-20'

Which is what is excepted.

Now, if I change the order and put the _useTableQuery() and _or() at the end:
BookingQuery::create()
->filterByDomainId($domainIds)
->filterByKey($query.'%')
->filterByArrivalDate(array('min'=>new DateTime()))
->_or()->useContactQuery()->filterByKey($query.'%')->endUse();

The code is:
SELECT COUNT(*) FROM booking LEFT JOIN contact ON (booking.CONTACT_ID=contact.CONTACT_ID) WHERE
booking.DOMAIN_ID IN (1,3,8,5,9,13)
AND booking.ARRIVAL_DATE>='2013-11-20'
AND (booking.KEY LIKE 'beu%' OR contact.KEY LIKE 'beu%')

And this is correct...

The workaround is to explicitly add _and():

BookingQuery::create()
->filterByDomainId($domainIds)
->_and()->filterByKey($query.'%')
->_or()->useContactQuery()->filterByKey($query.'%')->endUse()
->_and()->filterByArrivalDate(array('min'=>new DateTime()));

It's not a big issue since _add() solves the problem, but it should be either corrected or explained in the documentation.

_bertrand

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment