ColdMVC Query Building

[Tony Nelson]

All,

I just checked in a feature to ColdMVC for building complex queries. I
think it's sweet.

It works similar to the query builders in other frameworks, such as
Doctrine, Rails, and Grails/Hibernate.

In case you're curious, I chose to not directly use Hibernate's
Criteria API due to its heavy reliance on static variables. Instead, I
chose to model the ColdMVC query builder more off of Doctrine, an ORM
for PHP that's based on Hibernate. To me it seems like a more natural
fit to ColdMVC than pure Hibernate.

Here's a brief example of how it works.

var q = _User.createQuery();
q.where(
q.like("firstName", "Tony")
);
var users = q.list();

The example above will find all users with a first name like "Tony".

OK so maybe it's not the best example, since it's essentially
equivalent to _User.findAllByFirstNameLike("Tony").

How about this (contrived) example:

var q = _Team.createQuery();
q.where(
q.and(
q.or(
q.and(
q.like("firstName", "Tony"),
q.or(
q.startsWith("lastName", "N"),
q.isNull("lastName")
)
),
q.or(
q.eq("gender", "m")
),
q.or(
q.endsWith("email", "@gmail.com")
)
),
q.neq("lastName", "Montana")
)
)
.orWhere(
q.like("website", "www.coldmvc.com")
)
.sort("lastName")
.order("asc");


Until now, you wouldn't be able to build anything close to that
without resorting to raw HQL. Speaking of which, here's that query's
generated HQL:

select user from User user where ( ( ( user.firstName like :firstName
and ( user.lastName like :lastName or user.lastName is null ) ) or
( user.gender = :gender ) or ( user.email like :email ) ) and
user.lastName != :lastName_2 ) or user.website like :website

So why not just use HQL? Well you can if you want to, but the query
builder provides some helpful shortcuts that make it worthwhile:

- Makes sure you're using the correct case for properties since HQL is
case-sensitive.
- Automatically javaCasts your properties.
- Automatically creates bound parameters for the underlying HQL.
- Handles operators for you.

While most of the core functionality is in place, I haven't
implemented everything yet, so if something is missing or if you have
an idea, please let me know. I also haven't written tests to verify
that all of the ColdMVC operators are working properly, but I believe
they should be.

Try it out if you have some time and let me know what you think. I
love feedback.

Thanks,
Tony


PS - here are some links to how it's done in other frameworks, in case
you feel like exploring:

- http://www.doctrine-project.org/docs/orm/2.0/en/reference/query-builder.html
- https://github.com/rails/arel
- http://www.grails.org/Builder
- http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html

[Ryan Ricard]

Good stuff Tony! This will provide a lot more flexibility.

> -http://www.doctrine-project.org/docs/orm/2.0/en/reference/query-build...
> -https://github.com/rails/arel
> -http://www.grails.org/Builder
> -http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycrite...