An ORM Sceptic
June 08, 2008
A while back I had a bad experience of using an ORM (it wasn't Reactor or Transfer) to handle my database interactions. It all started so well, I could build lists, details pages, update and delete data easily without having to write any SQL.
Then I needed to build more complex SQL statements for example:
SELECT ChildCategory.title, ParentCategory.title
FROM [Category] as ChildCategory
INNER JOIN [Category] as ParentCategory
ON ( ParentCategory.id = ChildCategory.parent_id )
or
SELECT s.supplier_name, wc.workerscount
FROM Suppliers s
LEFT JOIN (
SELECT w.worker_supplier_id, count(*) as workerscount
FROM workers w
GROUP BY w.worker_supplier_id
) wc
ON wc.worker_supplier_id = s.supplier_id
This is where it all started to go wrong. Having already developed a lot of code using the ORM I didn't have enough time in the project to take it out so I ended up with a complete mess of my own custom queries and ORM queries. Not good. Since then I have built a code generator that introspects the database and builds a CFC with the CRUD methods for each table. I can then add methods for the more complex queries or update the generated ones.
I'm happy with my solution. I've never liked the DAO approach as you end up with a lot of code; I also often need to delete multiple records in one go and I don't want to have to iterate through them. Using DAO and Gateways together just adds more code.
The trouble is, there a plenty of developers out there who are much clever then me who rave about Transfer. It does have TQL, which will hopefully solve my complex query problem (although it does seem odd to me to write half an SQL statement!). So should I invest my time in learning it and then find it doesn't do everything I need or refine my own code generation? For now I think I'll stick with my own approach for production sites and try out Transfer on something else.
- Posted in:
- ColdFusion
9 comments
Leave a comment
If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)
I've been working with Transfer for just over a month now. I use it to perform CRUD operations on a singular record and related records, but when it comes to writing complex queries or manipulating a large number of records, I still rely on <cfquery> and SQL.
I don't mind the fact that Transfer can't handle every database interaction for me the way I'd like: the time and effort it saves me in the situations where I do use it make it worth it.
Comment by Brian Swartzfager – June 09, 2008
Thanks for the comment, I'm interested in your thoughts.
As all my database CRUD methods are created for me (including relationships thanks to the dbinfo tag), it is actually less effort for me to use my own generated code, than use transfer where I need to set up the table relationships.
I really don't like the idea of having two objects in my code to interact with the database. This doesn't fit with "information hiding" (including encapsulation), where I only need to change code in one place if my database model changes.
Comment by John Whish – June 09, 2008
Just out of curiosity, does your code generator let you configure/fine-tune the table relationships exposed by the cfdbinfo tag? For example, Transfer gives you a couple of different options when it comes to exposing a 1-to-n relationship. You can decide whether or not an object should always be instantiated with an array or struct of parent objects or if those child objects should only be provided when explicitly called ("lazy load"). You can also decide to work the relationship from the other direction, where the child can retrieve, set, or remove the parent rather than the other way around.
I did some experimentation with using cfdbinfo to generate record-based objects before I investigated Transfer, and I ended up deciding that trying to emulate the options Transfer brought to the table would take a bit more time than I had. But again, if your code generator works for you, then there's probably no reason to switch.
Comment by Brian Swartzfager – June 09, 2008
Chats like this are great - thanks for your input!
I don't have caching built into any of the sites I've built with it yet, however the next one will and then make use of Query of Queries to reduce the interaction with the database.
When I build the CFC the join between the tables is an inner join if the foreign key field is not null, otherwise it creates a left outer join. Is that what you meant? I don't use Lazy Loading, which is a very cool feature that I know is in ColdSpring, I've never had so many tables that it has been an issue.
One of the features I have built in is that update and delete methods also return the number of affected rows, also each query has a unique name in the CFCs, which is handy for debugging. I also bulid in a search method which does all my %like% code for nvarchar and ntext fields and a method to return a list of fields and field lengths.
Cfdbinfo is powerful, but I do fall back on some good old SQL statements in my generator as well, which means it anly works for MSSQL server at the moment.
I hope I've answered your questions?!
Comment by John Whish – June 09, 2008
Comment by Russ Johnson – June 09, 2008
Now that makes a lot of sense, that's kind of how my method works. Thanks for the heads-up.
Comment by John Whish – June 09, 2008
Regarding the lazy loading...it's an option more geared towards dealing with a large number of records rather than a large number of tables.
The Transfer documentation uses the example of the related records you have in a blog application: one blog post, for example, can have any number of comment records associated with it. If you've defined a one-to-many relationship between blog posts and comments, when you ask Transfer for a Transfer object based on a blog post record, by default that Transfer object will instantiate objects for all of the related comments and place those objects in either an array or structure (your preference) within the Transfer object.
While there's nothing wrong with that, including all those related comment objects can take up a lot of extra cycles if you're trying to generate a collection of blog post objects for a list or a table. That's when you might want to set the "lazy" attribute in the relationship definition to "true" so that those blog post objects only retrieve and instantiate the related comment objects when you specifically call an object function that relies on those records.
At least, that's how I understand it to work.
Comment by Brian Swartzfager – June 09, 2008
Also, I don't see anything particularly "wrong" with mixing cfquery SQL with ORM methods. One thing I tend to do though is move all SQL outside of my controller-type files and into the classes themselves.
So, this means you could end up with using some simple findById() calls mixed in with some of your own custom methods like getWorkerCount() in your controller files. It will look nice and clean and the SQL is hidden away in the class files.
By the way, I'm assuming you are referring to ColdFusion on Wheels here so the examples are based on that but they apply to any ORM.
Comment by Per Djurner – June 10, 2008
Transfer decorators sound like the way to go. I have read up on Transfer and it has certainly moved on from the last time I looked at it. You're right of course about "Lazy loading", I was thinking in ColdSpring mode and not thinking! So, yes, my method can be set up to only get the data when it's actually needed.
@Per,
Thanks for your input. I wasn't referring to ColdFusion on Wheels, but as you say it is common to how ORMs work (as I understand it).
@Everyone else!
Perhaps I should explain a bit more about what I do. I have a cfc for each table in the database which generated for me by some code I wrote. The CRUD methods are all in there as seperate functions/methods. The select/read method returns an ColdFusion query object which is where my approach differs to ORMs. I also can do updates, deletes and inserts by passing a struct to the method.
Simple Examples:
----------------
In onApplicationStart of Application.cfc:
<cfset Application.Data = CreateObject('component', "cfc.dataManager").init(dsn) />
The dataManager.cfc goes off and dynamically loads each cfc (so you can just drop new ones in and it will find them).
To get data:
<cfset filter = StructNew() />
<cfset filter.active = 1 />
<cfset qryObject = Application.Data.MyTable.Read(filter) />
As you can see I can use any field in the database table to filter by.
To get delete:
<cfset filter = StructNew() />
<cfset filter.category = '12,40' />
<cfset affectedrows = Application.Data.MyTable.Delete(filter) />
This will delete all rows that have a category of 12 or 40 (by doing an SQL IN (12,40))
To update:
<cfset filter = StructNew() />
<cfset filter.category = '12,40' />
<cfset data = StructNew() />
<cfset data.active = 1 />
<cfset data.email = 'new@email.com' />
<cfset affectedrows = Application.Data.MyTable.Update(data, filter) />
This will set the active field value to 1 where the category id is 12 or 40.
It's not OO but it works well for me! :)
Another benefit is that the updated field in each table is populated automatically when the table is updated.
Comment by John Whish – June 10, 2008