Mjoin and Dependent

th3t1ck · September 2020

So I have two tables and a linked table for a multi join. I also have a select field in editor. What I am trying to do is if an incident_type is selected from one field the incident_subtype select field should only be populated with the subtypes associated with the incident_type selected.
For instance if I select "Copyright Violation" under incident_type I should only see three choices in the incident_subtype select field. "Counterfeiting", "Video Copyright", and "Software Violation".

 describe cases;
+------------------------+------------------+------+-----+---------+----------------+
| Field                  | Type             | Null | Key | Default | Extra          |
+------------------------+------------------+------+-----+---------+----------------+
| case_number            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| report_date            | date             | YES  |     | NULL    |                |
| start_date             | date             | YES  |     | NULL    |                |
| close_date             | date             | YES  |     | NULL    |                |
| incident_type          | int(10) unsigned | NO   | MUL | NULL    |                |
| incident_subtype       | int(10) unsigned | NO   | MUL | NULL    |                |
| synopsis               | text             | YES  |     | NULL    |                |
| notes                  | mediumtext       | YES  |     | NULL    |                |
+------------------------+------------------+------+-----+---------+----------------+

 describe lk_incident_types;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| incident_type | varchar(30)      | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+

 describe lk_incident_subtypes;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| incident_subtype | varchar(50)      | YES  |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

 describe lnk_incidents;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| incident_type    | int(10) unsigned | NO   | MUL | NULL    |                |
| incident_subtype | int(10) unsigned | NO   | MUL | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

select * from lnk_incidents;
+-----+---------------+------------------+
|  id | incident_type | incident_subtype |
+-----+---------------+------------------+
|   1 |             1 |                1 |
|   2 |             1 |                4 |
|   3 |             1 |                5 |
|   4 |             1 |                7 |
|   5 |             1 |               12 |
|   6 |             2 |                3 |
|   7 |             2 |               10 |
|   8 |             2 |                9 |
|   9 |             3 |                1 |
|  10 |             3 |                2 |
|  11 |             3 |                4 |
|  12 |             3 |                6 |
|  13 |             3 |               11 |
+-----+---------------+------------------+

select * from lk_incident_types;
+----+----------------------------+
| id | incident_type              |
+----+----------------------------+
|  1 | Administrative             |
|  2 | Copyright Violation        |
|  3 | Data Extraction            |
+----+----------------------------+

select * from lk_incident_subtypes;
+-----+------------------------------------------+
| id  | incident_subtype                         |
+-----+------------------------------------------+
|   1 | Cell Phone Extraction                    |
|   2 | Cell/Tablet Phone Extraction             |
|   3 | Counterfeiting                           |
|   4 | Email Pull                               |
|   5 | Falsification of Records                 |
|   6 | Infotainment Extraction                  |
|   7 | Internet Data Retrieval                  |
|   8 | N/A                                      |
|   9 | Software Violation                       |
|  10 | Video Copyright                          |
|  11 | Video Extraction                         |
|  12 | Unacceptable use of email                |
+-----+------------------------------------------+

The Mjoin...

->join(
        Mjoin::inst( 'lk_incident_types' )
            ->link( 'cases.incident_type', 'lnk_incidents.incident_type' )
            ->link( 'lk_incident_subtypes.id', 'lnk_incidents.incident_subtype' )
            ->order( 'incident_type desc' )
            ->fields(
                Field::inst( 'primary_type' )
                    ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Incident type is required.' ) ) )
                        ->options( Options::inst()
                        ->table( 'lk_incident_types' )
                        ->value( 'id' )
                        ->label( 'incident_type' )
                    ),
                Field::inst( 'incident_type' ),
            )
        )

My editor select fields...

{
    label: 'Incident Type:',
    type: 'select',
    name: 'cases.incident_type'
}, {
    label: 'Incident Subtype:',
    type: 'select',
    multiple: true,
    name: 'cases.incident_subtype'
},

I am not sure what to do for the dependent() statement. Should this call on an ajax script, iterate through the array passed back and populate incident_subtypes based on the value of incident_type? Or can I do that more simply like the following?

editor.dependent(
        'cases.incident_type',
        function(val, data, callback) {
          editor
          .field( 'cases.incident_subtype' ).set( editor.field('cases.incident_type').val() );
        },
        {
          event: 'keyup change'
        }
    );

The multi join has me a bit puzzled on how best to deal with this.

rf1234 · September 2020

Don't know what you need the Mjoin for and I doubt the Mjoin would work in any way because your link table isn't a standard link table which may only have two columns: primary key A and primary key B, not its own id field!

