Help with complex query : Top + count(distinct x) + group by + order by

[W3Max]

Can you help me with this query (I'm using NHibernate 3 +
FluentNhibernate AutoMappings)

Copied from stackoverflow : http://stackoverflow.com/questions/4351683/how-can-i-do-this-query-with-nhibernate

How can I do this query with NHibernate

select top 10 count(distinct classedition.createdby_id) as editions,
class.id,
class.name, class.createdon, class.createdby_id
from class
inner join classedition on class.id = classedition.class_id
group by class.id, class.name, class.createdon, class.createdby_id
order by editions desc, class.createdon desc

I'm using NHibernate 3. I tried to do it with the new Linq provider
without success. I don't care about the way of doing it as long as it
produce the exact sql query above. I would prefer writing a strongly
typed query, without magic string if possible.

I'm new to NHibernate so this question may be simple.

Here is a little more info

I use Fluent NHibernate with AutoMappings. The C# classes are very
simple:

public class Class
{
public virtual int Id { get; set; }
public virtual string Name { get; set; }
public virtual DateTime CreatedOn { get; set; }
}

public class ClassEdition
{
public virtual int Id { get; set; }
public virtual Class Class { get; set; }
public virtual User CreatedBy { get; set; }
}

public class User
{
public virtual int Id { get; set; }
public virtual string Name { get; set; }
}

[Fabio Maulo]

what mean, for you, "magic string"?
if you will have an exception, for a wrong query, during the start-up
of your application, in your opinion, is an HQL a "magic string"?

P.S. If you have, at least, a test for that query perhaps a "magic
string" can be only a mirage.

--
Fabio Maulo

> --
> You received this message because you are subscribed to the Google Groups "nhusers" group.
> To post to this group, send email to nhu...@googlegroups.com.
> To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
>

[W3Max]

I don't understand why you take some time to answer the question and
buck on some words I maybe misused!?
I don't care about using HQL as long as it works! And what is this
thing about unit tests ?

Come on, I thought the user group would have been more friendly. Did I
insulted you by asking this question?

Thanks for nothing.

By the way, I resolved my problem by using a view.

On Dec 6, 10:19 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> what mean, for you, "magic string"?
> if you will have an exception, for a wrong query, during the start-up
> of your application, in your opinion, is an HQL a "magic string"?
>
> P.S. If you have, at least, a test for that query perhaps a "magic
> string" can be only a mirage.
>
> --
> Fabio Maulo
>

> El 06/12/2010, a las 21:14, W3Max <maxim...@gmail.com> escribió:
>
>
>
>
>
>
>
> > Can you help me with this query (I'm using NHibernate 3 +
> > FluentNhibernate AutoMappings)
>

> > Copied from stackoverflow :http://stackoverflow.com/questions/4351683/how-can-i-do-this-query-wi...

[Mohamed Meligy]

@W3Max

>> Thanks for nothing.

Please don't get me wrong, but I really would avoid using this statement (especially in public) at "all" times. Not trying to tell you what to do (sorry if sounds so), but really the guy is just trying to help in the way he sees appropriate. He sounds like saying that maybe you need to add some unit tests to capture such behaviors earlier or with better feedback or so ....

>> Come on, I thought the user group would have been more friendly. Did I
insulted you by asking this question?

Well, welcome to nhusers group. That's just how it is.

 

Mohamed Meligy
Readify | Senior Developer

M:+61 451 835006 | W: www.readify.net

    

[Diego Mijelshon]

BTW, here's the stackoverflow question, which I've already answered last week:

http://stackoverflow.com/questions/4351683/how-can-i-do-this-query-with-nhibernate/4351837#4351837

Funny, the solution is... a view :-D

 

    Diego

[W3Max]

BTW, have you read the last comment on stackoverflow?
The answer you gave does not work.

On Dec 8, 2:59 pm, Diego Mijelshon <di...@mijelshon.com.ar> wrote:
> BTW, here's the stackoverflow question, which I've already answered last

> week:http://stackoverflow.com/questions/4351683/how-can-i-do-this-query-wi...

>
> Funny, the solution is... a view :-D
>

>     DiegoOn Tue, Dec 7, 2010 at 21:35, Mohamed Meligy <eng.mel...@gmail.com> wrote:
> > @W3Max
>
> > >> Thanks for nothing.
>
> > Please don't get me wrong, but I really would avoid using
> > this statement (especially in public) at "all" times. Not trying to tell you
> > what to do (sorry if sounds so), but really the guy is just trying to help
> > in the way he sees appropriate. He sounds like saying that maybe you need to
> > add some unit tests to capture such behaviors earlier or with better
> > feedback or so ....
>
> > >> Come on, I thought the user group would have been more friendly. Did I
> > insulted you by asking this question?
>
> > Well, welcome to nhusers group. That's just how it is.
>

> > *Mohamed Meligy
> > *Readify | Senior Developer

>
> > M:+61 451 835006 | W:www.readify.net
> > [image: Description: Description: Description: Description: rss_16]  [image:
> > Description: Description: Description: Description:

> > cid:image003....@01CAF81D.6A076510] <http://www.linkedin.com/in/meligy>  [image:
> > Description: Description: Description: Description:
> > cid:image005....@01CAF81D.6A076510] <http://twitter.com/meligy>
> >  <http://www.greatplacetowork.com.au/best/best-companies-australia.php><http://www.readify.net/AboutUs/NewsItem.aspx?id=10>

> >> nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com >
> >> .

> >> > > For more options, visit this group athttp://
> >> groups.google.com/group/nhusers?hl=en.
>

> >> --
> >> You received this message because you are subscribed to the Google Groups
> >> "nhusers" group.
> >> To post to this group, send email to nhu...@googlegroups.com.
> >> To unsubscribe from this group, send email to

> >> nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com >
> >> .

> >> For more options, visit this group at
> >>http://groups.google.com/group/nhusers?hl=en.
>
> >  --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com >
> > .

[Diego Mijelshon]

You need to group by all the User properties.
 
    Diego