use Table Value Function in mJoin - .net MVC Project

montoyam

I have often successfully used a function in a .LeftJoin

.LeftJoin("udf_Lookup_FTEDeptName('" + AsOfCookie + "',1) as udf_Lookup_FTEDeptName", "udf_Lookup_FTEDeptName.ID", "=", "SystemUsers.DepartmentID")

However, I am getting an error when trying to use a function in a .MJoin.

                .MJoin(new MJoin("udf_SystemUsers_LineItemDetails('" + AsOfCookie + "') as LineItemDetails")
                    .Link("LineItemDetails.SystemUserID", "SystemUsers.SystemUserID")

I am getting the error

'udf_SystemUsers_LineItemDetails' is not a recognized built-in function name.

are .MJoin's not able to use a function? unfortunately I need to pass a date so I can not use a View.

allan

.MJoin's not able to use a function

No, I'm afraid they are not at this time. That said, the error suggests it might be trying to execute it. If you add .Debug(true) what is the JSON returned from the server please?

Allan

montoyam

I get the following:

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null
,"error":"'udf_SystemUsers_LineItemDetails' is not a recognized built-in function name."
,"fieldErrors":[],"id":null,"meta":{},"options":{},"searchPanes":{"options":{}},"files":{},"upload":{"id":null}
,"debug":[
{"Query":"SELECT  [SystemUserID] as 'SystemUserID', [EffectiveDate] as 'EffectiveDate', [ExpireDate] as 'ExpireDate', [FirstName] as 'FirstName', [LastName] as 'LastName', [EmailAddress] as 'EmailAddress' FROM  [SystemUsers] ","Bindings":[]}
,{"Query":"SELECT DISTINCT  [SystemUsers].[SystemUserID] as 'dteditor_pkey', udf_SystemUsers_LineItemDetails('07/01/2021') as 'LineItemDetails.LineItem', udf_SystemUsers_LineItemDetails('07/01/2021') as 'LineItemDetails.LineRate', udf_SystemUsers_LineItemDetails('07/01/2021') as 'LineItemDetails.DepartmentName' FROM  [SystemUsers]  JOIN [LineItemDetails] ON [SystemUsers].[SystemUserID] = [LineItemDetails].[SystemUserID]   JOIN udf_SystemUsers_LineItemDetails('07/01/2021') as LineItemDetails ON  =  ","Bindings":[]}],"cancelled":[]}

Yeah, that second query looks a bit odd.

    public class SystemUsersController : ApiController
    {
        [Route("api/SystemUsers")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult SystemUsers()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;

            var AsOfCookie = request.Cookies.Get("AsOfDate").Value;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "SystemUsers", "SystemUserID")
                .Model<SystemUsersModel>()
                .Field(new Field("EffectiveDate")
                    .Validator(Validation.NotEmpty())
                    .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                )
                .Field(new Field("ExpireDate")
                    .SetFormatter(Format.NullEmpty())
                    .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                )

                .MJoin(new MJoin("udf_SystemUsers_LineItemDetails('" + AsOfCookie + "') as LineItemDetails")
                    .Link("LineItemDetails.SystemUserID", "SystemUsers.SystemUserID")
                    .Field(new Field("LineItem"))
                    .Field(new Field("LineRate"))
                    .Field(new Field("DepartmentName"))
                )
                .Debug(true)
                .Process(request)
                .Data();
                return Json(response);
            }
        }
    }

montoyam

I got rid of the 'as LineItemDetails' in line 26 above and now get this:

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"'udf_SystemUsers_LineItemDetails' is not a recognized built-in function name.","fieldErrors":[],"id":null,"meta":{},"options":{},"searchPanes":{"options":{}},"files":{},"upload":{"id":null}
,"debug":[{"Query":"SELECT  [SystemUserID] as 'SystemUserID', [EffectiveDate] as 'EffectiveDate', [ExpireDate] as 'ExpireDate', [FirstName] as 'FirstName', [LastName] as 'LastName', [EmailAddress] as 'EmailAddress' FROM  [SystemUsers] ","Bindings":[]}
,{"Query":"SELECT DISTINCT  [SystemUsers].[SystemUserID] as 'dteditor_pkey', udf_SystemUsers_LineItemDetails('07/01/2021').LineItem as 'LineItem', udf_SystemUsers_LineItemDetails('07/01/2021').LineRate as 'LineRate', udf_SystemUsers_LineItemDetails('07/01/2021').DepartmentName as 'DepartmentName' FROM  [SystemUsers]  JOIN udf_SystemUsers_LineItemDetails('07/01/2021') ON [udf_SystemUsers_LineItemDetails]('07/01/2021').SystemUserID = SystemUsers.SystemUserID ","Bindings":[]}],"cancelled":[]}

It seems to me the MJoin does not handle the 'as' portion as the .LeftJoin does.

It should return a query like this:

SELECT DISTINCT  [SystemUsers].[SystemUserID] as 'dteditor_pkey'
, LineItems.LineItem as 'LineItem'
, LineItems.LineRate as 'LineRate'
, LineItems.DepartmentName as 'DepartmentName' 

FROM  [SystemUsers]  
JOIN udf_SystemUsers_LineItemDetails('07/01/2021') as LineItems ON LineItems.SystemUserID = SystemUsers.SystemUserID 

allan

Yes, reformatting it gives us:

SELECT DISTINCT 
    [SystemUsers].[SystemUserID] as 'dteditor_pkey',
    udf_SystemUsers_LineItemDetails('07/01/2021').LineItem as 'LineItem',
    udf_SystemUsers_LineItemDetails('07/01/2021').LineRate as 'LineRate',
    udf_SystemUsers_LineItemDetails('07/01/2021').DepartmentName as 'DepartmentName'
FROM  [SystemUsers]
JOIN udf_SystemUsers_LineItemDetails('07/01/2021')
    ON [udf_SystemUsers_LineItemDetails]('07/01/2021').SystemUserID = SystemUsers.SystemUserID 

I can see why you went for the as there! In the PHP versions of these libraries there is a tableAlias option which might resolve this issue. Let me have a think about how this might be resolved. It might be a few weeks before I get a chance to dig deeply into this at the moment though, as support requests are quite high at the moment.

Allan

montoyam

thank you. I created a function to use in place of the primary table (since it is read-only). It won't do exactly what I need, but it will be a band-aid until this gets worked out

allan

Nice workaround - thanks!

Allan

montoyam

Do you have an estimate as to when the alias in an mJoin may be looked into?

allan

It is planned for Editor 2.1 but that is likely some months away I'm afraid. Probably towards the end of 2021.

Allan

allan

Old thread this sorry! But I thought I'd offer a quick update while I'm going through my backlog. I've been reviewing how the PHP implementation works (I mentioned it above) and actually it wouldn't help at all as it renames the Editor host table, not the table that is Mjoin'ed.

I think the best way to get this working with Editor would be with a function or VIEW, similar to what you have done. I don't have a suitable change for this yet. Apologies for the massive delay.

Allan