New to ORM. I am following the ormexample sample app. I have 2 tables/models: Orders and Accounts both with ID PKs. Orders has an accountid field point back to Accounts table. I set it up as a many-to-one. I am searching for Orders and want to display Account table information. The query looks correct in the log but the ORM .list only returns the default Orders fields without the accounitd (viewing with cfdump). I also tried HQL and got the same thing.
Orders:
ORDER_ID (identity) (was just ID, I changed it to test)
accountid (many-to-one)
status
orderDate
…
Accounts:
ID (identity)
FName
LName
…
Handler for view:
var c = orderService.newCriteria();
prc.qPending = c.createAlias( “account”, “a” ).eq( “status”, JavaCast( “byte”,0) ).gt(“id”, JavaCast( “int”,0 )).list( sortOrder=“orderDate DESC”, asQuery=true );
Log:
Hibernate:
select
this_.ORDERS_ID as ORDERS1_1_1_,
this_.accountid as accountid1_1_,
this_.totalAmt as totalAmt1_1_,
this_.status as status1_1_,
this_.orderDate as orderDate1_1_,
a1_.ID as ID0_0_,
a1_.FName as FName0_0_,
a1_.LName as LName0_0_,
from
ORDERS this_
inner join
ACCOUNTS a1_
on this_.accountid=a1_.ID
where
this_.status=?
and this_.ORDERS_ID>?
order by
this_.orderDate desc
ORM works in terms of objects (AKA “entities”), which means that, if you run a criteria query on an entity, the objects returned by hibernate will be an array of those entities with their mapped relational accessors. The asQuery, method just passes that array into entityToQuery to transform them in to a CF query.
If you use asQuery=false, you would be able just pull the Account entity directly from with the result loop:
for( var order in prc.qPending ){
var account = order.getAccount();
//do stuff here
}
If you have the fetch on the Account property set to, “JOIN” or “SELECT” you will save yourself a trip to the database when you make that getAccount() call, because the entity relationship will already have been fetched.
While queries are familiar, they are not nearly as powerful as using the ORM objects directly – especially if you use a solid fetching and caching strategy. If you want to retrieve them in CF native query with join columns, it’s much more efficient to use standard CF query() methods. You can also return an array of arrays with specifc column values (asQuery=true) will not work on this:
c.setProjection(
c.projections.projectionList()
.add( c.projections.property(“propName1”, “aliasForPropName1”)
.add( c.projections.property(“propName2”, “aliasForPropName2”)
);
which will return an array of arrays with aliasForPropName1 at index 1 and aliasForPropName2 at index 2. Personally, I’d much rather work with ORM entities, unless I absolutely have to use a query.
HTH,
Jon
Oops. Made a mistake in my code example. Hibernate used to have an alias in the property function, but it’s not necessary or allowed now. The array of arrays example should be something like:
c.setProjection(
c.projections.projectionList()
.add( c.projections.property(“id”)
.add( c.projections.property(“a.firstName”)
.add( c.projections.property(“a.lastName”)
);
That would return an array like:
[
[
1234,
“Jon”,
“Clausen”
]
]