[coldbox-3.8.1] Criteria Builder & Projection Property of “name” causes alias in SQL WHERE clause

Communities ColdBox HMVC
1

Can anyone explain why a column alias is being created in the WHERE clause in this scenario?

In attempting to learn the Criteria Builder – and Coldbox/ColdFusion in general – I noticed that when I refer to a property named “name” from my User model from within the .withProjections() then the SQL WHERE clause that is sent to SQL Server contains an alias instead of the actual column name.

So if I have a User model like this:

`
component persistent=“true” table=“Users” {
// Primary Key
property name=“ID” fieldtype=“id” column=“ID” generator=“native” setter=“false”;

// Properties
property name=“code” column=“Code” ormtype=“string”;
property name=“name” column=“Name” ormtype=“string”;
…
}
`

Then I do something like this in my Users handler:

var users = c.$and(c.restrictions.like("name", userName & "%"),c.restrictions.eq("status","Active")).withProjections(property="name, code").list();

The SQL that is generated is:

select this_.Name as y0_, this_.Code as y1_ from schema.Users this_ where ( y0_ like ? and this_.Status=? )

And I get a SQL Server error message:

Invalid column name 'y0_'.

But, if I alias that name property and change my Criteria to:

var users = c.$and(c.restrictions.like("name", userName & "%"),c.restrictions.eq("status","Active")).withProjections(property="name:userName, code").list();

Then the SQL is generated appropriately and I get the results I expect:

select this_.Name as y0_, this_.Code as y1_, from schema.Users this_ where ( this_.Name like ? and this_.Status=? )

Can anyone explain why a column alias is being created in the WHERE clause in the above scenario? It would be nice to know what to watch out for ahead of time rather than me having to alias every property or just wait until errors happen.

Similar revelations found here.

Thanks!

Wes

2

I think it’s a combination of both Hibernate and CriteriaBuilder in ColdBox.

First of all, in CriteriaBuilder in ColdBox, it looks like projections ALWAYS get an alias. If you leave an alias off, it will use the property name as the alias (e.g. “name:name”).

See line 481 here: https://github.com/Ortus-Solutions/ContentBox/blob/master/coldbox/system/orm/hibernate/BaseBuilder.cfc. It does a listLast() on the property name…but if the property doesn’t have a “:alias”, listLast() will return the property name itself.

This is fine for projections. However, when you use that same property in a criteria, it throws an error. I believe this is a Hibernate/SQL issue, as I’ve seen others complain about not being able to use the same alias name as the property name–in other words, you’re saying “use an alias in the where clause”…which won’t work.

When I remove the addition of the alias from addProjection(), it seems to bypass the error (still need to do a lot of testing to verify that). If this turns out to be a workaround, perhaps we can upgrade that bit of code to check first before adding an alias.

3

Joel, that was an incredible explanation, thank you! I’m so new to CB/CF that I’m sure I truly only understood a small a portion of that.

So, it seems my take away for right now is that if I intend to use the same Property in a Criteria Restriction as well as a Projection, then I need to make sure that I alias the field as part of the Projection statement to work around this. I didn’t make the connection that it was happening because I was using the Property in both places, so that is a huge help to me to know what to watch out for. Also, I appreciate you pointing me to the source of the problem in the source code – it helps newbies like myself to learn :slight_smile:

Again, thanks very much!

Wes

4

I’m glad it was helpful! Take care, and let us know if you have any other questions!

5

I actually ran into this in my own code today. If you need the property projection alias to be the same as the property name itself, it seems that this works:

.withProjections( property=“this.MyProperty:MyProperty” )