How to replicate a LEFT JOIN in CFWheels

[Brian Lang]

I have an old query that I want to convert to CFWheels. The database has not been modified to follow CFWheels conventions. The tables are MessageCenter (a record is a message) and MessageCats (categories). If it matters, the database is MySQL.

Here's the query:

    SELECT MessageCenter.*, MessageCats.mCatDesc
    FROM MessageCenter
    LEFT JOIN MessageCats ON MessageCenter.mCat = MessageCats.mCatCode
    WHERE mAssignTo = <cfqueryparam cfsqltype="cf_sql_varchar" value="#GetAuthUser()#">
    ORDER BY mStamp DESC

On the view for message creation, the category for the message (MessageCenter) is selected from a drop-select populated from the categories (MessageCats) table.
Basically the query is just getting the category description from the lookup table.

How should I write my code to recreate this query using CFWheels Models and Controllers? Up to now, I've simply reverted to writing my own SQL queries and avoided using CFWheels for this problem and I want to figure out the correct way to approach this.

Thanks.

[Per Djurner]

Use the joinType argument on belongsTo / hasMany to control whether the query uses LEFT or INNER JOIN.

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfwheels+u...@googlegroups.com.
To post to this group, send email to cfwh...@googlegroups.com.
Visit this group at http://groups.google.com/group/cfwheels.
For more options, visit https://groups.google.com/d/optout.

[Brian Lang]

Part of the reason I have avoided setting up joins via CFWheels is that I haven't bothered to figure out the hasMany / belongsTo configuration.
Which do I use in which model?

[Per Djurner]

It's all explained here:

http://cfwheels.org/docs/1-3/chapter/associations

Specifically this part is what you need to know I think:

"If your database table contains a field that is a foreign key to another table, then this is where to use the belongsTo() function."

[Brian Lang]

I've tried the following:
1. Messagecategory.cfc (model)
component extends = "Model" output = "false" {

    public void function init()
    {
        table("messagecats");
        hasMany(name="messages", modelname="message", foreignkey="mcat", joinkey="mcatcode");
    }

}

2. Message.cfc (model)
component extends = "Model" output = "false" {

    public void function init()
    {
        table("messagecenter");
        belongsTo(name="messagecategory", modelname="messagecategory", foreignkey="mcatcode", joinkey="mcat", jointype="outer");

    }

}

3. Controller action to use the above models:
    public void function test()
    {
        messages = model("Message").findAll(where="mAssignTo=#session.user.username#", order="mStamp DESC", include="messagecategory");
    }

I am getting an error:

Expression Exception - in /Users/blang/Documents/Websites/testsite/wheels/model/sql.cfm : line 649
Element mcatcode is undefined in a CFML structure referenced as part of an expression.

Where did I go wrong?

[Per Djurner]

The best way to find the issue is to simplify your code:

- Delete the "where" and "order" arguments.

- Delete the hasMany call (since your call originates from the Message model it's not relevant here).

- Delete the "joinKey" arguments (should only be needed when you have not set a primary key in the database table which I assume you have).

- Change foreignkey="mcatcode" to foreignkey="mcat" in the belongsTo (if I am reading your original SQL the foreign key is mcat and not mcatcode).

Try that and see if it works.

If it does. re-add the code that you removed... hopefully it still works :)

PS: I believe you can also remove the modelName argument since it will be deduced from the "name" argument.

[Brian Lang]

So here's what I ended up with:

1. Messagecategory.cfc (model)
component extends = "Model" output = "false" {

    public void function init()
    {
        table("messagecats");

        setPrimaryKey("mcatcode");

    }

}

2. Message.cfc (model)
component extends = "Model" output = "false" {

    public void function init()
    {
        table("messagecenter");       

        belongsTo(name="messagecategory", modelname="messagecategory", foreignkey="mcat", jointype="outer");

    }

}

3. Controller action to use the above models:
    public void function test()
    {

        messages = model("Message").findAll(where="mAssignTo='#session.user.username#'", order="mStamp DESC", include="messagecategory");

    }

Which is working as expected.

Thanks for your troubleshooting tips.

[Per Djurner]

No problem, glad you got it working :)