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

Email

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