Implementing findWhere() and findAllWhere()

[Tony Nelson]

I haven't fully implemented the findWhere() and findAllWhere() methods
for models because I haven't yet decided how it should be done in all
circumstances. In Grails, it looks like this:

def book = Book.findWhere(title:"The Shining", author:"Stephen King")

This is easy enough to convert to ColdFusion, but I see 2 possible
ways of achieving the same thing.

Option 1: Pass in multiple arguments, with each argument being a
property on the model.
params.book = _Book.findWhere(title="The Shining", author="Stephen
King");

Option 2: Pass in a single struct argument, with each key in the
struct being a property on the model.
params.book = _Book.findWhere({title="The Shining", author="Stephen
King"});

I think the first option more closely resembles Grails, which is nice.
Plus it's a little cleaner. However, I think I prefer option 2 more
when you start adding paging parameters (offset, max, sort, order) to
findAllWhere(). For example:

params.books = _Book.findAllWhere({author="Stephen King"}, {max="10",
sort="datePublished", order="desc"});

Having the constraint of only accepting 1 or 2 arguments would greatly
simplify the code to generate the HQL, plus you no long have to worry
about naming conflicts between paging paramaters and model properties.

Also, I'd like some opinions on how to handle various operators (like,
startsWith, endsWith, etc...). In Grails, you can use closures to
generate the HQL like such:

def books = Book.createCriteria().list(max: 5, offset: 10) {
like("title","foo%")
}

Since closures aren't available in ColdFusion (yet), we need to figure
out our own syntax. I think the most natural way of handling operators
would be to make them available to the findWhere() and findAllWhere()
methods. Here are a couple ways I see this working:

Option 1: Use an array where the first item is the operator and the
second item is the value.
params.books = _Book.findAllWhere({
title = [ "like", "foo" ]
}, {
max="5",
offset="10"
});

Option 2: Use a struct with operator and value keys.
params.books = _Book.findAllWhere({
title = { operator="like", value="foo" }
}, {
max="5",
offset="10"
});

Option 3: Use a struct where the key is the operator and the value
is... the value.
params.books = _Book.findAllWhere({
title = { like="foo" }
}, {
max="5",
offset="10"
});

I don't think any of the options would be that hard to implement, so
it's really a matter of preference. I think right now I'm leaning
towards option 1, although I wouldn't be against the other options
either.

Any thoughts or comments are appreciated.

Also, here's a Grails reference if you're interested:
http://www.grails.org/DomainClass+Dynamic+Methods

[Tony Nelson]

Since nobody responded, I went ahead and implemented the methods as
best I could. Here's how it looks:

var comments = _Comment.findAllWhere({
email = "j...@example.com",
author = [ "startsWith", "Joe" },
website = { operator="like", value=".blogspot" },
"post.title" = { like="Hello, World" }
}, { sort="date", order = "desc" });

The above code will produce HQL similar to the follow, with parameter
bindings replaced by their actual values:

select comment
from Comment comment
where comment.email = 'j...@example.com'
and comment.author like 'Joe%'
and comment.website like '%.blogspot%'
and comment.post.title like '%Hello, World%'
order by comment.date desc

In case you couldn't tell, I decided to pass in a single struct of
parameters, where each parameter could be a simple value, a struct, or
an array. Hopefully this gives the developer the most flexibility in
developing their applications.

-Tony