AutoQuery: join tables via a middle table and define which FK to join on

Question

We started using ServiceStack AutoQuery recently. It's a nice feature and we really enjoyed it. We have a table structure like this (minified version to make it easy to read):

Salary [Id (PK), ManagerId (FK)] 
Manager [Id (PK), DepartmentId (FK)] /* Manager is like Employee table even thought it's named Manager */
Department [Id (PK), ActiveManagerId (FK)] /* The FK ActiveManagerId is supposed to be a shortcut, it's Nullable.  */

So theoretically we can have joins like so

Salary -> Manager via Salary.ManagerId = Manager.Id
Manager -> Department via Manager.DepartmentId = Department.Id
Department -> Manager via Department.ActiveManagerId = Manager.Id

However in this specific case, if we join from Department to Manager via Department.ActiveManagerId = Manager.Id will not produce correct results because Department.ActiveManagerId is a shortcut and designed for other scenarios.

So when I define AutoQuery like so

public class SalaryQuery : QueryBase<Salary, SalaryQueryResult>,
 ILeftJoin<Salary, Manager, Department>

Below SQL is produced by AutoQuery which is correct from ServiceStack AutoQuery perspective.

select  s.Id
    ,   d.Id
from    Salary s
    left join
        Manager m
    on  s.ManagerId = m.Id
    left join
        Department d
    on  d.ActiveManagerId = m.Id /* should NOT use shortcut: Department -> Manager via Department.ActiveManagerId = Manager.Id */

But instead we want the ability to produce SQL which looks like this

select  s.Id
    ,   d.Id
from    Salary s
    left join
        Manager m
    on  s.ManagerId = m.id
    left join
        Department d
    on  d.Id = m.DepartmentId /* should use the desired FK: Manager -> Department via Manager.DepartmentId = Department.Id */

score 3 · Accepted Answer · answered May 02 '16 at 07:03

3

If you want different JOIN behavior you would need to add the custom LEFT JOIN in a Custom AutoQuery implementation, e.g:

//AutoQuery DTO
public class SalaryQuery : QueryDb<Salary,SalaryQueryResult>, ILeftJoin<Salary,Manager>

//Implementation
public class MyServices : Service
{
    public IAutoQueryDb AutoQuery { get; set; }

    public object Any(SalaryQuery query)
    {
        var q = AutoQuery.CreateQuery(query, base.Request)
            .LeftJoin<Manager, Department>((m, d) => d.Id == m.DepartmentId);

        return AutoQuery.Execute(query, q);
    } 
}

Note: From v4.0.56 QueryBase<T> has been deprecated and renamed to QueryDb.

answered May 02 '16 at 07:03

mythz

141,670
29
246
390

1

I was hoping there would be some kind of attribute to let me manually define the join ON so that it saves me from having to write the manual Service implementation. Ok. I'll do the suggested way. Thanks @mythz. – Jeff May 02 '16 at 23:07
I believe there might be a bug in AQ (i.e. being too flexible). If I have ForeignKey attribute declared on Department.ActiveManagerId in C#, the produced sql will join ON (d.ActiveManagerId = m.Id) - 1st sql in original question, however if I remove ForeignKey on Department.ActiveManagerId, the sql produced will join ON (d.Id = m.DepartmentId) - 2nd sql in original question. Because Manager.DepartmentId (in C#) also has the ForeignKey attribute declared. So why would AQ produce different join ON when the join declaration stays the same (i.e. ILeftJoin)? – Jeff May 02 '16 at 23:57
1

@Jeff Because the [ForeignKey and References attributes has precedence for defining references](https://github.com/ServiceStack/ServiceStack.OrmLite#foreign-key-and-references-attributes). – mythz May 03 '16 at 00:11
Example 1: public class Manager { [ForeignKey(typeof(Department))] public Guid DepartmentId {get;set;} } public class Department { [ForeignKey(typeof(Department), OnDelete = "SET NULL")] public Guid ActiveManagerId {get;set;} } which generates join on d.ActiveManagerId = m.Id (RHS.FKId = LHS.Id) – Jeff May 03 '16 at 00:33
Example 2: public class Manager { [ForeignKey(typeof(Department))] public Guid DepartmentId {get;set;} } public class Department { //Removed FK attribute here public Guid ActiveManagerId {get;set;} } generates join on d.Id = m.DepartmentId (RHS.Id = LHS.FKId) – Jeff May 03 '16 at 00:35
By RHS/LHS I mean the orders which the tables are defined in ILeftJoin. So shouldn't example 2 produce some kind of exception because AQ can't find the mapping FK either by attribute or by convention from Department? Sorry for the multiple comments. – Jeff May 03 '16 at 00:36
1

@Jeff Not sure what's being disputed, in the absence of attributes it falls back to the [implicit reference conventions](https://github.com/ServiceStack/ServiceStack.OrmLite#reference-conventions) of `Department.Id = Manager.DepartmentId`. – mythz May 03 '16 at 01:06
I'm saying if I write ILeftJoin, I should expact the join to be on Department.ActiveManagerId = Manager.Id (D -> M). It should never be Department.Id = Manager.DepartmentId (M -> D). I am saying the orders defined in ILeftJoin should matter, the order should determine which table to pick the FK from. But I guess from AQ perspective, it's just trying to figure out whether there's a convention and / or whether there's an attribute FK/Reference so the orders defined in ILeftJoin shouldn't matter. I see what you are saying, the orders shouldn't matter at all. – Jeff May 03 '16 at 01:26
And I guess having bi-directional design in the first place was what caused all the shebang. – Jeff May 03 '16 at 01:30

AutoQuery: join tables via a middle table and define which FK to join on

1 Answers1