Mjoin - Display values from second table

Johannes B.

Dear community,

I am working on a page to display cargo and the movement in a storage. Basically I used the MJoin example and modified it a
bit. But my problem should be the same with the example provide here: https://editor.datatables.net/manual/php/mjoin#Mjoin-class

To show my problem is use this as an example and modified the value of the third row in "user_permission" and added
the field "created_on". Only the data in the user table will be modified the rest will only be displayed (the database is populate
by my android application).

Table: users                 Table: user_permission
+----+------------+-------+  +----------+-----------+-----------+
| id | first_name | role  |  | staff_id | access_id |created_on
+----+------------+-------+  +----------+-----------+-----------+
| 1  | Allan      | CEO   |  | 1        | 2         |2018-02-01 10:00:00         |
| 2  | Charlie    | CTO   |  | 2        | 1         |2018-02-01 10:00:00         |
| 3  | Fiona      | COO   |  | 2        | 1         |2018-02-01 11:00:00         |
| 4  | Richard    | CFO   |  | 3        | 1         |2018-02-01 10:00:00         |
+----+------------+-------+  | 4        | 1         |2018-02-01 10:00:00         |
                             +----------+-----------+-----------+
 
Table: permission
+----+----------+
| id | name     |
+----+----------+
| 1  | Printers |
| 2  | Web-site |
| 3  | VM       |
+----+----------+

I took a look at the SQL-Query and it seems to use a syntax with DISTINCT (Select Distinct value, value2....). So i need
to get the value for "created_on" or else the two records for staff_id = 2 and acces_id = 1 will be combined. I tried something
like this below to acces the field "created_on" from the table "user_permission".

Editor::inst( $db, 'users' )
    ->field(
        Field::inst( 'users.first_name' )
    )
    ->join(
        Mjoin::inst( 'permission' )
            ->link( 'users.id', 'user_permission.user_id' )
            ->link( 'permission.id', 'user_permission.access_id' )
            ->order( 'name asc' )
            ->fields(
                Field::inst( 'id' )
                    ->options( Options::inst()
                        ->table( 'permission' )
                        ->value( 'id' )
                        ->label( 'name' ),
                Field::inst( 'created_on' )
                    ->options( Options::inst()
                        ->table( 'user_permission' )
            )
    )
    ->process($_POST)
    ->json();

I though the

->table( 'user_permission' )

is something like "the values is contained in the database user_permission". But the error I get is something
like "Unkown colmun 'permission.created_on'". Maybe the problem is cause by my old datatables Version (the one i bought
is from 12th Jun 2017)?

Could somebody give me a hint how to do this? Would really appreciative some help.

allan

You can update to the current version without any additional cost - download it from here.

I'm not sure that will resolve the issue though - when using a link table the Editor PHP libraries don't provide a way to specify any additional fields for the link table - only the final table (permission in the above case).

Do you need information from the permission table, or is the information from the user_permission enough?

Allan

Johannes B.

Dear Allan,

thank you for your reply. One (maybe stupid) question, how can I see which version of datatables I currently use?

I would be good if i could get the information from both of them. I want to show some kind of history. The result will basically be something like this:

If it is not possible to get the information from the second table it would be good if i somehow can prevent that the records in the second table are combined (when they have equal values). But in this case i would be important to keep to order of the elements.

Worst case I would have to combine the tables user_permission and permission.

allan

The debugger will tell you the client-side versions. For the PHP open the Editor.php file and look for the version string.

I see the benefit of being able to read additional information from the link table as you are, but unfortunately that is not something that the PHP libraries can currently do. I don't even think there is a workaround using a VIEW since a left join isn't enough here. Possibly a VIEW with a GROUP BY might work if you want to show multiple entries per user.

I'll have a bit more of a think about this!

Allan

Johannes B.

I am using version 1.6.3 so the update shouldn't be a problem.

I could GROUP BY users .id and then use a GROUP_CONCAT (name SEPARATOR '<br>'). This way I wouldn't be able to acces the second colmun user_permission but at least I would get the values.

I searched the forum but I think GROUP_CONTAT is not supported right now?

Thanks again for your help.

Johannes

allan

Yes, in the Editor libraries that is correct. However, if you have a VIEW which does that group by and perhaps a second view if needed for the concat, or a sub-select in the view, Editor can read the view just as if it was another database table.

Allan

Johannes B.

Hey Allan,

thank you a lot for your help an comments regarding this problem. Will searching for a way to use subselect, I think I finally found a approach with seems to work and is not limited by concanting the values.

I will join only the two tables users and users_permission in my php script. After that I will
use this https://datatables.net/examples/api/row_details.html to display the values in a child row.

function format ( d ) {
   var result = "";
   var permision = [];
   permission[1] = "Printers";
   permission[2] = "Website";
   d.user_permission.forEach(function (item,index,arr){
       result += permission[item.acces_id];
   });
   return '<table><tr><td>' + result + '</td></tr></table>';
}

The final step will be to populate the array "permission" with the values from the table
permission. But the rest is working already.

I will upload my site as soon as I complet it. The real site is (a bit) more complex and maybe it will help someone.

Johannes

fkindy01

I am facing a similar issue. Isn't there a way to resolve this with in editor instance e.g mjoin or something