CODEWITHSUNDEEP
hibernatenhibernate
Positonic
Positonic

Reputation: 9411

HQL join without foreign keys on the table

Is it possible to do a join between 2 tables that don't have foreign keys?

Edit: Ok, I have been assured this is possible, but I'm not entirely sure how to do it yet.

Classes

public class Booking
{
    public virtual int Id { get; set; }
    public virtual Int32 bookingID { get; set; }
    public virtual Int32 bookingAdminID { get; set; 
}

public class BookingLocation
{
    public virtual int Id { get; set; }
    public virtual Int32 bookingID { get; set; }
    public virtual Int32 locationID { get; set; } 
}

Mappings

 public BookingMap()
 {
    Table("Bookings");

    Id(x => x.Id).Column("ID");
    Map(x => x.bookingID).Column("BookingID");
    Map(x => x.bookingAdminID).Column("BookingAdminID");
 }

public class BookingLocation
{
    public virtual int Id { get; set; }
    public virtual Int32 bookingID { get; set; }
    public virtual Int32 locationID { get; set; } 
 }

I need to do a simple inner join on bookingID of both table above. How would I do that?

I have tried:

var hql = "select b from Booking as b inner join BookingLocation as bl on b.bookingID =   bl.bookingID";
var bookings = session.CreateQuery(hql).List<object[]>();

but I get the error:

Semantic exception was unhandled by user code Path expected for join!

[select b from Booking as b inner join BookingLocation as bl on b.bookingID = bl.bookingID]

I am taking this approach because I can't easily build a relationship between these 2 tables in the mapping. Do you need to have a relationship in order to do a join?

Upvotes: 1

Views: 8059

Answers (2)

Positonic
Positonic

Reputation: 9411

I was able to resolve this by adding relationships to my entities like so:

 public class Booking
 {
     public virtual int Id { get; set; }
     public virtual Int32 bookingID { get; set; }
     public virtual Int32 bookingAdminID { get; set; }


     public virtual IList<BookingLocation> BookingLocations { get; set; }

     public Booking()
     {
         BookingLocations = new List<BookingLocation>();
     }
 }

public class BookingLocation
{
    public virtual int Id { get; set; }
    public virtual Int32 bookingID { get; set; }
    public virtual Int32 locationID { get; set; } 
    public virtual Booking Booking { get; set; }
}

Even though they don't have foreign keys, I was then able to do a join like this:

var bookings = session.CreateCriteria<Booking>("p")
                              .CreateCriteria("p.BookingLocations", JoinType.InnerJoin)
                              .SetMaxResults(30) 
                              .List<Booking>();

Upvotes: 0

Baz1nga
Baz1nga

Reputation: 15579

by 2 tables you mean two classes that are mapped to 2 tables correct? In that case you can just use the class (say two classes Foo & Bar that are mapped to some tables) as follows:

session.CreateQuery("select * from A as a, B as b 
where a.Prop1=:value1 and b.Prop2=:value2" //conditions if any)
.SetString("value1",val1)
.SetString("value2",val2)
.List<object[]>();

Hope this is what you want.

Upvotes: 4

Related Questions