Let's assume the Mjoin worked then your front end field name would be "lk_incident_types[].primary_type".

The good news is that the Mjoin is completely redundant for the two select fields you mention.

What you would need to do is this:
use "dependent" to make an ajax call to retrieve the options for the second field depending on what was selected in the first field. Then use
editor.field('cases.incident_subtype').update(options from ajax call);

th3t1ck · September 2020

Thank you rtf1234. It appears that your suggestion simplifies what I'm trying to accomplish. Just what I like.
I'll give your tip a try and see where I get stuck. Then I'll come back and update this thread.

th3t1ck · September 2020

And thank you for the clarification on my link table. I appreciate the information and didn't even notice the difference in my link table and the link table in the Mjoin document.

th3t1ck · September 2020

I believe I will still need the link table to query in the ajax call as there is no correlation between the incident_type and incident_subtype in the other tables. And since one incident_type can/will have many incident_subtypes I need some kind of link to those right? That is why I thought I would need a Mjoin. I would love to simplify it if I can and If I don't need the Mjoin and can make an ajax call to a link table that would be great.

rf1234 · September 2020

Sure you need a link table but not an Mjoin in your particular use case.

When retrieving the options from the server you need to return label - value pairs.

This can be based on highly complex queries or even multiple queries. You can do anything you like. Here is an example from my own coding where I retrieve different options depending on $ctrId and the respective user. This is something you can't do with Editor! Because Editor retrieves only ONE set of options for each field, not for each field per table row.

As you can see I need two link tables here to get the result I require: ctr_has_ctr_label and user_has_available_label where the latter is even a complex view on multiple tables (it has five INNER JOINS etc.) ...

