filtering datatable by member_id and associated count value

cpshart

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

I have created a test case to use for my system based upon the blog post

Parent / child editing with Editor
https://datatables.net/blog/2016-03-25

Tables are

'''
sites
and
usersd
'''

I have added a member_id, widgets columns to the usersd table.
I have added widgets column to the sites table.

When there are users defined for a site, the total widgets for a site is derived by summing the widgets for all users where usersd.site = sites.id. This is working okay.

I need the system to summate the number of widgets for a given member_id = current logged in userid, so I need to add a filter or search

where usersd.member_id = $_POST['memberid'];

this needs to impact the value derived in this code below

                    {
                return data.reduce( function (accum, item) {
                  return parseFloat(accum) + parseFloat(item.widgets);
                }, 0 );         

Client Script Extract

...
<input type='hidden' id='passuserid' value='<?php echo $current_user->ID; ?>'>
    
<script type="text/javascript">
(function($) {
var editor; // use a global for the submit and return data rendering in the examples
var sites_widgets;
 
$(document).ready(function() {
    var siteEditor = new $.fn.dataTable.Editor( {
        ajax: "../../Editor-PHP-1.9.0/controllers/sitesd.php",
        table: "#sites",
        fields: [ {
                label: "Site name:",
                name: "sites.name"
            }, {
                label: "country:",
                name: "sites.country_id",
                         type: "select",
       placeholder: "Select a country"
            }, {
                label: "widgets:",
                name: "sites.widgets",
       placeholder: "Enter widgets",
                def: 0
            }                
        ]
    } );
    window.editor = siteEditor; // for demo only!
 
    var siteTable = $('#sites').DataTable( {        
        dom: "Bfrtip",
        ajax: "../../Editor-PHP-1.9.0/controllers/sitesd.php",
        columns: [
            { data: 'sites.name' },
            { data: 'country.name'},
            { data: 'usersd', render: function ( data ) {
                return data.length;
            } },
            { data: 'usersd',
              render: function (data, type, row) {
                if ( data.length === 0 ) {
                  return row.sites.widgets;
                } else
                    {
                return data.reduce( function (accum, item) {
                  return parseFloat(accum) + parseFloat(item.widgets);
                }, 0 );         
                    }
              }
            }                       
        ],
        select: {
            style: 'single'
        },
        buttons: [
            { extend: "create", editor: siteEditor },
            { extend: "edit",   editor: siteEditor },
            { extend: "remove", editor: siteEditor }
        ]
    } );
...

Server file Extract
sitesd.php

....
Editor::inst( $db, 'sites' )
    ->fields(
            Field::inst( 'sites.id' )->set( false ) 
           ,Field::inst( 'sites.name' )->validator( 'Validate::notEmpty' )
           ,Field::inst( 'sites.country_id' )
            ->options( Options::inst()
                ->table( 'country' )
                ->value( 'id' )
                ->label( array('name') )
            )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Please select country ..' )
            ) ),
        Field::inst( 'country.name' ),
        Field::inst( 'sites.widgets' )
    )

    ->join(
        Mjoin::inst( 'usersd' )
            ->link( 'sites.id', 'usersd.site' )
            ->fields(
                Field::inst( 'id' ),
                Field::inst( 'widgets' )
            )
    )
   ->leftJoin( 'country', 'country.id', '=', 'sites.country_id' )

    ->debug(true)
    ->process( $_POST )
    ->json(); 
    

<?php
>
```
Server file Extract
usersd.php
?>

$search = '%';
$memberid = $_POST['memberid'];

//$memberid = '3';

if ( ! isset($_POST['site']) || ! is_numeric($_POST['site']) ) {
echo json_encode( [ "data" => [] ] );
}
else {
Editor::inst( $db, 'usersd', 'id' )
->field(
Field::inst( 'usersd.first_name' ),
Field::inst( 'usersd.last_name' ),
Field::inst( 'usersd.phone' ),
Field::inst( 'usersd.site' )
->options( 'sitesd', 'id', 'name' )
->validator( 'Validate::dbValues' ),
Field::inst( 'sites.name' ),
Field::inst( 'usersd.widgets' )
)
->leftJoin( 'sites', 'sites.id', '=', 'usersd.site' )
->where( 'site', $_POST['site'] )
->where( function ( $q ) use ( $memberid) {
$q->where( 'usersd.member_id', $memberid);
} )
->debug(true)
->process($_POST)
->json();
}

<?php > ``` ?>

I can provide links to all files for access to my system by PM.

Many Thanks for any help.

Regards

Colin

rf1234

You are not mentioning whether you have a problem and what it is ...

But let me guess: It looks like you are trying parent child editing but you only have one client side Data Table and only one client side Editor.

That is not going to work. I don't really understand what you are trying to do. A picture of your data(base) model would be helpful to understand this better. You might not need parent child editing at all. This looks more like a use case for left joins and m joins.

cpshart

Hi rf1234

I have sent you a PM with access details to my system.

I am developing a membership website for share portfolios, so I have used the Parent Editor blog example as a template to build part of my system.

The problem I am having is that the summation of widgets in the parent table is being done for all child rows where say usersd.site = 2 (London)

So I have
usersd.member_id = 2

usersd.member_id = 3

results of my snippet

I am logged in as member_id = 3 which comprises of 3 child rows for London, widget quantities 10, 115, 20, (total, 145) so the widgets in the parent table need to reflect the total widgets for London where member_id = 3 (the logged in user).

It is currently showing a value of 261 which is total widgets for all usersd.widgets where usersd.site = 2

I need to add a filter for the usersd.member_id which will be reflected in the parent table.

Best Regards

Colin