CODEWITHSUNDEEP
nhibernatequeryoversummary
Macko
Macko

Reputation: 1

NHibernate QueryOver to select one entity for each group entity

I'm writing part of turist blog so I have two entities: place and photo in relation 1 to many, so every place can have multiple photos, but single photo belongs to one place.

My aim is to create summary: last added 3 places and with every place I want to have one photo (for example photo with the lower id, it doesn't matter). It's important to have not only the id of the photo, but more info (columns) like photo label, path to photo,etc.

Using SQL in MS SQL 2008 it can be done by using rank functions:

select top 3 * from
(
SELECT     p.id, p.label, p.VisitDate, ph.id AS photo_id, ph.Label, ph.Path,
RANK() OVER (PARTITION BY p.id ORDER BY ph.id) AS _rank
FROM         place AS p INNER JOIN
                      photo AS ph ON p.id = ph.place_id
) ranked
where _rank = 1
order by VisitDate desc

Or it can be resolved without ranking:

SELECT     TOP (3) a.id AS ida, z.id AS idz, z.etykieta, z.sciezka, a.data_odwiedzenia
FROM         place AS a INNER JOIN
                      photo AS z ON a.id = z.id_atrakcji
and z.id in 
(
SELECT  min(z.id) AS idz
FROM photo z 
where z.id_atrakcji in (select top 3 a.id from place a order by a.data_dodania desc)
group by z.id_atrakcji
)
ORDER BY a.data_odwiedzenia DESC

I started writing this query without ranking, but I've stucked.

    summaryList =
 session.QueryOver(() => placeAlias)
.JoinAlias(o => o.Photos, () => photoAlias)
.SelectList(list => list
.Select(() => placeAlias.Id).WithAlias(() => summaryAlias.PlaceId)
.Select(() => placeAlias.Etykieta).WithAlias(() => summaryAlias.PlaceName)
.Select(() => photoAlias.Etykieta).WithAlias(() => summaryAlias.PhotoLabel)
.Select(() => photoAlias.Id).WithAlias(() => summaryAlias.PhotoId)
)
.OrderByAlias(() => placeAlias.VisitDate).Desc
.WithSubquery.WhereProperty(x => x.Id).In(lastPlaces)
.TransformUsing(Transformers.AliasToBean<PlacesSummary>())
.List<PlacesSummary>();

Can you help me with this? How to write lastPlaces queryover or maybe there's another approach?

Regards, Macko

Upvotes: 0

Views: 2908

Answers (2)

Macko
Macko

Reputation: 1

it doesn't work as it should but you it is very close:

summaryList =
session.QueryOver(() => placeAlias)
.OrderByAlias(() => placeAlias.VisitDate).Desc
.JoinAlias(o => o.Photos, () => photoAlias)
**.WithSubquery.WhereProperty(()=> photoAlias.Id).Eq(subquery)**
.SelectList(list => list
.Select(() => placeAlias.Id).WithAlias(() => summaryAlias.PlaceId)
.Select(() => placeAlias.Etykieta).WithAlias(() => summaryAlias.PlaceName)
.Select(() => photoAlias.Etykieta).WithAlias(() => summaryAlias.PhotoLabel)
.Select(() => photoAlias.Id).WithAlias(() => summaryAlias.PhotoId))
.TransformUsing(Transformers.AliasToBean<PlacesSummary>())
.Take(3)
.List<PlacesSummary>();

It differs in only one line .WithSubquery where orginally it refers to Place instead it should refer to Photo. Now it's working and it produces following SQL:

SELECT TOP (3)  this_.id as y0_, this_.PlaceName as y1_, photoalias1_.PhotoName as y2_, photoalias1_.id as y3_ 
FROM [place] this_ inner join [photo] photoalias1_ on this_.id=photoalias1_.id_place 
WHERE photoalias1_.id = (SELECT min(this_0_.id) as y0_ FROM [photo] this_0_ WHERE this_0_.id_place = this_.id) 
ORDER BY this_.DateVisit desc

It's working great but I have another question:

How to rewrite in QueryOver such SQL:

SELECT  min(id)
FROM photo
where id in (... it doesn't matter what's here ...)
group by place_id

So I want only list of photo ids (only column) and this query will be used as subquery in another queryover. Can it be done by QueryOver syntax?

Upvotes: 0

Firo
Firo

Reputation: 30813

var subquery = QueryOver.Of(() => photoAlias)
    .Where(photo => photo.Place.Id == placeAlias.Id)
    .Select(Projections.Max<Photo>(photo => photo.Id));

var summaryList = session.QueryOver(() => placeAlias)
    .OrderBy(() => placeAlias.VisitDate).Desc
    .JoinQueryOver(() => placeAlias.Photos, () => photoAlias)
    .WithSubquery.WhereProperty(photo => photo.Id).Eq(subquery)
    .SelectList(list => list
        .Select(() => placeAlias.Id).WithAlias(() => summaryAlias.PlaceId)
        .Select(() => placeAlias.Etykieta).WithAlias(() => summaryAlias.PlaceName)
        .Select(() => photoAlias.Etykieta).WithAlias(() => summaryAlias.PhotoLabel)
        .Select(() => photoAlias.Id).WithAlias(() => summaryAlias.PhotoId)
    )
    .TransformUsing(Transformers.AliasToBean<PlacesSummary>())
    .List<PlacesSummary>();

cant test it right now

Upvotes: 1

Related Questions