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 issues 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...