G
GrzybSon
Hello,
I have db with to tables Queue and Message. Every Queue has multiple
messages but message belongs to only one queue.
Sample data:
Table Queue
Id | Label
1 | Incoming
2 | Outgoing
Table Message:
Id | id_queue | id_messageType | dateAdded
---------------------------------------------------------------------
1 | 1 | 1 | 1.01.2009
2 | 1 | 2 | 1.01.2010
3 | 2 | 3 | 1.01.2008
So now I want list queues with info about last added message that
belongs to selected queue.
Sample output:
Id | Label | id_message | id_messageType | dateAdded
---------------------------------------------------------------------
1 | Incoming | 2 | 2 | 1.01.2010
2 | Outgoing | 2 | 3 | 1.01.2010
As long as db is MSSQL 2k5+ you can use ranking functions so SQL looks
like that:
SELECT q.id, q.Label, m.id as id_message, m.id_messageType, m.dateAdded
FROM Queue q
inner join
(
select id, id_queue, id_messageType, dateAdded
from
(
SELECT id, id_queue, id_messageType, dateAdded, rank() over
(partition by id_queue order by dateAdded desc) as r
FROM Message
) as sq
where sq.r = 1
) m on q.id = m.id_queue
My question is how to rewrite this query using NHibernate (ICriteria,
HQL)? Is there a way to force NH to use MS SQL specific functions like
RANK, ROW_NUMBER()?
Currently I have per-table mapping.
Thanx
I have db with to tables Queue and Message. Every Queue has multiple
messages but message belongs to only one queue.
Sample data:
Table Queue
Id | Label
1 | Incoming
2 | Outgoing
Table Message:
Id | id_queue | id_messageType | dateAdded
---------------------------------------------------------------------
1 | 1 | 1 | 1.01.2009
2 | 1 | 2 | 1.01.2010
3 | 2 | 3 | 1.01.2008
So now I want list queues with info about last added message that
belongs to selected queue.
Sample output:
Id | Label | id_message | id_messageType | dateAdded
---------------------------------------------------------------------
1 | Incoming | 2 | 2 | 1.01.2010
2 | Outgoing | 2 | 3 | 1.01.2010
As long as db is MSSQL 2k5+ you can use ranking functions so SQL looks
like that:
SELECT q.id, q.Label, m.id as id_message, m.id_messageType, m.dateAdded
FROM Queue q
inner join
(
select id, id_queue, id_messageType, dateAdded
from
(
SELECT id, id_queue, id_messageType, dateAdded, rank() over
(partition by id_queue order by dateAdded desc) as r
FROM Message
) as sq
where sq.r = 1
) m on q.id = m.id_queue
My question is how to rewrite this query using NHibernate (ICriteria,
HQL)? Is there a way to force NH to use MS SQL specific functions like
RANK, ROW_NUMBER()?
Currently I have per-table mapping.
Thanx