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

[Wesley Hampton]

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

[Exist~Dissolve]

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.

--
--
You received this message because you are subscribed to the Google Groups "ColdBox Platform" group.
For News, visit http://blog.coldbox.org
For Documentation, visit http://wiki.coldbox.org
For Bug Reports, visit https://ortussolutions.atlassian.net/browse/COLDBOX
---
You received this message because you are subscribed to the Google Groups "ColdBox Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to coldbox+u...@googlegroups.com.
To post to this group, send email to col...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--

ContentBox Evangelist

http://www.gocontentbox.org/

Blog: http://existdissolve.com

The only thing we learn from history is that we learn nothing from history.

[Wesley Hampton]

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 :)

Again, thanks very much!

Wes

[Exist~Dissolve]

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

[Joel Watson]

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" )