Using OR In Propel Queries Becomes Much Easier With Propel 1.6
Combining two generated filters with a logical OR used to be impossible in Propel - the alternative was to use orWhere() or combine(), but that meant losing all the smart defaults of generated filters.
Propel 1.6 introduces a new method for Query objects: _or(). It just specifies that the next condition will be combined with a logical OR rather than an AND.
// Basic usage: _or() as a drop-in replacement for orWhere()
$books = BookQuery::create()
->where('Book.Title = ?', 'War And Peace')
->_or()
->where('Book.Title LIKE ?', 'War%')
->find();
// SELECT * FROM book
// WHERE book.TITLE = 'War And Peace' OR book.TITLE LIKE 'War%'
// _or() also works on generated filters:
$books = BookQuery::create()
->filterByTitle('War And Peace')
->_or()
->filterByTitle('War%')
->find();
// SELECT * FROM book
// WHERE book.TITLE = 'War And Peace' OR book.TITLE LIKE 'War%'
// _or() also works on embedded queries
$books = BookQuery::create()
->filterByTitle('War and Peace')
->_or()
->useAuthorQuery()
->filterByName('Leo Tolstoi')
->endUse()
->find();
// SELECT book.* from book
// INNER JOIN author ON book.AUTHOR_ID = author.ID
// WHERE book.TITLE = 'War and Peace' // OR author.NAME = 'Leo Tolstoi'
This new method is implemented in the Criteria class, so it also works for the old-style queries. And since ModelCriteria::orWhere() is a synonym for ->_or()->where(), it is now deprecated.
11 comments
Since Propel 1.6 is not stable, I would like to raise my voice for another name of the method.
Especially beginners learn an API with the help of code completion and nobody expects a method to start with an underscore.
So a beginner would type a character and would see what it's got. The result is he hardly sees this method in code completion suggestions. But even if he sees it, he could think this method is kind of "private" as long as the underscore is often a naming convention for methods you should not call directly.
Also an advanced user must always remember that only this particular method starts with an underscore what could be annoying over time.
I'd like to make constructive criticism and here are some suggesstions: useOr, withOr, addOr, orWith
I'm also open for other suggestions of course and I hope I could convince you of the disadvantages starting a method with an underscore.
I will need this method soon while working on an admin generator for symfony and dealing with filters.
May you post a more complex example of code using _or() method nested with "AND" and "OR" conditions ?
I was also wondering if it would not be easy and powerful to use _or() (whatever the name) and _and() methods as it is done with useOtherModelQuery()->...->endUse()
Following IcyT proposal, we would write something like
$query
->where('field1=value1')
->useAnd() // opening OR (
->where('field2=value2')
->where('field3=value3')
->endAnd()
->where('field4=value4')
...
making "simply" a "field1=value1 AND (field2=value2 OR field3=value3) AND field4=value4"
Personnaly, I like chaining (thanks JQuery !) and find it very readable and efficient.
I guess that Propel already knows to do that, but sure you will tell us how to do it well :)
Loïc
First of all, I failed displaying right indentation in my last post.
I am already using combine() but it has a strong flavor with Criteria/Criterion way to make nested conditions that were hard and not natural to use in earlier versions of Propel.
And that is the point : I do believe an additional level maybe built on combine() could enhance usability.
For people knowing SQL, its by far more natural to deal with "at-the-moment" nested conditions than defining all conditions you need and then manage them one more time, and maybe one other time to get the final request : the more your request is complex (as far as conditions nesting is concerned), the less your query is readable and maintanable.
Many of you could disagree, and I will be glad to be wrong and learn why ;)
On the other hand, the use of combine is clearly indicated for automated tasks, such as code generation from config files as it is done in symfony admin generator.
Loïc
You're of course right that beginners should read the manual. But in my opinion it is also important to not put obstacles in their way and at this spot it's even very easy to avoid it.
After all I was sad reading your answer.
Loic is not reinventing combine, he s just doing what you re doing with _or() that cannot be done with condition/combine : keeping the power of the query method. Unless it s possible to use a method with condition/combine i m all for ->useConditionXXX ->endUseCondition()
...well when i reread that i see it s much more complicated than that but i ll just let it here in the unlikly case it could be a good idea =)