function getCtrLabelOptions($ctrId, &$dbh) {
    
    $dbh->query('SELECT DISTINCT a.label_text AS label, a.id AS value 
                   FROM ctr_label a
             INNER JOIN ctr_has_ctr_label b         ON a.id = b.ctr_label_id
                  WHERE b.ctr_id = :ctrId 
                        UNION
                SELECT DISTINCT a.label_text AS label, a.id AS value 
                   FROM ctr_label a
             INNER JOIN user_has_available_label b  ON a.id = b.ctr_label_id
                  WHERE b.user_id = :userId
               ORDER BY 1 ASC');
    $dbh->bind(':ctrId', $ctrId); 
    $dbh->bind(':userId', $_SESSION['id']); 
    
    return $dbh->resultsetAssoc();     
}

th3t1ck · September 2020

Ahhh, I think I'm starting to see what you mean. I'll give it a try and get back to this thread. Thank you for the example code. It really helps.

rf1234 · September 2020

No problem. Before I implemented this I always did an ajax reload whenever a different record was selected for editing because of Editor's limitation to retrieve just ONE set of options per field. That of course wasn't performing very well. You can see my commented original code for field "ctr_label" - I could get rid of the options instance in my MJoin (not to confuse you: I needed one, you don't ) because it was no longer needed. You can do the same with the options instance for your dependent field. You will only need the options instance for your "parent" field!

Mjoin::inst( 'ctr_label' )
    ->link( 'ctr.id', 'ctr_has_ctr_label.ctr_id' )
    ->link( 'ctr_label.id', 'ctr_has_ctr_label.ctr_label_id' )
    ->order( 'ctr_label.label_text asc' )
    ->fields(
        Field::inst( 'id' )->set( false ),
//                ->options( Options::inst()
//                    ->table('ctr_label, user_has_available_label')
//                    ->value('ctr_label.id')
//                    ->label( 'ctr_label.label_text' )
//                    ->order( 'ctr_label.label_text asc' )
//                    ->where( function($q) {
//                        $q ->where( function($r) {
//                            $r ->where('user_has_available_label.user_id', $_SESSION['id']);
//                            $r ->where('ctr_label.id', 'user_has_available_label.ctr_label_id', '=', false); //join
//                        } );
//                        //We want to be able to keep existing ctr labels even though they are not in the user's domain
//                        $ctrId = 0;
//                        if ( isset($_SESSION['ctr_id'] ) ) {
//                            $ctrId = $_SESSION['ctr_id'];
//                        }
//                        $q  ->or_where( 'ctr_label.id', 
//                            '( SELECT DISTINCT ctr_label_id  
//                                 FROM ctr_has_ctr_label
//                                WHERE ctr_id = :ctrId
//                                )', 'IN', false); 
//                        $q  ->bind( ':ctrId', $ctrId );       
//                    } )
//                ),
        Field::inst( 'label_text' )->set( false )
    )
)

th3t1ck · October 2020

Getting close. Just am not sure how the JOIN will work.

My joins in the server side file...


        ->leftJoin( 'lk_incident_types', 'lk_incident_types.id', '=', 'cases.incident_type' )
        ->leftJoin( 'lk_incident_subtypes', 'lk_incident_subtypes.id', '=', 'cases.incident_subtype' )

My dependent...

    editor.dependent(
        'cases.incident_type', 
        function(val, data, callback){
        $.ajax ( {
            url: 'ajax/incident_types_to_subtypes.php',
            data: {
                   'incident_type': editor.get( 'cases.incident_type' )
                   },
            type: 'POST',
            dataType: 'JSON',
            success: function ( JSON ) {
               callback( JSON );
              }
        });
    }) ;

I don't know how I can display the names in the subtype select instead of the index id.

I'm assuming I'll have to do another JOIN in my ajax script which currently looks like this...
```
<?php

$incident_type = $_POST['incident_type'];

include( '../db_connect.php' );

$data = array();

$query = "SELECT lnk_incidents.*
FROM lnk_incidents
LEFT JOIN lk_incident_subtypes
ON lnk_incidents.incident_subtype = lk_incident_subtypes.id
WHERE lnk_incidents.incident_type = ".$incident_type;

//print"Query=".$query."<br>\n";
$result = $dbConn->query( $query );

while ($row = mysqli_fetch_array($result)) {
$data[] = array("label"=>$row['incident_subtype'], "value"=>$row['id']);
}

$temp = array('cases.incident_subtype'=>$data);
//print_r( $data );
$json = array('options'=>$temp);
//print_r( $json );
echo json_encode($json);

<?php > ``` ?>

rf1234 · October 2020

To answer this question:

if an incident_type is selected from one field the incident_subtype select field should only be populated with the subtypes associated with the incident_type selected.

you would need this query:

SELECT DISTINCT a.incident_subtype AS label, a.id AS value
  FROM lk_incident_subtypes a
INNER JOIN lnk_incidents b ON a.id = b.incident_subtype 
WHERE b.incident_type = :selected_incident_type_id

:selected_incident_type_id being the host variable that needs to be bound with the id of the incident type selected.

Using Editor's db handler and Editor's ->raw()-method it would look like this:

function getOptions( $db, $selectedIncidentTypeId ) {
  $result = $db->raw()
    ->bind( ':selected_incident_type_id', $selectedIncidentTypeId )
    ->exec('SELECT DISTINCT a.incident_subtype AS label, a.id AS value
                      FROM lk_incident_subtypes a
             INNER JOIN lnk_incidents b ON a.id = b.incident_subtype 
                   WHERE b.incident_type = :selected_incident_type_id' );
  return $result->fetchAll(PDO::FETCH_ASSOC);
}

As I said above you need to do this:

editor.field('cases.incident_subtype').update(options from ajax call);

which you don't ... But here you do:

editor.dependent(
    'cases.incident_type',
    function(val, data, callback){
    $.ajax ( {
        url: 'ajax/incident_types_to_subtypes.php',
        data: {
               'incident_type': editor.get( 'cases.incident_type' )
               },
        type: 'POST',
        dataType: 'JSON',
        success: function ( JSON ) { 
          //assuming that JSON contains the label value pairs.
           editor.field('cases.incident_subtype').update(JSON);
          }
    });
}) ;

th3t1ck · October 2020

Ok. That helped me get closer. I don't know how to return the options back to the client script and update the subtypes.

I currently have the following, sorry if I didn't follow your directions exactly. I did try the function you provided but the below was easier for me to follow and I am getting options back thanks to the query you provided.

incident_types_to_subtypes.php

<?php
// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('log_errors', '1');
ini_set('display_errors', '1'); // display errors in browser. comment out for production.

$incident_type = $_POST['incident_type'];

include( '../db_connect.php' );
 
$data = array();

$query = "SELECT DISTINCT a.incident_subtype AS label, a.id AS value
FROM lk_incident_subtypes a
INNER JOIN lnk_incidents b ON a.id = b.incident_subtype
WHERE b.incident_type = ".$incident_type;

//print"Query=".$query."\n";
//$result = $dbConn->query( $query );
$result = mysqli_query( $dbConn, $query );

if( !$result ) {
    print "<pre class='error'>Error: No result returned." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    print"</pre>\n";
    exit;
}
//print"result=".$result."\n";
 
while ($row = mysqli_fetch_array($result)) {
//    print"label=".$row['label']."\n";
//    print"value=".$row['value']."\n";
    $data[] = array("label"=>$row['label'], "value"=>$row['value']);
}
 
$temp = array('cases.incident_subtype'=>$data);
//print_r( $temp );
$json = array('options'=>$temp);
//print_r( $json );
echo json_encode($json);
//$subtypes = json_encode($json);
//print"<pre> Subtypes";
//print_r($subtypes);
//print"</pre>";
//return( $subtypes );



<?php
>
```
My dependent, I was trying to return $subtypes but that wasn't working.
```
editor.dependent(
        'cases.incident_type',
        function(val, data, callback){
        $.ajax ( {
            url: 'ajax/incident_types_to_subtypes.php',
            data: {
                   'incident_type': editor.get( 'cases.incident_type' )
                   },
            type: 'POST',
            dataType: 'json',
            success: function ( json ) {
//  assuming that JSON contains the label value pairs.
               editor
               .field('cases.incident_subtype').update( ".$subtypes." );
              }
        });
    }) ;
```
?>


And some options returned.

{"options":{"cases.incident_subtype":[{"label":"House\r","value":"44"},{"label":"N\/A\r","value":"63"}]}}
```
At the moment I'm stuck getting the incident_subtypes to update.

rf1234 · October 2020

My dependent, I was trying to return $subtypes but that wasn't working.

I don't understand what you are doing. Sorry. What is ".$subtypes."?? You need to use what is returned to the client in the json variable. Which you don't. That cannot work.

sorry if I didn't follow your directions exactly. I did try the function you provided but the below was easier for me to follow and I am getting options back thanks to the query you provided.

Should work but if it doesn't I recommend you use my stuff because that does work ...

You need to json encode the array of label value pairs. That is really easy to do ... If I understand your code correctly the array of label value pairs is called $data.

Please just json_encode $data! That's it.

Your last line of PHP code should be this:

echo json_encode($data);

and on "success" you should do this on the client side:

success: function ( json ) {
//  assuming that JSON contains the label value pairs.
      editor
          .field('cases.incident_subtype').update( json );
  }

There might be one other cause of issues. In my first example (function "getCtrLabelOptions") I return in array of objects. You are returning an array of arrays. If you have further issues change your code to return an array of objects.

This is what I return in function "getCtrLabelOptions":

And this is how I call the function in PHP:

...
echo json_encode( getCtrLabelOptions(filter_input(INPUT_POST,'ctr_id'), $dbh) );

And for the sake of completeness the ajax call:

$.ajax({
    type: "POST",
    url: 'actions.php?action=getCtrLabelOptions',
    data: {
        ctr_id: selected.data().ctr.id
    },
    dataType: "json",
    success: function (data) {   
        editor
          .field('ctr_label[].id').update( data );
    }
});

th3t1ck · October 2020

I will try that and get back to you. It might be a day or two because I like to try and find what the problem is. That seems to be my mis-understanding on what I'm doing.

rf1234 · October 2020

There might be one other cause of issues. In my first example (function "getCtrLabelOptions") I return in array of objects. You are returning an array of arrays. If you have further issues change your code to return an array of objects.

Pardon my bullshit ... I also return an array of arrays from PHP; it is the json encoding that converts it to an array of objects. So your code to create $data should be ok.

This code here adds complexity and I don't know what the benefit could be to be honest:

$temp = array('cases.incident_subtype'=>$data);
$json = array('options'=>$temp);
echo json_encode($json);

If you wanted to disentangle this with Javascript you would need to do something like this:

success: function ( json ) {
//  assuming that JSON contains the label value pairs.
      editor
          .field('cases.incident_subtype').update( json.options["cases.incident_subtype"] );
  }

Ahh, I see you want to kind of imitate the structure Editor returns! Ok, if you like ...

Here is an example where I need to disentangle it and save it in a global variable. Looks like my suggestion above should work, I guess.

.on('xhr', function( e, settings, json, xhr ) {
    if ( json != null ) {
        if ( typeof json.options !== 'undefined' ) {
            serverManualCreditorOptions = json.options["contract.gov_manual_creditor_id"];
        }
    }          
});

th3t1ck · October 2020

Thank you for your time and patience. It is working perfectly now. Once I changed this

echo json_encode($data);

and this

.field('cases.incident_subtype').update( json );

It started working.

Mjoin and Dependent

Mjoin and Dependent

Answers

Howdy, Stranger!

Categories

DataTables

Mjoin and Dependent

Mjoin and Dependent

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables