Yep... I decided the subquery was unnecessary and came up with something similar to what you did below (which works as intended) - thanks for the advice!
On Wed, Oct 1, 2008 at 1:30 PM, Ramon Smits <[EMAIL PROTECTED]> wrote: > > > Can't you just join in together?? > > select > art.Id, > count(*) as total > from > OrderLine as l > join l.Article as art > join art.Themes as t > join l.Order as o > where > t.Id = :themeId > and o.OrderState = 4 > group by > art.Id > order by > total > > Then you could use the top X result of this query result.. > > Just doing this straight from the head... but you get the idea. > > -- > Ramon > > > On Oct 1, 1:20 pm, "Jamie Fraser" <[EMAIL PROTECTED]> wrote: >> I'm trying to write something in HQL which isn't coming very naturally >> to me (I can do this in SQL no problem). >> >> Essentially I have >> >> select art >> from Article as art >> join art.Themes as t >> where art.Id in >> (select l.Article.Id >> from OrderLine as l >> join l.Order as o >> where o.OrderState = 4 >> group by l.Article.Id >> ) >> >> and t.Id = :themeId >> >> This gets me all the "articles" for a specified "theme" where the >> article has been ordered and the order.status = 4. >> >> This is fine, however in SQL I would have an ORDER BY within the >> subquery so that I could order by a count, as I only want the top N >> items ordered. I can't do the count in HQL because it would require me >> to use SELECT TOP also, which isn't supported. >> >> Can anyone help? > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
