Complex Queries With Active Record
time to read 15 min | 2811 words

There are some things that you simply can't do effectively with an abstraction. Take Hierarchical Queries as a good example. They are very hard in SQL, there is no good way to solve the issue in a portable way. Therefor, each database has its own method to handle this issue. In SQL 2000, you would build a table valued function that you would query, in Oracle, you use CONNECT BY, and in SQL 2005 and DB2 (that I know of) you can use Common Table Expressions.

Out of the box, NHibernate can't handle this, but is exposes the hooks to allow this. A classic example is bringing all the messages in a thread in a single query. This is an issue since if you try to handle this by default, you'll get a query per each level in the hierarchy. Here is a simple message class, which has a parent message.

(Image from clipboard).png

And here is how we get all the messages in the hierarchy, for the message with id 43:

SimpleQuery<Message> msgs = new SimpleQuery<Message>(QueryLanguage.Sql, @"

WITH    AllMsgs ( id )

      AS ( SELECT   messages.id               FROM     Messages

           WHERE    messages.id = ?

           UNION ALL

           SELECT   messages.id               FROM     Messages

           INNER JOIN AllMsgs parent

           ON       messages.parent = parent.id )

 SELECT {msg.*}

 FROM   Messages msg

 WHERE  msg.id IN ( SELECT  id                 FROM    AllMsgs )  

", 43);

 

msgs.AddSqlReturnDefinition(typeof(Message),"msg");

Message[] messages = msgs.Execute();

The SQL query is standard SQL for SQL Server 2005, with two slight modifications. The ? marks a positional parameter, in this case, the 43 that we pass to the query. And the {msg.*}, which NHibernate will expand for you to the appropriate columns. It is important to notice that we need to tell the query the mapping between the Message type and the "msg" alias.

We brought all the message hierarchy to the application in one shot, instead of N+1. This is really good, if you are interested only in the properties of the message. A slight complication may arise if you want to handle the author for each message. Now we need to bring not only use the messages, but their authors, preferably in a single query. Let us see how we tackle this.

We will want to pull everything in one go, which means that we need to join between the messages and users table. This, in turn, means that we will get a list of tuples back. Here is the query itself:

SimpleQuery<object[]> msgs = new SimpleQuery<object[]>(typeof(Message),QueryLanguage.Sql, @"

WITH    AllMsgs ( id )

      AS ( SELECT   messages.id               FROM     Messages

           WHERE    messages.id = ?

           UNION ALL

           SELECT   messages.id               FROM     Messages

           INNER JOIN AllMsgs parent

           ON       messages.parent = parent.id )

 SELECT {msg.*}, {u.*}

 FROM   Messages msg JOIN Users u on msg.Author = u.id

 WHERE  msg.id IN ( SELECT  id                 FROM    AllMsgs )

", 43);

 

msgs.AddSqlReturnDefinition(typeof(Message),"msg");

msgs.AddSqlReturnDefinition(typeof(User), "u");

 

object[][] messages_and_users = msgs.Execute();

Here is how you use this now:

foreach (object[] message_and_user in messages_and_users)

{

       Message message = (Message) message_and_user[0];

       Console.WriteLine("{0} - {1}", message.Author.Name, message.Id);

}

Notice that even though we brought the user from the database, we don't access it from the tuple, we access it normally, from the message object. NHibernate is smart enough to figure out that it already loaded it, and can use it directly.

A note, NHibernate itself can use the mapping file to hide all of this from you, which you can later modify without touching the code (or even recompiling). Active Record has no such facility that I know of, though.

This post is dedicated to the localhost blogger.

Tweet Share Share 0 comments
Tags: