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
-~----------~----~----~----~------~----~------~--~---
