Issue with Mjoin
Issue with Mjoin
Ellisphere_user1
Posts: 16Questions: 1Answers: 0
Hi,
Actually, I use the join because I want to have in my datatable all columns of the user table and an additionally column role (which show all the role gave to each user). The objective is to manage user and also give/remove one or many role.
Here is tables that I use :
user :
+--------+-----------+--------------+---------+---------+
| userid | username | fullname | comment | enabled |
+========+===========+==============+=========+=========+
| 1 | da_john | John Lewis | null | true |
+--------+-----------+--------------+---------+---------+
| 2 | da_victor | Victor Wamby | null | true |
+--------+-----------+--------------+---------+---------+
role :
+--------+--------+---------+
| roleid | name | comment |
+========+========+=========+
| 2 | Admin | null |
+--------+--------+---------+
| 3 | Writer | null |
+--------+--------+---------+
role_attribution :
+--------+--------+
| userid | roleid |
+========+========+
| 1 | 2 |
+--------+--------+
| 2 | 2 |
+--------+--------+
But I here is the error that I canno't resolve : DataTables warning: table id=datatable_user - Join was performed on the field 'user' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.
Here is my php script :
Editor::inst($db, 'backup_referential.user', 'backup_referential.user.userid')
->field(
Field::inst('backup_referential.user.userid')
->get($_SESSION['read'])
->set(false),
Field::inst('backup_referential.user.username')
->get($_SESSION['read'])
->set($_SESSION['editing_user'])
->validator(Validate::notEmpty(ValidateOptions::inst()))
->validator(Validate::maxLen(50))
->validator(Validate::noTags(ValidateOptions::inst()))
->validator(Validate::xss(ValidateOptions::inst())),
Field::inst('backup_referential.user.fullname')
->get($_SESSION['read'])
->set($_SESSION['editing_user'])
->validator(Validate::notEmpty(ValidateOptions::inst()))
->validator(Validate::maxLen(50))
->validator(Validate::noTags(ValidateOptions::inst()))
->validator(Validate::xss(ValidateOptions::inst())),
Field::inst('backup_referential.user.comment')
->get($_SESSION['read'])
->set($_SESSION['editing_user'])
->setFormatter(Format::ifEmpty(null))
->validator(Validate::noTags(ValidateOptions::inst()))
->validator(Validate::xss(ValidateOptions::inst())),
Field::inst('backup_referential.user.enabled')
->get($_SESSION['read'])
->set($_SESSION['editing_user'])
->setFormatter(function ($val, $data, $opts) {
return !$val ? 0 : 1;
})
)
->join(
Mjoin::inst('backup_referential.role')
->link('backup_referential.user.userid', 'backup_referential.role_attribution.userid')
->link('backup_referential.role.roleid', 'backup_referential.role_attribution.roleid')
->order('backup_referential.role.name asc')
->fields(
Field::inst('roleid')
->validator(Validate::required())
->options('backup_referential.role', 'roleid', 'name'),
Field::inst('name')
)
)
->debug(true)
->process($_POST)
->json();
And here the javascript :
var editor = new DataTable.Editor({
ajax: '../../controllers/editor/editor_user.php',
fields: [
{
label: 'Username:',
name: 'backup_referential.user.username'
},
{
label: 'Fullname:',
name: 'backup_referential.user.fullname'
},
{
label: 'Commentaires:',
name: 'backup_referential.user.comment'
},
{
label: 'Roles:',
name: 'backup_referential.role[].id',
type: 'select',
multiple: true
},
{
label: 'Enabled:',
name: 'backup_referential.user.enabled',
type: 'checkbox',
separator: '|',
options: [{ label: '', value: 1 }]
}
],
table: '#datatable_user'
});
var table = $("#datatable_user").DataTable({
language: {
url: "assets/language/fr-FR.json"
},
ajax: {
url: '../../controllers/editor/editor_user.php',
type: 'POST'
},
pageLength: 5,
lengthMenu: [[5, 10, 20, 50, -1], [5, 10, 20, 50, "Tous"]],
scrollCollapse: true,
scrollY: 500,
scrollX: true,
autoWidth: false,
colReorder: true,
select: {
style: 'os',
selector: 'td:first-child'
},
fixedColumns: {
left: 2
},
dom: "<'row'<'col-sm-12 col-md-8'lB><'col-sm-12 col-md-4'f>>" +
"<'row'<'col-sm-12'tr>>" +
"<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
buttons: [
{ extend: 'create', editor: editor, className: 'bg-navy' },
{ extend: 'edit', editor: editor, className: 'bg-navy' },
{ extend: 'remove', editor: editor, className: 'bg-navy' },
{ extend: 'colvis', className: 'bg-navy' },
{ extend: "searchBuilder", className: 'bg-navy' }
],
columns: [
{ data: 'backup_referential.user.userid' },
{ data: 'backup_referential.user.username' },
{ data: 'backup_referential.user.fullname' },
{ data: 'backup_referential.user.comment' },
{ data: 'backup_referential.role', render: '[, ].name' },
{
data: 'backup_referential.user.enabled',
render: function (data, type, row) {
if (type === 'display') {
return '<input type="checkbox" class="editor-active">';
}
return data;
},
className: 'dt-body-center'
}
],
rowCallback: function (row, data) {
// Set the checked state of the checkbox in the table
$('input.editor-active', row).prop('checked', data.active == 1);
}
});
I've tried a lot of solution give in forum for similar post but it doesn't work. If someone have any idea about my issue, I'll take it.
Thanks,
Matéo
This question has accepted answers - jump to:
Answers
Hi Matéo,
I suspect is the the schema scope that is causing the issue. Try:
I don't think you need the schema scope due to the parent defining it.
Allan
If that doesn't resolve it, could you show me the JSON response from the server when loading the data please?
Allan
Thanks for your response. I tried your change but doesn't work :
Here is the JSON response (I don't know why I've not all the error) :
Matéo
I don'y know why, but the SQL request is weird. The FROM have
backup_referential.usertwo times. It's here by the way where the error.The JOIN are also not good yet I think the PHP script is correct.
Matéo
I was wondering, is it possible that there is a problem with the function itself (Mjoin) ? I reviewed my PHP script and it once again seems correct to me.
I'm wondering this because the duplicate inclusion of
backup_referential.userin my FORM is surprising and nothing in the script seems to cause itI suspect it probably is. Apologies for the delayed reply to this thread - I'll need to try and recreate the issue here will update you when I've done that (probably tomorrow now).
Allan
Hi allan,
I would like to ask you again to see if you had time to look on your side and possibly spot any anomalies. Thanks in advance.
Matéo
Hi Matéo,
Apologies for loosing sight of this issue! It appears to be related to the use of the database name as part of the parameter names - and I've been experimenting a bit, but it isn't 100% clear to me yet what the correct why to address this is going to be (specifically the issue is related to how we alias column names, I think that might need to be reworked completely).
That said, I think we can get you up and running without too much difficulty - what we need is for you to be able to drop the
backup_referential.database qualifier. That can be done by using:And then remove the
backup_referential.qualifier from your table name and field names. Since you aren't doing any cross database joins, hopefully that should get reading data okay:Let me know how you get on with that.
Thanks,
Allan
No problem for the late response. Thanks for your suggest, I've tried with PostegreSQL syntax :
But it doesn't work, the name of the table is put two times again. It's surprising, this hasn't already happened to anyone using mjoin ?
Here is the JSON response :
More understandable in sql language :
Hi,
I tried several things including renaming my table for other reasons. With all these changes, my table is displayed and I no longer have any errors. My only problem is for editing the data. I cannot add, modify or delete data.
Here is the error show in the web console :
Uncaught Unable to automatically determine field from source. Please specify the field name. For more information, please refer to https://datatables.net/tn/11However, I think I have implemented my fields well. Maybe still a problem with the join?
Here is the PHP script :
And my javascript :
If anyone has any idea, I'm all ears. Thanks in advance
Matéo
I think something is wrong here:
"laps_role" is not a field, I guess. It is a table.
My Mjoins - particularly the options instances - look quite different from yours - but I don't know whether that is important ...
Here is one:
Here are examples from the docs. Take a look at the server script and the options instances:
https://editor.datatables.net/examples/simple/join
https://editor.datatables.net/examples/advanced/joinArray.html
Please also take a look at this. Scroll down to "Options".
https://editor.datatables.net/manual/php/joins#options
Some more things on options:
The following options are (almost) equivalent. The first are retrieved using SQL with Editor's db-handler using Editor's "raw()" method, the second ones are retrieved using Editor's options class.
I use the first because this is more flexible. The second one is a little less flexible. You can't use inner joins for example and you can't return additional values as I do in the first one (field "report_type" which I need to generate <optgroup> tags at the client).
Hi,
Tanks for your answers rf1234. I redid my JOIN construction like you. I also fixed some other issues on my side.
First of all, I took the liberty of renaming editor and table by editor_user and table_user. Return it to the initial state to sort out a lot of problems.
I also forgot to remove
backup_referentialin my fields in the editor declaration in the javascript.Now, the roles are indeed displayed in the table but it is at the editing level that there is a problem. When I want to edit inline for role column (only for this column), I get this error message in the javascript console:
Uncaught Unable to automatically determine field from source. Please specify the field name. For more information, please refer to https://datatables.net/tn/11I obviously checked the datatables error 11 but I can't seem to locate the problem for my code. I also have a problem with editing, select2 is displayed well but without data. When I validate the modification or even an addition, it does not work because the role field is obviously empty.
These issues are most likely related, so I was wondering if anyone had any idea why the data is not showing in the editor and I am having an inability to edit inline as well.
Here is the javascript with the modifications I talked about previously :
And maybe my php script :
Thanks in advance
Matéo
I guess the "editField" is called 'laps_role[].id' and not 'laps_role.id'
I would give that a try.
Thank you very mutch, that removed that error, I should have thought of that. I now have my select2 displaying correctly. But as for editing, I still have the problem that there is no data in the drop-down list which prevents editing. I will continue to look (especially on the JOIN side) on my side but if you have an idea I am interested
If ever, here is the errors returned:
Fatal error: Uncaught TypeError: count(): Argument #1 ($value) must be of type Countable|array, string given in C:\wamp64\www\LAPS\vendor\datatables.net\editor-php\Editor\Join.php on line 796TypeError: count(): Argument #1 ($value) must be of type Countable|array, string given in C:\wamp64\www\LAPS\vendor\datatables.net\editor-php\Editor\Join.php on line 796And the function where the errors occur (in raw) :
Locate in Editor.php:857 :
Matéo
To be honest I have no idea and I am using a different Editor version. Since you have no special messages in your "notEmpty" validators they look a bit odd.
Could you try this please
Alternatively you can try this:
Thank for your time, unfortunately it doesn't work.
This is surprising because my JOIN sort of works. It displays the role of the user but it is at the time of editing, it is as if select2 had no options available. However, I think I have done everything possible to allow it.
Sorry I am out; I personally use those legacy validators that work fine for me. I guess you will need to make a test case.
Thanks anyway, I'll keep looking on my own. And if I don't find it, I'll stay in the hope of finding someone who has a solution.
Hi folks,
Sorry I've been out of the loop with this one a little. Mjoin validators are a little different from the regular field ones. Have a look at this part of the docs specifically. If you have a required field, then use the
mjoinMinCountvalidator (i.e. the number selected must be at least 1). Its just a slightly different way of looking at a "required" field (since it is an array of values).In terms of the values, If your field is called
name: 'laps_role[].roleid'on the client-side, then it should populate with options (it looks you might have it aslaps_role[].id- I think that needs to be updated, and theeditFieldvalue for it changes to match).Allan
Hi allan,
This is called a careless error, I was so focused on something else that I didn't pay attention, thank you very much the list of options displays fine now.
But I still have an error for editing but it is with a function in the Join.php file:
Any idea where this could come from ? I have to declare somewhere that laps_role.roleid is necessarily a "countable" ?
The error appears regardless of whether the inclusion of a validator is there :
With the inclusion of validator, the error is the same but located in the Validate.php :
That error suggests that it isn't an array of data being sent to the server. Could you pop open your browser's "Inspect", make the request that causes the error and then find that in the "Network" inspector panel and show me the parameters that are being sent to the server please?
Thanks,
Allan
Here are the parameters sent to the server when modifying the fields of a row :
I also add a data row sent during initialization to be displayed in the table :
Thanks,
Matéo
There is something goting wrong with the data being submitted. It should contain a
-many-countparameter forlaps_role- to tell Editor's server-side components that it is submitting for an Mjoin.Can you show me the current field options you are using for
laps_role[].roleidplease? I'm wondering if there is aseparatoroption or something (there isn't above, but maybe it has changed).If that isn't the issue, I'd need a link to the page to be able to trace it through and see what is going wrong.
Allan
Indeed yes, nothing has changed compared to above :
How can I send you a link of the page ? The website is only accessible locally.
Matéo
You can't then. At least not without port forwarding and all that stuff.
Can you show me the current Javascript for the field please?
Allan
We will actually avoid port forwarding. Here is my javascript :
Thanks again for your time.
Matéo
That's the issue. Select2 hasn't been configured for multiple selection there. You could try it without Select2:
or configure Select2 for multiple selection:
Thanks,
Allan
Hi allan,
Effectively it was the problem, everything works perfectly. Tanks you very mutch for your time and your patience.
Have a great day.
Matéo