Counting values available in another table

Capamania

How can I count values available in another table? I would like to count (or have the number) how often a value (table_01) is available in table_02 and display it in column count of table_01. I'm using DataTable and Editor.

table_01:

id | value | count
1|value_01|{count of value_01 in table_02}
2|value_02|{count of value_02 in table_02}
3|value_03|{count of value_03 in table_02}
4|value_04|{count of value_04 in table_02}
5|value_05|{count of value_05 in table_02}
6|value_06|{count of value_06 in table_02}
7|value_07|{count of value_07 in table_02}
8|value_08|{count of value_08 in table_02}
9|value_09|{count of value_09 in table_02}

table_02:

foreign_id | value
1|value_01
1|value_01
1|value_01
3|value_03
5|value_05
5|value_05
8|value_08
8|value_08
8|value_08
8|value_08
8|value_08

Many thanks

Capamania

I was looking at this thread ...
https://datatables.net/forums/discussion/28251/edit-return-the-count-of-a-column

Capamania

So with ...

"render": function ( data ) {
                    var json = table_02.ajax.json();                    
                    return json.data.length; 
                }   

I can count the records in table_02 and display it in table_01 (see below). But how can I filter or only count e.g. values with 'foreign_id' '1' ?

table_01 = $('#table_01').DataTable( {
        dom: "Blfrtip",
        ajax: {
              url: "source.php",
              type: "POST",
              data: function (d) { 
                        d.selectUserID = userID;                         
                        } },
        serverSide: true,
        processing: true,
        columns: [
            { data: "name" },
            { data: "id" },
            { data: "user_id", visible: false },
            { data: "count" ,
                "render": function ( data ) {
                    var json = table_02.ajax.json();                    
                    return json.data.length; 
                }               
            }                              
        ],
        ...

Anybody an idea?

console.log(json.data) ... returns me the whole dataset.

source_02.php (of table_02):

[Object { DT_RowId="row_1", table_01={...}, table_02={...}, more...},
Object { DT_RowId="row_47", table_01={...}, table_02={...}, more...},
...
Object { DT_RowId="row_41", table_01={...}, table_02={...}, more...},
Object { DT_RowId="row_46", table_01={...}, table_02={...}, more...}]

allan

You would probably need to include another column in your table and insert the foreign_id into that so the host table and filter on it (it can't filter on data it doesn't have).

The other option, if you don't want to filter on the length that you've cunning managed to get (nice one btw), you could use the orthogonal data options to use the foreign id as the search value.

Allan

Capamania

Thanks Allan. The filter actually does work, but I get numerous outputs in the console.log(). E.g. when I do this

{ data: "count" ,
                "render": function ( data ) {
                      var filter = table_02.columns( 2 ).search( '1' ).draw();
                      return filter.length;
                }              
            }        

and as count result in the table I get 1 instead of 3 (since it filtered down to 3 objects). And the table stays filtered.

I'm looking at https://datatables.net/manual/data/orthogonal-data right now too but I'm struggling to loop through the object array and render only the relevant objects (so I can do a .length on the filtered objects). E.g. where table_02.foreign_id = '1'. Any hint would be much appreciated. With ...

console.log( table_02.ajax.json().data[3].table_02.foreign_id);

I can access the value I'm looking for:

Result: 1

But how to loop and filter? I tried this so far which returns me the first value ...

table_01.on( 'xhr', function ( e, settings, json, xhr ) {
    
    for ( var i=0, ien=json.data[i].table_02.foreign_id ; i<ien ; i++ ) {
          
          console.log( json.data[i].table_02.foreign_id );
          
        }    
} );

allan

To check my understanding - basically you want to calculate the count from the data in the second table? Try using the filter() and count() methods for that.

Redrawing the second table every time the rendering function is called is going to kill performance.

Allan

Capamania

Hi Allan. Yes, but I want to get the count of a specific value. So for instance the count results (3, 1, 2, 5) :

foreign_id = 1: 3
foreign_id = 3: 1
foreign_id = 5: 2
foreign_id = 8: 5

And display them in table_01. So it looks like:

id | value | count
1|value_01|3
2|value_02|
3|value_03|1
4|value_04|
5|value_05|2
6|value_06|
7|value_07|
8|value_08|5
9|value_09|

I'm not sure if it matters ... I'm using ajax and serverside

allan

In that case the filter() and count() methods probably would be a good way to do it. Get the data for the column you want to match, filter out the data you don't want and count the result.

Allan

Capamania

All the attempts below failed though:

var filteredData = table_02.columns( 2 ).data().filter( function ( value, index ) {return value = 1 ? true : false;} );
console.log(filteredData.count());  
//Result: 0 

var filteredData = table_02.columns( 2 ).data().filter( function ( value, index ) {return value = 1;} );
console.log(filteredData.count());  
//Result: 0

var filteredData = table_02.columns( 2 ).data().filter( function ( value, index ) {return value = '1';} );
console.log(filteredData.count());  
//Result: 0

... and when I do .columns( 2 ).search( '1' ).draw(); I can see that the table reduces to the 3 correct rows ... but as a result I get 1 instead of 3. 

var filteredData = table_02.columns( 2 ).search( '1' ).draw();
console.log(filteredData.count());
//Result: 1

var filteredData = table_02.columns( 2 ).search( '1' ).draw();    
console.log(filteredData.length);  
//Result: 1

allan

I would have expected the first one to work. If you post a link to the page I would be happy to take a look and help you debug it.

Allan