findAll() method does not recognize aliased column names in the "where" attribute for tables joined in an association but has the same table column name

[cfcoder]

I have encountered what appears to be a limitation (at a minimum) in using an association to join two related tables together and both tables have a column with the same name. Wheels aliases the name of the associated column so it can be displayed. But when you try to use that aliased column name in a WHERE clause in a findAll() method call, it is not recognized by Wheels and blows up.

Let's say you have two tables, lookup_types (LT) and lookup_values (LV), both with a column "is_active" to indicate when either table row is an active row. The LT is the parent of multiple LV rows. LV.lookup_types_id is the FK to LT table's id column.
Let's say there are associations to join these two tables. Set up both the hasMany() on the parent model (LT) and belongsTo() on the children (LV) model.

If I try to query:
qGetTypeValues = model("lookup_type").findAll(include="lookup_value", where="is_active=1 AND lookup_valueis_active=1");

This throws an error complaining about the lookup_valueis_active is not found in the table. Well of course it is not in the table. But Wheels seems to forget about the alias it created for itself in displaying the query output when no WHERE clause is used. It also has no problem with the parent's "is_active" column name.

Is this a limitation or a bug?

[adamcameron]

The aliases are for the SELECT clause right? One cannot - to the best of my knowledge - use them in a WHERE clause.

I'd've thought you'd've needed to do something like where="lookup_type.is_active=1 AND lookup_value.is_active=1" to disambiguate between the two tables? (untested... I'd give even money on CFWheels not coping with that, and breaking).

That said by the time yer writing SQL into strings to pass to CFWheels methods, you might consider cutting out the middle-man and just make a queryExecute call with the exact SQL you want in it. Not sure CFWheels's monkeying is necessarily adding much here?

[cfcoder]

My table name is actually lookup_types so when I prefixed is_active column with that, it works. I was thrown off by the aliased display of column names when you view the contents of the two joined tables that have some columns sharing the same name.

Thanks for the tip!

[dbelanger]

Hey Bruce, I've the same situation every now and then. Most often, in my case, because we follow the CFWheels conventions, I've had to prefix the "id" column with the table name because the "id" column is present on every single table in the db. So if you need to refer specifically to one of the column with the same name in the where clause, Wheels needs to know which one.

If you need to refer to a specific column you need to prefix it with the actual table name in the db. So, as Adam has just said, where="lookup_type.is_active=1 AND lookup_value.is_active=1" is correct. Assuming that lookup_type is the actual name of your table and not the name of the model of course.

[cfcoder]

I gave Adam the credit as his response came first. But yes, prefixing with the table name (lookup_types, following CFWheels convention for model name is singular and table name is plural) it works! Thanks for weighing in.