Putting together different data from different tables

andreavellone

That the situation:
I have three different table, i must arrange an array with data from this table...

Here a part of Json to better explain

"data": [
    {
      "DT_RowId": "row_150",
      "ol": {
        "id": "150",
        "ol": "0",
        "fase": "8",
        "centrale": "roma",
        "Data_Inizio": "2017-11-17",
        "Data_Fine": "2017-11-17",
        "cliente_id": "3",
        "fatturato": "1",
        "Materiali_id": "",
        "costi_materiali": null,
        "impiegati_id": null,
        "Time_Stamp": "2017-12-06 10:49:51"
      },
      "clienti": {
        "nome": "FastWeb"
      },
      "materiali": [
        {
          "id": "7",
          "codice": "F-LCLC01-DX-B"
        }
      ],
      "movimenti": [
        {
          "id": "355",
          "quantita": "1",
          "materiale_id": "7"
        }

The last part: I have an array in "movimenti" and another in materiali, i "movimenti" i need to use "codice" and not "materiale_id.

Here the queries:

->join(
        Mjoin::inst( 'materiali' )
            ->link( 'ol.id', 'vardisp.olid' )
            ->link( 'materiali.id', 'vardisp.matid' )
            ->fields(
                Field::inst( 'id' )
                    ->validator( 'Validate::required' )
                    ->options( Options::inst()
                        ->table( 'materiali' )
                        ->value( 'id' )
                        ->label( 'codice' )
                    ),
                    
                Field::inst( 'codice' )
                
            )
    )
    
    
  
  

   ->join(
        Mjoin::inst( 'movimenti' )
           
            ->link( 'movimenti.ad_ol_id', 'ol.id' )
            
            ->fields(
                Field::inst( 'id' )
                    ->validator( 'Validate::required' )
                    ->options( Options::inst()
                        ->table( 'movimenti' )
                        ->value( 'id' )
                        ->label( array ('quantita', 'materiale_id' ))
                       
                    ),
              
                Field::inst( 'quantita' ),
                Field::inst( 'materiale_id' )
      
               
            )
    )
    

and here how I use the data on JS

    {data: "movimenti",
render: function ( data, type, full ) {
  return $.map( data, function ( d, i ) {
    return d.materiale_id +' '+ d.quantita;
  } ).join( ', ' );
}}

any ideas?
tx a lot

allan

I don't quite understand the end goal I'm afraid. Could you show me what you want the data from the JSON above to look like on output?

Thanks,
Allan

andreavellone

Sorry Allan, i'd like to have a json like this:

"materiali": [
        {
          "id": "7",
          "codice": "F-LCLC01-DX-B"
        }
      ],
      "movimenti": [
        {
          "id": "355",
          "quantita": "1",
          "materiale_id": "7"
          "codice": "F-LCLC01-DX-B"
        }

I have three table:
Materiali: id, codice
Movimenti: id, quantita, materiale_id (this is the link with materiali.id)

I'm working on table OL
Editor::inst( $db, 'ol' )

this is a link
http://temisgest.it/tg/files/inline/temis-ol.php

allan

Ah I see! And the movimenti table presumably doesn't have a codice column - you want to join to it?

I fear that at the moment the Editor libraries don't currently support a leftJoin inside an Mjoin, which is what you are looking for.

What you would need to do is something like:

data: 'movimenti',
render: function ( data, type, row ) {
  var out = [];
  for ( var i=0, ien=data.length ; i<ien ; i++ ) {
    var materiale_id = data[i].materiale_id;

    for ( var j=0, jen=row.materiali.length ; i<ien ; i++ ) {
      if ( materiale_id  === row.materiali[i].id ) {
        out.push( row.materiali[i].codice );
      }
  }

  return out.join( ', ' );
}

Its a bit of a pain I know! Left joins inside an MJoin is something that is planned.

Allan

andreavellone

mmm something don't run in the right way.
I have an error type 4 on row 1 column 8
This the debug http://debug.datatables.net/atahim

I think he can't define materiali

->leftJoin('clienti', 'clienti.id', '=' , 'ol.cliente_id') 
    ->join(
        Mjoin::inst( 'materiali' )
            ->link( 'ol.id', 'vardisp.olid' )
            ->link( 'materiali.id', 'vardisp.matid' )
            ->fields(
                Field::inst( 'id' )
                    ->validator( 'Validate::required' )
                    ->options( Options::inst()
                        ->table( 'materiali' )
                        ->value( 'id' )
                        ->label( array ('codice', 'id' ))
                    ),
                    
                Field::inst( 'codice' ),
                Field::inst( 'id' )
                
            )
    )

andreavellone

I got it, there was an "i" instead of a "j"

tx a lot

andreavellone

but now there's another problem. I want also near codice an output with quantità

i tried something like this

out.push( row.materiali[j].codice +' '+data[i].quantita );

but i Obtain "undefined"...

any idea?

andreavellone

Now i solved, there was an error on server-side page.
Tx a lot

allan

Thanks for posting back - good to hear you've got it working now!

Allan