use Table Value Function in mJoin - .net MVC Project

use Table Value Function in mJoin - .net MVC Project

montoyammontoyam Posts: 568Questions: 136Answers: 5

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.

Answers

  • allanallan Posts: 63,601Questions: 1Answers: 10,486 Site admin

    .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

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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); } } }
  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    edited August 2021

    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 
    
  • allanallan Posts: 63,601Questions: 1Answers: 10,486 Site admin

    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

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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 :)

  • allanallan Posts: 63,601Questions: 1Answers: 10,486 Site admin

    Nice workaround - thanks!

    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

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

  • allanallan Posts: 63,601Questions: 1Answers: 10,486 Site admin

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

    Allan

  • allanallan Posts: 63,601Questions: 1Answers: 10,486 Site admin

    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

Sign In or Register to comment.