left join within mjoin - .net project

montoyam

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

rf1234

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

montoyam

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" }

allan

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

rf1234

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.

montoyam

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();
            }
        }
    }

rf1234

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

allan

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

montoyam

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.

allan

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

montoyam

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.

rf1234

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.

montoyam

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.

rf1234

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!

sandy

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

montoyam

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

thank you so much Sandy!!