Using CF9 ORM for listing pages: HQL or SQL?

I have an ORM mapping which has:

magazine object with a many-to-one relationship to a genre and a one-to-many relationship to issues

I have an admin page which displays a listing of basic magazine info, the magazines genre (looked up from the linked genre object) and the number of issues currently stored.

It all works fine simply using

EntityLoad('magazine');

...but a glance at the console log shows that it's doing one query for the list of magazines, one query to look up each different genre name, and one query on each magazine to load all iss开发者_C百科ues for that magazine (just to get the count!). As the dataset gets larger, this is going to be a bigger and bigger performance hit.

Of course, traditionally I'd just have a joined SQL query which would return all the data I need in one query. Is there a way of achieving the same in HQL, or would I be better off with a standard SQL query?

I had a look at the HQL "SELECT" syntax, but it's not very user-friendly as it returns queries with specifically-named columns as an array of arrays, which isn't particularly pleasant to work with...

Something that can help with this is using a fetching strategy. Basically, you can give ORM the instruction to grab those related records using a join

You have to set fetch="join" on the one to many relationship.

For more info on this see: http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSE01EDE03-6E6B-4669-8C54-358460778450.html