CODEWITHSUNDEEP
ormcoldfusioncfccfwheels
Brian D
Brian D

Reputation: 973

Selecting specific joined record from findAll() with a hasMany() include

(I tried posting this to the CFWheels Google Group (twice), but for some reason my message never appears. Is that list moderated?)

Here's my problem: I'm working on a social networking app in CF on Wheels, not too dissimilar from the one we're all familiar with in Chris Peters's awesome tutorials. In mine, though, I'm required to display the most recent status message in the user directory. I've got a User model with hasMany("statuses") and a Status model with belongsTo("user"). So here's the code I started with:

users = model("user").findAll(include="userprofile, statuses");

This of course returns one record for every status message in the statuses table. Massive overkill. So next I try:

users = model("user").findAll(include="userprofile, statuses", group="users.id");

Getting closer, but now we're getting the first status record for each user (the lowest status.id), when I want to select for the most recent status. I think in straight SQL I would use a subquery to reorder the statuses first, but that's not available to me in the Wheels ORM. So is there another clean way to achieve this, or will I have to drag a huge query result or object the statuses into my CFML and then filter them out while I loop?

Upvotes: 0

Views: 167

Answers (2)

Michael Giovanni Pumo
Michael Giovanni Pumo

Reputation: 14784

I don't know your exact DB schema, but shouldn't your findAll() look more like something such as this:

statuses = model("status").findAll(include="userprofile(user)", where="userid = users.id");

That should get all statuses from a specific user...or is it that you need it for all users? I'm finding your question a little tricky to work out. What is it you're exactly trying to get returned?

Upvotes: 0

Chris Peters
Chris Peters

Reputation: 18090

You can grab the most recent status using a calculated property:

// models/User.cfc
function init() {
    property(
        name="mostRecentStatusMessage",
        sql="SELECT message FROM statuses WHERE userid = users.id ORDER BY createdat DESC LIMIT 1,1"
    );
}

Of course, the syntax of the SELECT statement will depend on your RDBMS, but that should get you started.

The downside is that you'll need to create a calculated property for each column that you need available in your query.

The other option is to create a method in your model and write custom SQL in <cfquery> tags. That way is perfectly valid as well.

Upvotes: 1

Related Questions