left join within mjoin - .net project

left join within mjoin - .net project

montoyammontoyam Posts: 568Questions: 136Answers: 5

I am doing an mjoin to a table. that secondary table has a foreign key to yet another table. In that Mjoin I would like to pull in a field from that third table, but I don't see that a .LeftJoin is possible within the .MJoin.

I know I can create a view which de-normalizes the brings in the field from that third table, but I was hoping I could do this in my controller without creating a new View.

is this possible?

here is the code I have so far. I brought that third "table" in as an option for LineItemID, but I think only the Editor can use the options to display the field called ItemDisplay, the dataTable, can't display that field, correct?

                .MJoin(new MJoin("SystemUsers_LineItems")
                    .Link("SystemUsers_LineItems.SystemUserID", "SystemUsers.SystemUserID")
                    //.LeftJoin("udf_Lookup_LineItemID('" + AsOfCookie + "') as LineItemLookup", "SystemUsers_LineItems.LineItemID", "=", "LineItemLookup.LineItemID")

                    .Field(new Field("UserLineItemID"))
                    .Field(new Field("LineItemID")
                        .Options(new Options()
                            .Table("udf_Lookup_LineItemID('" + AsOfCookie + "') as LineItemLookup")
                            .Value("LineItemID")
                            .Label("ItemDisplay")
                        )
                 )

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited May 2021

    You can't do a left join in the options instance but you can do an implicit old fashioned inner join using the WHERE clause. In this case this should work as well provided every "LineItem" has "ItemDisplay".

    Here is a complex one INNER JOINING two additional tables (ctr_govdept_has_ctr_installation and ctr_installation). You can see the two joins in the WHERE clause. You would just need to do this in .NET if that is what you are using. I do it in PHP.

    $q ->where('ctr_govdept.id', 'ctr_govdept_has_ctr_installation.ctr_govdept_id', '=', false); //join
    $q ->where('ctr_govdept_has_ctr_installation.ctr_installation_id', 'ctr_installation.id', '=', false); //join
    
    Field::inst( 'ctr_govdept_has_user.ctr_govdept_id' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
        ->options( Options::inst()
            ->table('ctr_govdept, ctr_govdept_has_ctr_installation, ctr_installation')
            ->value('ctr_govdept.id')
            ->label( array('ctr_installation.inst_name', 'ctr_govdept.dept_name') )
            ->render( function ( $row ) {               
                return $row['ctr_installation.inst_name'].' / '.$row['ctr_govdept.dept_name']; 
            } )
            ->order( 'ctr_installation.inst_name,  ctr_govdept.dept_name asc' )
            //where clause MUST be a closure function in Options!!!
            ->where( function($q) {
                    $q ->where('ctr_govdept.id', 'ctr_govdept_has_ctr_installation.ctr_govdept_id', '=', false); //join
                    $q ->where('ctr_govdept_has_ctr_installation.ctr_installation_id', 'ctr_installation.id', '=', false); //join
                } )
            ),
    

    If you want to have those fields in the data table as well you simply need to LEFT JOIN to your second table in the Editor statement outside the options instance. Then you can retrieve the field for the data table as well.

    This thread should be helpful as well. It also shows how to build your options instance yourself using embedded SQL.
    https://datatables.net/forums/discussion/comment/177739/#Comment_177739

    This thread shows an implicit INNER JOIN and how it could be written as a LEFT JOIN checking for null values
    https://datatables.net/forums/discussion/comment/132045/#Comment_132045

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    i will look at it further, but I don't think this will work. I am using an mJoin because I need one 'parent' record to display. Then, I am adding a column that is going to be a comma separated list of the ItemDisplay. I can currently build a comma separated list of the ItemID, but I need the ItemDisplay.

    ps) this is .net, not php


    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>("SystemUsers") .Field(new Field("SystemUsers.Department_Import").Xss(false)) .Field(new Field("SystemUsers.DepartmentID") .Options(new Options() .Table("udf_Lookup_FTEDeptName('" + AsOfCookie + "',1) as DeptLookup") .Value("ID") .Label("DisplayName") ) ) .Field(new Field("SystemUsers.EffectiveDate") .Validator(Validation.NotEmpty()) .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy")) ) .Field(new Field("SystemUsers.ExpireDate") .SetFormatter(Format.NullEmpty()) .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy")) ) .LeftJoin("Departments", "Departments.DepartmentID", "=", "SystemUsers.DepartmentID") .Field(new Field("Departments.DepartmentName")) .MJoin(new MJoin("SystemUsers_LineItems") .Link("SystemUsers_LineItems.SystemUserID", "SystemUsers.SystemUserID") //.LeftJoin("udf_Lookup_LineItemID('" + AsOfCookie + "') as LineItemLookup", "SystemUsers_LineItems.LineItemID", "=", "LineItemLookup.LineItemID") .Field(new Field("UserLineItemID")) .Field(new Field("LineItemID") .Options(new Options() .Table("udf_Lookup_LineItemID('" + AsOfCookie + "') as LineItemLookup") .Value("LineItemID") .Label("ItemDisplay") ) ) .Where( r => { r.Where("SystemUsers_LineItems.EffectiveDate", AsOfCookie, "<="); r.Where(s => { s.Where("SystemUsers_LineItems.ExpireDate", AsOfCookie, ">="); s.OrWhere("SystemUsers_LineItems.ExpireDate", null); } ); } ) ) .Process(request) .Data(); return Json(response); } } }
     { data: "SystemUsers_LineItems", render: "[, ].LineItemID" }
    
  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    I know I can create a view which de-normalizes the brings in the field from that third table, but I was hoping I could do this in my controller without creating a new View.
    is this possible?

    I'm sorry no - not yet. The Mjoin class doesn't have the ability to do a left join at the moment. It is something that I hope to address with an update in future, but at the moment using a VIEW is the correct way to approach this.

    Regards,
    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited May 2021

    Yes I know that my stuff is php ... but you can read it, right? I can read .NET but certainly cannot write it ...

    If you must avoid a view and you don't need updating through the MJoin you can simply use a getFormatter that executes an SQL statement to emulate the Mjoin.

    This emulates an Mjoin that returns an array called "gov". I alias the required id field to be able to pass it into the getFormatter that returns the "gov" array.

    Field::inst( 'ctr_govdept.id AS gov' )->set( false )   //return same format as an MJoin             
        ->getFormatter( function($val, $data, $opts) {
            return getFormatterGovArray($val);
        }),
    
    function getFormatterGovArray($ctrGovdeptId) {
        global $dbh;
        
        $dbh->query('SELECT DISTINCT a.name AS govName, a.regional_12 AS govRegional12
                       FROM gov a
                 INNER JOIN ctr_installation_has_gov b          ON a.id = b.gov_id
                 INNER JOIN ctr_installation c                  ON b.ctr_installation_id = c.id
                 INNER JOIN ctr_govdept_has_ctr_installation d  ON c.id = d.ctr_installation_id
                      WHERE d.ctr_govdept_id = :ctrGovdeptId
                   ORDER BY 1 ASC');
        $dbh->bind(':ctrGovdeptId', $ctrGovdeptId); 
        
        return $dbh->resultsetAssoc();     
    }
    

    You can also do this using the Editor database handler of course.

    In this particular case I retrieve two fields that I display as comma and line break separated values in two data table columns like this.

    {   data: "gov", render: "[,<br>].govName"  },
    {   data: "gov", render: "[,<br>].govRegional12"  }
    

    I personally believe that Mjoins are difficult to handle. I only use them if I must because I need to perform an Editor update through the Mjoin. Otherwise I simply use getFormatters with SQL. Much faster for me - and no restrictions.

  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    edited June 2021

    the reason I mentioned that I was using .net was because as far as I know, DataTables library doesn't have a way for .net to use a sql statement. I am still working on it and may just use a non-Datatables connection method to get the data out and use the getFormatter like you suggested.

    here is what I had been trying (I changed it to return a string just to test, will change back to IHttpActionResult I believe??):

        public class UserLineItemDescriptionsController : ApiController
        {
            [HttpGet]
            [HttpPost]
            public string UserLineItemDescriptions()
            {
                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 sqlQuery = "select * FROM SystemUsers";
                    var results = db.Sql(sqlQuery);
                    //return Json(results);
                    return results.ToString();
                }
            }
        }
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    because as far as I know, DataTables library doesn't have a way for .net to use a sql statement.

    That would very much surprise me. A custom formatter which is available in .NET as well should allow you to return anything because its code runs outside Data Tables - just like in PHP. You can use your own database handler as well, no need to use Editor's db handler. This way I don't see any reason why this should be dependent on Data Tables at all.

    https://editor.datatables.net/manual/net/formatters#Custom-formatters

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

    I suspect a VIEW with an Mjoin is going to be your most performant option here. The Mjoin will see the VIEW just like any other table, so it can read from it using the regular setup.

    The reason I think it will be more performant, is using a getFormatter with a query in it will work just fine and can allow some really neat and complex stuff such as @rf1234 has done in the past, but it also means that the query runs for every row in the table. If you are using server-side processing, that might be acceptable - an extra 10 queries per page. If you aren't, and you have thousands of rows, then that is going to really hurt performance.

    MJoin on the other hand will do a single query and then join the data to the host table in C# - trading memory for speed.

    Either way will work - it is a case of being aware of the tradeoffs.

    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    I ended up just having a view being built in SQL, mostly because I didn't want any performance issues, like @allan mentioned.

    However, now that I got this working, I realized that I want to use SearchPanes but am needing those line items that I just got grouped to be split out again, but without having extra rows show up. This is ending up a huge nightmare that I may not be able to pull off.

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

    Are you using server-side processing, or client-side? With server-side processing I'm afraid that Mjoin and SearchPanes is not currently supported.

    With client-side processing however, it should be quite possible.

    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    edited June 2021

    yes, client-side. Sorry, I was a bit cryptic. I can get searchPanes to work, but not the way I am wanting (as far as I know).

    I have parent/child tables. I was doing an mjoin so I can get comma separated list of child records on the parent DataTable. so I can see a records like this:
    John, Doe, JohnDoe@email.com, 'VPN License, Outlook License, Adobe License
    Jane, Doe, JaneDoe@email.com 'Outlook License'

    So the Parent (users) will have in one field a comma separated list of what licenses exist in the child table. This works just fine.

    But for the SearchPane, I would love for the different licenses show up as individual items. So it would show
    VPN License (1)
    Outlook License (2)
    Adobe License (1)

    But, again, I just want the one Parent record to show up in the DataTable row. I'm guessing that is not possible.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    But, again, I just want the one Parent record to show up in the DataTable row. I'm guessing that is not possible.

    It is possible but you would either need a view or do it the "unorthodox" way I suggested above. A view is clearly the better way also for performance reasons as @allan pointed out.

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    Sorry, I am probably going back and forth and confusing everyone on this...

    Yes, I did end up using a view, so now I have one row with a comma separated list of the child records (done via SQL, not an MJoin).

    But, now I want my cake and eat it too (what an odd expression). Now that I have the comma separated list with one row per parent, I would love to have each of the children show up in the SearchPane. That is the part I am thinking is not possible. I can not have both the single record in the table and the multiple records in the searchPane.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    I have always found SearchPane to be too complicated for my users. So I can't help you with that one, I am afraid. Good luck!

  • sandysandy Posts: 913Questions: 0Answers: 236
    Answer ✓

    Hi @montoyam ,

    It's not possible to show the values of child tables within the parent table's SearchPanes I'm afraid.

    You could display SearchPanes on each of the child rows and use a renderer like this example to display the results, although I don't think this is what you are looking to do...?

    Thanks,
    Sandy

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    YES....this is exactly what I am looking for!!

    thank you so much Sandy!!

Sign In or Register to comment.