2

This question is close to but not the same as the one here: NHibernate Query across multiple tables

Basically, my question is given the following model, how would I query to find out if the current dog has the name "foo" or a past dog has the name "foo" (disjunction). Essentially I have a Many-To-One relationship for CurrentDog and a Many-To-Many relationship for PastDogs.

public class Dog {
    public string name {get; set;}
}

public class Owner {
    public string firstname {get; set;}
    public string lastname {get; set;}
    public Dog CurrentDog {get; set;}
    public Dog[] PastDogs {get; set;}
}

I'd guess the SQL should look something like this:

    SELECT o.* FROM owners AS o
    INNER JOIN dogs AS cd ON o.current_dog_id = cd.id
    INNER JOIN owner_past_dog_maps AS pd ON o.id = pd.owner_id
    INNER JOIN dogs AS d ON pd.dog_id = d.id
    WHERE d.name = 'foo' 
    OR cd.name = 'foo'

Hope that makes sense... I'll try clarify it if anyone asks.

Community
  • 1
  • 1
longda
  • 10,153
  • 7
  • 46
  • 66
  • I guess I could put this in a new post but an extension to this question would be how to I return all the dogs that an owner has ever owned, past or present? – longda Aug 24 '11 at 01:32

2 Answers2

4

I've try to solve it using QueryOver and alias

Owner myOwner = null;
Dog myCurrentDog = null; 
Dog myPastDogs = null;

var sax = _HibSession.QueryOver<Owner>(() => myOwner)
                .JoinAlias(() => myOwner.CurrentDog, () => myCurrentDog, JoinType.InnerJoin)
                .JoinAlias(() => myOwner.PastDogs, () => myPastDogs , JoinType.InnerJoin)
                .Where(Restrictions.Disjunction()
                    .Add(Restrictions.EqProperty(myCurrentDog.Name,"foo"))
                    .Add(Restrictions.EqProperty(myPastDogs.Name,"foo"))                    
                )                                            
                .List<Owner>();

I hope it's helpful!

Faber
  • 2,194
  • 2
  • 27
  • 36
  • Oh cool. Different way to do it I guess... are there advantages to using the QueryOver pattern? – longda Aug 26 '11 at 18:20
  • 1
    I always use QueryOver pattern because there's no string to put inside the code (like .Restrictions.Eq("cd.Name","foo") as the Criteria pattern). So I reduce the probability to write wrong field name that causes runtime errors (e.g. .Restrictions.Eq("cd.NaNe","foo") throw an Exception only on runtime). Enjoy QueryOver! :-) – Faber Aug 28 '11 at 10:36
  • Here some example of its potentiality http://nhforge.org/blogs/nhibernate/archive/2009/12/17/queryover-in-nh-3-0.aspx – Faber Aug 29 '11 at 07:18
  • Oh, that's very nice. Thanks for the tip! – longda Aug 31 '11 at 19:04
0

I guess I should have started off that I was new to NHibernate. What I didn't understand was the aliasing going on in NHibernate and that you're creating aliases not for the tables but for the relationships and the property names. Behold, the solution that will return all the owners who have or had a dog name foo:

    var output = Session.CreateCriteria<Owner>()
        .CreateAlias("CurrentDog", "cd")
        .CreateAlias("PastDogs", "pd")
        .Add
        (
            Restrictions.Disjunction()
            .Add(Restrictions.Eq("cd.Name", "foo"))
            .Add(Restrictions.Eq("pd.Name", "foo"))
        )
        .List<Owner>();
longda
  • 10,153
  • 7
  • 46
  • 66