CODEWITHSUNDEEP
ormlite-servicestack
Kai-Rune
Kai-Rune

Reputation: 462

Joining same table multiple times in ServiceStack.OrmLite

When joining a table to itself, the sql statment generated does not reference the tables correctly.

It's working when the "main" table is different from the joining table https://github.com/ServiceStack/ServiceStack.OrmLite#join-aliases

Class

public class Page
{
    public string ActivityId { get; set; }
    public int DefinitionId { get; set; }
    public int PageId { get; set; }
}

Code

using (var db = connection.Open())
{
    var sql = db.From<Page>()
     .Join<Page>((p1, p2) => 
       p1.DefinitionId == 349 && 
       p1.ActivityId == "a633326227969545457" && 
       p1.PageId == p2.PageId && 
       p2.DefinitionId == 340, db.JoinAlias("p2"))
     .Select<Page>(p => new {
            String = Sql.JoinAlias(p.ActivityId, "p2")
     });

}

SQL statement

p1.DefinitionId == 349 and p1.ActivityId == "a633326227969545457", these should not refer to p2

SELECT p2."ActivityId" AS String 
FROM "Page" INNER JOIN "Page" p2 ON (
    ((("p2"."DefinitionId" = 349) 
  AND ("p2"."ActivityId" = 'a633326227969545457')) 
  AND ("p2"."PageId" = "p2"."PageId")) 
  AND ("p2"."DefinitionId" = 340))

Is it a bug or am I missing something here?

Upvotes: 2

Views: 644

Answers (1)

mythz
mythz

Reputation: 143284

In order to be able to use an alias on the source table you would need to set a table alias which wasn't supported until now with this commit.

You can now use the new db.TableAlias() API (which is now preferable over JoinAlias()) on both the source table as well as any join tables, e.g:

var q = db.From<Page>(db.TableAlias("p1"))
    .Join<Page>((p1, p2) => 
        p1.DefinitionId == 349 && 
        p1.ActivityId == "a633326227969545457" && 
        p1.PageId == p2.PageId && 
        p2.DefinitionId == 340, db.TableAlias("p2"))
    .Select<Page>(p => new {
        String = Sql.TableAlias(p.ActivityId, "p2")
    });

var rows = db.Select(q);

This change is available from v5.4.1 that's now available on MyGet.

Upvotes: 1

Related Questions