How to JOIN more than 2 tables

ServiceStack.OrmLite
1

I cannot realize how to join more than 2 tables.

This is the example query I would obtain from OrmLite

SELECT *
FROM “LRAANALISI”
INNER JOIN “LRDANALISI” ON (“LRAANALISI”.“IDAANALISI” = “LRDANALISI”.“IDDANALISI”)
INNER JOIN “LRDRISULTATI” ON (“LRAANALISI”.“DANALISIID” = “LRDRISULTATI”.“DANALISIID”)
LEFT JOIN “LRARISULTATI” ON (“LRAANALISI”.“IDAANALISI” = “LRARISULTATI”.“AANALISIID”
AND LRAANALISI.IDAANALISI = LRDRISULTATI.DANALISIID
AND LRARISULTATI.DRISULTATOID = LRDRISULTATI.IDDRISULTATO)

This is my OrmLite code:

var q = db.From<LRAAnalisi>();
q.Join<LRAAnalisi, LRDAnalisi>((ana, dana) => ana.Id == dana.Id);
q.Join<LRAAnalisi, LRDRisultato>((ana, dris) => ana.AnalisiId == dris.AnalisiId);
q.LeftJoin<LRAAnalisi, LRARisultato>((ana, ris) => ana.Id == ris.AnalisiId);
q.LeftJoin<LRARisultato, LRDRisultato>((ris, dris) => ris.RisultatoId == dris.Id);
q.LeftJoin<LRAAnalisi, LRDRisultato>((ana, dris) => ana.AnalisiId == dris.AnalisiId);
db.Select<LRAAnalisi>(q).PrintDump();

as I cannot do q.LeftJoin<t1, t2, t3> I append multiple LeftJoin<> but the sql I obtain doesn’t execute:

SELECT *
FROM “LRAANALISI”
INNER JOIN “LRDANALISI” ON (“LRAANALISI”.“IDAANALISI” = “LRDANALISI”.“IDDANALISI”)
INNER JOIN “LRDRISULTATI” ON (“LRAANALISI”.“DANALISIID” = “LRDRISULTATI”.“DANALISIID”)
LEFT JOIN “LRARISULTATI” ON (“LRAANALISI”.“IDAANALISI” = “LRARISULTATI”.“AANALISIID”)
LEFT JOIN “LRDRISULTATI” ON (“LRARISULTATI”.“DRISULTATOID” = “LRDRISULTATI”.“IDDRISULTATO”)
LEFT JOIN “LRDRISULTATI” ON (“LRAANALISI”.“DANALISIID” = “LRDRISULTATI”.“DANALISIID”)

the LEFT JOIN should be:

LEFT JOIN “LRARISULTATI” ON (“LRAANALISI”.“IDAANALISI” = “LRARISULTATI”.“AANALISIID”
AND LRAANALISI.IDAANALISI = LRDRISULTATI.DANALISIID
AND LRARISULTATI.DRISULTATOID = LRDRISULTATI.IDDRISULTATO)

Regards

2

You should be able to add a WHERE to apply other conditions, e.g. something like:

var q = db.From<LRAAnalisi>()
    .Join<LRDAnalisi>((ana, dana) => ana.Id == dana.Id)
    .Join<LRDRisultato>((ana, dris) => ana.AnalisiId == dris.AnalisiId)
    .LeftJoin<LRARisultato>((ana, ris) => ana.Id == ris.AnalisiId)
    .Where<LRDRisultato,LRARisultato>((dris, ris) => ris.RisultatoId == dris.Id)
    .And<LRAAnalisi,LRDRisultato>((ana, dris) =>  ana.AnalisiId == dris.AnalisiId);

db.Select(q).PrintDump();

Note: the AND LRAANALISI.IDAANALISI = LRDRISULTATI.DANALISIID on the last LEFT JOIN is odd since it doesn’t refer to either of the tables being joined.

3

You are right, the AND is redundant, I apologize, many hours spent on this today.

