Adding a few columns for the search functions from a mjoin
Adding a few columns for the search functions from a mjoin
arie0512
Posts: 15Questions: 4Answers: 0
Hello,
I have a mjoin which works great but I want to extend the mjoin with other fields.
Here is what I have:
Client
Clientnumber | Name | Gender | addressnumber
1 | Joe | M | 1
2 | Jack | M | 2
3 | Sylvia | F | 2
4 | Robert | M | 3
5 | Sarah | F | 3
6 | Stephan | M | 3
Address
addressnumber | Street | Zipcode | City
1 | Street 1 | 1111AA | New York
2 | Street 2 | 2222BB | Paris
3 | Street 3 | 3333CC | London
Clientnumber | Email
Here is the Ajax call:
columns: [
{ data: 'clienten.clientnummer' },
{ data: 'clienten.geslacht' },
{ data: 'clienten.voorletters' },
{ data: 'clienten.tussenvoegsel' },
{ data: 'clienten.achternaam' },
{ data: 'adressen.straat' },
{ data: 'adressen.huisnummer' },
{ data: 'adressen.toevoeging' },
{ data: 'adressen.postcode' },
{ data: 'adressen.woonplaats' },
{ data: 'clienten.geboortedatum',
render: function (data, type, row) {
return moment(new Date(data).toString()).format('DD-MM-YYYY');
}
},
{ data: 'email[].email', render: DataTable.render.ellipsis(20,true) },
{ data: 'clienten.unieke_url' }
],
createdRow: function( row, data, dataIndex ) {
$(row).attr('data-href', 'clienten-details.php?id=' + data.clienten.unieke_url);
},
columnDefs: [
{ targets: [0,6], className: 'dt-body-left'},
{ targets: [0], render: DataTable.render.number( '.', ',', 0 ) },
{ visible: false, targets: [12] },
{ width: '100px', targets: [8] },
{ targets: [4], render: DataTable.render.ellipsis(25,true) },
{ targets: [5], render: DataTable.render.ellipsis(25,true) },
{ targets: [9], render: DataTable.render.ellipsis(25,true) },
{ targets: [11],render: DataTable.render.ellipsis(20,true) },
{ searchable: false, targets: [1,3,6,7,12] }
],
And here is the PHP:
Editor::inst($db, 'clienten', 'clientnummer')
->field(
Field::inst('clienten.clientnummer'),
Field::inst('clienten.geslacht'),
Field::inst('clienten.voorletters'),
Field::inst('clienten.tussenvoegsel'),
Field::inst('clienten.achternaam'),
Field::inst('adressen.straat'),
Field::inst('adressen.huisnummer'),
Field::inst('adressen.toevoeging'),
Field::inst('adressen.postcode'),
Field::inst('adressen.woonplaats'),
Field::inst('clienten.geboortedatum'),
Field::inst('clienten.unieke_url'),
Field::inst('clienten.adresnummer')
->options(
Options::inst()
->table('adressen')
->value('adresnummer')
->label('woonplaats')
)
->validator(Validate::required())
->validator(Validate::dbValues())
)
->leftJoin('adressen', 'adressen.adresnummer', '=', 'clienten.adresnummer')
->join(
Mjoin::inst( 'email' )
->link( 'clienten.clientnummer', 'email.clientnummer' )
->order( 'id asc' )
->fields(
Field::inst( 'clientnummer' )
->options( Options::inst()
->table( 'email' )
->value( 'clientnummer' )
->label( 'email' )
),
Field::inst( 'email' )
)
)
->process($_POST)
->json();
I get the table good filled with the column Email filled with the mjoin from the table email.
But the table email is filled with these records:
Clientnumber | Email
1 | email11@gmail.com
1 | email12@gmail.com
1 | email13@gmail.com
2 | email21@gmail.com
2 | email22@gmail.com
3 | email31@gmail.com
I get in my table all the first email adresses of each client but I really want to add the other email adresses of each client to the search function.
So I don't want to show them in my table but only if someone search for email12@gmail.com than this client should be found in the search command.
Any idea how to achieve this?
Best regards,
Arie
Answers
Hi Arie,
Are you able to link to an example showing the issue, I'm having a hard time picturing exactly what is needed here - sorry.
Are you using server-side processing here? If so, Mjoin doesn't support search I'm afraid.
Thanks,
Allan
Hi Alan,
Do you know if there is a tool where you can make a test case with a mysql table? Because that would help to make a good test case but I can't find anything fot testing with a mysql table, only for HTLM, CSS, Javascript.
Best regards,
Arie
Hi Arie,
I'm afraid I'm not sure that there is. Is your actual page available on the web somewhere? You could PM me login details possibly?
Allan
Hi Allan,
Never mind, I figured it out and it work now great!
Tnx fot getting back to me.
Best regards,
Arie
Hi Arie,
Superb, great to hear that you've got it working now.
Allan