Self Reference <From>, JoinAlias

ServiceStack.OrmLite
1

Hi,

I have this scenario:

    var q = db.From<Task>()
             .LeftJoin<Task, Task>((parent, history) => parent.Id == history.ParentId, db.JoinAlias("history"));
q = q.And<Task,Task>((parent,history) =>  history.CreatedAt >= viewmodel.CreatedAtFromDateTime || parent.CreatedAt >= viewmodel.CreatedAtFromDateTime);

*Select statement is not relevant.

This outputs “LEFT JOIN “Tasks” history ON (“history”.“Id” = “history”.“ParentId”)”

Am i doing something wrong?

Thanks in advance

1 Like
2

You can’t use a previously defined JoinAlias in a WHERE condition since OrmLite does not know what Generic Type argument refers to what JOIN alias.

You’d need to extend the JOIN condition to incude any additional filters, e.g:

var q = db.From<Task>()
    .LeftJoin<Task, Task>((parent, history) => (parent.Id == history.ParentId)
            && (history.CreatedAt >= viewmodel.CreatedAtFromDateTime 
                  || parent.CreatedAt >= viewmodel.CreatedAtFromDateTime)
        ,db.JoinAlias("history"));
3

This:

var q = db.From<Task>()
      .LeftJoin<Task, Task>((parent, history) => parent.Id == history.ParentId
                                                    && (parent.CreatedAt >= viewmodel.CreatedAtFromDateTime
                                                        ||
                                                        history.CreatedAt >= viewmodel.CreatedAtFromDateTime), db.JoinAlias("history"))

outputs

FROM "Tasks" LEFT JOIN "Tasks" history ON (("history"."Id" = "history"."ParentId") AND (("history"."CreatedAt" >= '20160928 00:00:00.000') OR ("history"."CreatedAt" >= '20160928 00:00:00.000')))

Unfortunatelly it uses the alias for both tables :frowning:

4

Unfortunately OrmLite’s Typed APIs doesn’t support self joins as the Table name the Join Alias replaces is the same. You’d need to drop down to custom SQL for Self Join conditions, e.g:

var q = db.From<Task>()
    .CustomJoin("LEFT JOIN Task history ON (Task.Id = history.ParentId)")
    .Where("history.\"Created\" >= {0} OR Task.\"Created\" >= {0}", viewmodel.CreatedAtFromDateTime);