Self-referencing left join

Self-referencing left join

dynasoftdynasoft Posts: 422Questions: 67Answers: 3
edited February 2020 in DataTables 1.9

Hi

I'm trying to convert the following sql into valid server DT code but I'm struggling a bit translating the aliases and embedded select statement in the left join.

  • Here's the sql:

SELECT V.ID, ISNULL(V.CustomerIndex, C.CustomerIndex) as CustomerIndex

FROM GlobalPriceLists as V

LEFT JOIN (SELECT CustomerIndex, ListType, ListName FROM GlobalPriceLists WHERE CustomerIndex = 0) as C

ON C.ListType = V.ListType AND C.ListName = V.ListName AND V.CustomerIndex = 1

  • Server code so far:

editor = new Editor(db, "GlobalPriceLists", "GlobalPriceLists.id")
.Model<CustomerSNsDBModel.GlobalPriceLists>("GlobalPriceLists");
editor.Field(new Field("GlobalPriceLists.id")
.Set(false)
);
editor.MJoin(new MJoin("GlobalPriceLists")
.Model<PriceListsDBModel>()
.Name("GlobalPriceLists.CustomerIndex")
.Link("GlobalPriceLists.ListType", "GlobalPriceLists.ListType")
.Link("GlobalPriceLists.ListName", "GlobalPriceLists.ListName")
.Where(q =>
q.Where("GlobalPriceLists.CustomerIndex", lngCustIdx, "=")
)
.Order("GlobalPriceLists.id ASC")
.Field(new Field("id")
.Options(new Options()
.Table("GlobalPriceLists")
.Value("id")
.Label("CustomerIndex")
)
.Set(false)
)
.Set(false)
);
editor.Field(new Field("GlobalPriceLists.ListType")
.GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
);
editor.Field(new Field("GlobalPriceLists.ListName")
.GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? string.Empty : val)
);
editor.LeftJoin("GlobalPriceLists AS C", "GlobalPriceLists.id", "=", "GlobalPriceLists.SubPackID");
editor.Where("GlobalPriceLists.CustomerIndex", 0);
editor.Debug(true);
editor.Process(formData);

Thanks a lot.

Answers

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Btw, there is no editor for this DT so there is only a DT.

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Am I getting near w/ the code below? The code runs but the CustomerIndex shown in DT is for only CustomerIndex = 0. The idea is that if there is a record where CustomerIndex > 0 (if this is the case there is also a record for CustomerIndex = 0) then the CustomerIndex shown back to the user is this CustomerIndex greater than 0 and should not be 0. In my porgramme, records for CustomerIndex = 0 are general records. Records where CustomerIndex > 0 are specific to a customer. Records are defined by a type (ListType) and a name (ListName) which are identical whether CustomerIndex = 0 or CustomerIndex > 0.

                    editor.Field(new Field("myGlobalPriceLists1.CustomerIndex")
                        .Options(new Options()
                            .Table("GlobalPriceLists")
                            .Value("id")
                            .Label("CustomerIndex")
                        )
                    );
                    editor.LeftJoin("GlobalPriceLists AS myGlobalPriceLists1", "GlobalPriceLists.CustomerIndex", "=", "myGlobalPriceLists1.CustomerIndex").Where("myGlobalPriceLists1.CustomerIndex", 0);
                    editor.LeftJoin("GlobalPriceLists AS myGlobalPriceLists2", "GlobalPriceLists.ListType", "=", "myGlobalPriceLists2.ListType").Where("myGlobalPriceLists2.CustomerIndex", 0);
                    editor.LeftJoin("GlobalPriceLists AS myGlobalPriceLists3", "GlobalPriceLists.ListName", "=", "myGlobalPriceLists3.ListName").Where("myGlobalPriceLists3.CustomerIndex", 0);
    
  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    The sub-select you are using for the join in the original SQL means that the Editor LeftJoin method isn't going to be suitable here I'm afraid.

    What I would suggest in this case is that you create a VIEW that can be used for reading the data (and that way you can use your original SQL). There is an example of that available here - the .NET download has a matching example.

    Allan

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Many thanks

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3
    edited February 2020

    Hi
    I think I need to use a SP as a variable needs to be passed but I read your product does not support SPs.

    What is the best way forward and do you have examples? Thanks

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    You are correct - Editor doesn't support stored procedures (or at least, the .NET libraries we provide for it don't).

    Instead a VIEW would be the best way to do it as I suggested above, since you already know the SQL you want to use. Create a VIEW based on that and then Editor can read that exactly the same way it would with a regular TABLE.

    Allan

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    How would I pass a variable to the view?

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Hi
    Stll interested in getting a reply here but I went down the route of using an data array and pure sql in the end.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    You wouldn't be able to pass a variable to a VIEW - VIEWs are just virtual tables so you would query them the same as a standard table.

    Colin

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    I pointed out I need to pass a variable but Allan came back saying a view would work.

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    Sorry - I missed that point. Is the variable for the WHERE condition? If so, you can just apply a WHERE to the SQL as you would do with any table.

    Allan

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Hi. Thanks.
    I'm not clear still: You advised that I can't use your standard server code because I have embedded select statements; I can't use a view because I need to pass a variable (yes in the where clause) and DT does not support SP's. What is the best way forward? Is there a way to pass pure sql with a parameter? if not, when will this and using SPs be supported?

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    We don't currently have any plans to support stored procedures with our server-side libraries I'm afraid. If that is something you require you'd need to implement the server-side code for that in your project yourself.

    Regarding the view and a where condition - a view is read just like a table, so in SQL you would do:

    SELECT ...
    FROM view_name
    WHERE view_column = :boundVariable
    

    In the C# Editor code you would use .Where('view_column', myVariable).

    The assumption is that the VIEW includes a column that you can use as your condition.

    Allan

This discussion has been closed.