I am having trouble with a specific query, using Datamapper ORM for Codeigniter. My data model is set up so that I have blog posts, each of which have many categories, users, and tags. I want to fetch blog posts that are related to a specific category, user, or tag, but is also of status "published" or "scheduled."
So in other words, what I want to accomplish is:
category = General AND (status = published OR status = scheduled)
In Datamapper ORM I've tried the following syntax (assuming I want posts that are in the category with the name 'General'):
$posts->where_related('category', 'name', 'General')
->where('status', 'published')
->or_where('status', 'scheduled')
->get();
However, this results in getting posts that are: (status = published AND category = General) OR status = scheduled. Changing around the order of the statements doesn't help either. I can't seem to figure out if there's a Datamapper syntax for nesting the OR conditional in parentheses.
I realize I could write the query in raw SQL, such as:
SELECT * FROM posts WHERE (status = "published" OR status = "scheduled) AND ...
...followed by some more complicated JOIN statements, but the whole reason I'm using Datamapper is to make working with relationships easier, so I don't think I should have to write this whole thing out in raw SQL just to nest an OR conditional! I also realize I could use subqueries, but again, I don't think it should be that complicated.
Is there something I'm missing? Some way to organize this query so that I can accomplish it using Datamapper's syntax? It seems like being able to specify the order of operations of the AND/OR statements should be built in somehow...
If you need parentheses for your where clauses, you need to add them.
See http://datamapper.wanwizard.eu/pages/get.html#Query.Grouping for some examples.