CODEWITHSUNDEEP
servicestackormlite-servicestackautoquery-servicestack
Jeff
Jeff

Reputation: 13199

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

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 */

Upvotes: 3

Views: 331

Answers (1)

mythz
mythz

Reputation: 143319

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.

Upvotes: 3

Related Questions