Rewriting postgres query to NHibernate

Question

Please can you advise how I can write the following using NHibernate's QueryOver<>() Query<>() or other NHibernate method that will not involve me rewriting this (and numerous other similar queries) when switching DB provider?

The subqueries with multiple table joins are making this a pain for me to translate with my limited exposure of NHibernate.

return Session.CreateSQLQuery("select " +
                               "    boards.id, boards.name, boards.description, " +
                               "    (" +
                               "        select " +
                               "            count(topic.id) " +
                               "        from topic " +
                               "        left join users on users.id=topic.user " +
                               "        left join boards b on b.id=topic.bid " +
                               "        left join boards b2 on b2.id=b.bid " +
                               "        where (topic.bid=boards.id or b.bid = boards.id or b2.bid = boards.id) " +
                               "        and (b.type <= (:userType)) " +
                               "        and (b2.type is null or b2.type <= (:userType)) " +
                               "        and users.type > 0 " +
                               "        and users.chatban = 0" +
                               "    ) as TopicCount," +
                               "    (" +
                               "        select (max(posts.time) - max(read.time)) as t " +
                               "        from posts " +
                               "        left join users u on u.id=posts.user" +
                               "        left join topic on topic.id=posts.tid " +
                               "        left join read on read.topic=topic.id and read.userid=(:userId) " +
                               "        left join boards b on b.id=topic.bid" +
                               "        left join boards b2 on b2.id=b.bid" +
                               "        where " +
                               "            (topic.bid=boards.id or b.bid = boards.id or b2.bid = boards.id) " +
                               "            and (b.type <= (:userType)) " +
                               "            and (b2.type is null or b2.type <= (:userType)) " +
                               "            and not exists (select boardid from boardhigh where boardid=b.id and userid=(:userId))" +
                               "            and u.type > 0 " +
                               "            and u.chatban = 0 " +
                               "        group by topic.id " +
                               "        order by t desc " +
                               "        limit 1" +
                               "    ) as time " +
                               "from boards " +
                               "left join topic on topic.bid=boards.id " +
                               "where" +
                               "    boards.type <= (:userType) " +
                               (parentBoard.HasValue ? " and boards.bid = " + parentBoard.Value : "") +
                               "group by boards.id, boards.name, boards.description, boards.display " +
                               "order by boards.display desc, boards.name asc"
            )
            .SetInt32("userType", (int)UserHelper.LoggedInUser.UserType)
            .SetInt64("userId", UserHelper.LoggedInUser.Id)
            .List()
            .Cast<object[]>()
            .ToList()
            .Select(x => new BoardValueObject
            {
                Id = (int)x[0],
                Name = x[1].ToString(),
                Description = x[2].ToString(),
                TopicCount = (long)x[3],
                Time = x[4] as int?
            })
            .ToList();

Thanks

Answer 1

I would heavily doubt that you'll ever recieve answer for your giant SQL Query - and its conversion. Meanwhile, I would like to point out some differences in approach/thinking when using NHibernate.

• Use entities first. Define your model, the business domain model with Users, Topics, Boards.
• Spend some time to understand the lazy loading. Lot of data could be recieved by simple query to get User(s), and then nagivating to his boards, topics... All data needed will be loaded in separted SQL statements, but without complex querying.
• Do check the advantages fo batch-loading, which is a technique how to avoid 1+N SELECT issue. It is really working well and has surprisingly large effect on amount of issued SQL statements.
• DO NOT WORRY to use native SQL to recieve "..." data required by users (replace dots with words like crazy, unbelievable, complex). NHibernate is ORM at first place. It does great job for all CRUD operations related to Business object model. Any "SP like selects" are supported but indirectly... via CreateSQLQuery

So, try to start with simple stuff and once that is working, even the complex one will.