Join referenced property on joined collection element

Question

I have users and users_updateable_data tables which have 1-to-1 relationship (User.UpdateableData). Also each User can be in a Guild (there is a mapped Guild.Users; User.GuildId property is a foreign key for guilds).

So I have Guild, Guild.Users, User, User.UpdateableData, User.GuildId.

I'm trying to get specific properties from users which meet some criteria:

    public class UserTournamentInfo
    {
        public virtual int Id { get; set; } // from user
        public virtual int TournamentXP { get; set; } // from upd. data
        public virtual int League { get; set; } // from user
        public virtual int TournamentClassXP { get; set; } // from upd. data
        public virtual int? GuildId { get; set; } // from user
        public virtual int? GuildLeague { get; set; } // from guild
    }

So I need to make two joins: guild + users (right outer) and user + upd. data.

This is how I tried to do it:

    public virtual IEnumerable<UserTournamentInfo> GetTournamentUserToXP()
    {
        using (ISession session = SessionFactory.OpenSession())
        using (session.BeginTransaction())
        {
            User userAlias = null;
            User.UpdateableDataContainer userUpdAlias = null;
            Guild guildAlias = null;
            UserTournamentInfo dto2 = null;

            var result = session.QueryOver<Guild>(() => guildAlias)
                .JoinAlias(x => x.Users, () => userAlias, JoinType.RightOuterJoin)
                // next line doesn't compile
                .JoinAlias((User u) => u.UpdateableData, () => userUpdAlias, JoinType.RightOuterJoin)
                .Select(MakeProjections())
                .Where(() => userAlias.TournamentXP > 0)
                .TransformUsing(Transformers.AliasToBean<UserTournamentInfo>())
                .List<UserTournamentInfo>();

            session.Transaction.Commit();
            return result;
        }
    }

Here is how I define the projections:

    public static IProjection[] MakeProjections()
    {
        Guild guildAlias = null;
        User userAlias = null;
        User.UpdateableDataContainer userUpdAlias = null;
        UserTournamentInfo dto = null;

        return new[]
                {
     Projections.Property(() => userAlias.Id).WithAlias(() => dto.Id),
     Projections.Property(() => userUpdAlias.TournamentClassXP).WithAlias(() => dto.TournamentClassXP),
     Projections.Property(() => userUpdAlias.TournamentXP).WithAlias(() => dto.TournamentXP),
     Projections.Property(() => userAlias.GuildId).WithAlias(() => dto.GuildId),
     Projections.Property(() => userAlias.League).WithAlias(() => dto.League),
     Projections.Property(() => guildAlias.League).WithAlias(() => dto.GuildLeague),
                      };
    }

It worked before I decided to split users into two tables - users and users_updateable_data.

How to make this join query?

Answer 1

I solved it with HQL:

SELECT 
u.Id as Id, ud.TournamentClassXP as TournamentClassXP, ud.TournamentXP as TournamentXP, u.GuildId as GuildId, u.League as League, g.League as GuildLeague
FROM Guild g RIGHT OUTER JOIN g.Users u JOIN u.UpdateableData ud
WHERE ud.TournamentXP > 0