But in spite of this I think moving part of the join to the where cut off some records from the result set.
When LRARISULTATO doesnt match any LRAANALISI the where cut off all unmatched LRAANALISI from the result set.

These are the two queries, the first is what I would, the second is what your code generate.
(I took away the redundant AND from both).

SELECT *
FROM “LRAANALISI”
INNER JOIN “LRDANALISI” ON (“LRAANALISI”.“IDAANALISI” = “LRDANALISI”.“IDDANALISI”)
INNER JOIN “LRDRISULTATI” ON (“LRAANALISI”.“DANALISIID” = “LRDRISULTATI”.“DANALISIID”)
LEFT JOIN “LRARISULTATI” ON (“LRAANALISI”.“IDAANALISI” = “LRARISULTATI”.“AANALISIID”
AND LRARISULTATI.DRISULTATOID = LRDRISULTATI.IDDRISULTATO
)

SELECT *
FROM “LRAANALISI”
INNER JOIN “LRDANALISI” ON (“LRAANALISI”.“IDAANALISI” = “LRDANALISI”.“IDDANALISI”)
INNER JOIN “LRDRISULTATI” ON (“LRAANALISI”.“DANALISIID” = “LRDRISULTATI”.“DANALISIID”)
LEFT JOIN “LRARISULTATI” ON (“LRAANALISI”.“IDAANALISI” = “LRARISULTATI”.“AANALISIID”)

WHERE (“LRARISULTATI”.“DRISULTATOID” = “LRDRISULTATI”.“IDDRISULTATO”)

When I execute these statements I get all the expected records from the first but not from the latter.

4

Is there a way to overcome this limitation?

I can upload the backup of the database where the issue arise.

Please let me know. Thanks.

5

I’ve added support for referencing up to 4 tables in JOINs in this commit.

So you can now reference up to 4 tables in JOINs using a Typed API:

var q = db.From<Table1>()
    .Join<Table2>((t1, t2) => t1.Id == t2.Id)
    .Join<Table3>((t1, t3) => t1.Id == t3.Id)
    .LeftJoin<Table1, Table4, Table2, Table3>((t1, t4, t2, t3) =>
        t1.Id == t4.Id && t4.Id == t2.Id && t4.Id == t3.Id);

var results = db.Select(q);

db.GetLastSql().Print();
results.PrintDump();

This new support is available from v4.5.9 that’s now available on MyGet

To show you different customization options possible in OrmLite I’ve also added different examples of how you can achieve a similar result with the current v4.5.8 version of OrmLite by varying level of custom SQL:

Using Custom JOIN Expression

var q = db.From<Table1>()
    .Join<Table2>((t1, t2) => t1.Id == t2.Id)
    .LeftJoin<Table1, Table3>((t1, t3) => t1.Id == t3.Id,
        (dialect, modelDef, joinExpr) =>
        {
            var injectJoin = " AND Table3.Id = Table2.Id)";
            return dialect.GetQuotedTableName(modelDef.ModelName)
                    + " " + joinExpr.Replace(")", injectJoin);
        });

var results = db.Select(q);

Using Custom JOIN SQL

var q = db.From<Table1>()
    .Join<Table2>((t1, t2) => t1.Id == t2.Id)
    .CustomJoin("LEFT JOIN Table3 ON (Table1.Id = Table3.Id AND Table3.Id = Table2.Id)");

var results = db.Select(q);

Using Custom SQL

var results = db.SqlList<Table1>(@"SELECT Table1.* 
    FROM Table1 
    INNER JOIN Table2 ON (Table1.Id = Table2.Id) 
    LEFT JOIN Table3 ON (Table1.Id = Table3.Id AND Table3.Id = Table2.Id)");
6

For future OrmLite support it’s much preferable to create a stand-alone, live example on Gistlyn so we can easily corroborate on the same solution.

7

Absolutely, thank you.

When do you plan to release version 4.5.9?

8

v4.5.9 is our pre-release versions which is already available on MyGet: http://docs.servicestack.net/myget

Our next release on NuGet will have an even number, e.g. v4.5.10.