Monday, November 5, 2012

CFWheels FindAll using Aliases Note

So, I typically use SQL Servers MMC (Management console) to create complex queries and visualize relationships between tables, since it has such a fantastic GUI for doing this.

 I have a query that brings in a second(and third) table via wheels include="secondModel(thirdModel)", and counts up how many rows are in the third table... long story short.. there is a SUPER EXCELLENT feature of wheels where it allows you to use the DATABASE column names and table names.. effectively giving you control again for this case... you just have to:

 http://cfwheels.org/docs/1-1/function/findall 

SELECT: 
Determines how the SELECT clause for the query used to return data will look. You can pass in a list of the properties (which map to columns) that you want returned from your table(s). If you don't set this argument at all, Wheels will select all properties from your table(s). If you specify a table name (e.g. users.email) or alias a column (e.g. fn AS firstName) in the list, then the entire list will be passed through unchanged and used in the SELECT clause of the query. By default, all column names in tables JOINed via the include argument will be prepended with the singular version of the included table name.

So, if you have a complicated select statement, you don't have to try and figure out what wheels has mapped your column names to now that there are multiple tables involved, you can just use their actual names. I've being using wheels for quite a while now, and never knew that it had this behaviour. No wonder I got frustrated with wheels when doing complex joins and aliases... it was switching to un-wheelsy mode.. and not telling me!!

Caveat: I really like cfwheels' built in ORM. I enjoy not needing using sql servers' table and column names, along with the table prefixes in my queries. But, in some cases...you know them.. the cases where you threaten wheels that you're going to break out a <CFQUERY> tag and just paste in the answer... its nice to just be able to go half-way.