External input to search DataTable

cbasmadjian

Hello,

I have been searching these forums to try to find something close to what I need, but have been unsuccessful. I think my implementation is wrong as the results I am getting back from the Network Tab is "Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 72 bytes) in..."

My table which the Editor looks into averages around 130k of rows. So I have been using "serverSide" true, and that works fine, until...

When I want to integrate external buttons, text boxes, sliders, etc that are not part of DataTables per se, but I want to send "Where clauses" to search the DataTables itself. This is where I am uncertain on to apply the Where Clause documentation into my implementation.

Client Side code

var diamondsTable;
    $(document).ready(function() {
        diamondsTable = $('#diamonds').DataTable( {
            dom: "Bfrtip",
                        serverSide: true,
            ajax: {
                url: "../../wp-content/mu-plugins/invDiamonds.php",
                dataType: "json",
                type: "POST",
                data:{
                    "diamondsInventory":"true"
                }
            },
            columns: [
                { data: "shape", title:"Shape", name:"shape" },
                { data: "weight", title:"Carat", name:"weight" },
                { data: "cutgrade", title:"Cut", name:"cutgrade" },
                { data: "clarity", title:"Clarity", name:"clarity" },
                { data: "color", title:"Color", name:"color" },
                { data: "lab", title:"Lab", name:"lab" },
                { data: "cert", title:"Certificate", name:"cert" },
                { data: "totalprice", title: "Price", name:"totalprice"}
            ],
            select: { style: 'single'}
        } );
        $('.shape').click( function() {
            var shapes = ColorShapeSelection();
            $.post( "../../wp-content/mu-plugins/invDiamonds.php", { diamondsInventory: "true", shapeResults: shapes } );
                        diamondsTable.draw();
        } );
    } );

Looking at the Client Side code I am using the jQuery button click event and sending an ajax post to the script asking it to update the DataTables with filtered results. That's the theory whether I implemented this correctly I am not sure. This may be the source of the problem. Maybe...

Server Side

    $salonList = isset($_POST['diamondsInventory']) ? $_POST['diamondsInventory'] : '';
    $shapes = isset($_POST['shapeResults']) ? $_POST['shapeResults'] : NULL;
    
    if($salonList == "true"){       
        Editor::inst( $db, 'wp_jj6sh9h88k_diamonds' )
        ->fields(
            Field::inst( 'shape' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'weight' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'cutgrade' ),
            Field::inst( 'clarity' ),
            Field::inst( 'color' ),
            Field::inst( 'lab' ),
            Field::inst( 'cert' ),
            Field::inst( 'totalprice' )
        )
        ->process( $_POST )
        ->json();
    }
    else if(!is_null($shapes)){
        Editor::inst( $db, 'wp_jj6sh9h88k_diamonds' )
        ->fields(
            Field::inst( 'shape' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'weight' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'cutgrade' ),
            Field::inst( 'clarity' ),
            Field::inst( 'color' ),
            Field::inst( 'lab' ),
            Field::inst( 'cert' ),
            Field::inst( 'totalprice' )
        )
        ->where( function ( $q ){
            $q
                ->where( 'shape', $shapes[0])
                ->or_where( function ( $r ) {
                    for( $i = 1; $i<count($shapes); $i++ ) {
                        $r->or_where( 'shape', $shapes[$i] );
                     }
                } );
        })
        ->process( $_POST )
        ->json();
    }

The first IF statement is for an intial load with no filters at all. Just return everything you got, basically. The second IF statement is when the user clicks on a button to filter within the remaining results.

Maybe I am doing this wrong, because without the filter I am not receiving a memory error, but with the filter it is. What am I doing wrong? If there was documentation that I missed that discusses this please link me.

Thank you!

allan

This is PHP's somewhat crazy anonymous function variable syntax rather than an issue with the libraries I think. Change:

->where( function ( $q ){

To be:

   ->where( function ( $q ) use ( $shapes) {

You'll need to add a similar use statement to the inner function for the or_where loop.

The PHP documentation has more information about it. Its particularly frustrating when you are also using Javascript and don't need to think about that kind of thing.

I'll add a note about this to the documentation for the PHP libraries as you aren't the first to trip over this.

Allan

cbasmadjian

$salonList = isset($_POST['diamondsInventory']) ? $_POST['diamondsInventory'] : '';
    $shapes = isset($_POST['shapeResults']) ? $_POST['shapeResults'] : NULL;
    
    if($salonList == "true"){       
        Editor::inst( $db, 'wp_jj6sh9h88k_diamonds' )
        ->fields(
            Field::inst( 'shape' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'weight' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'cutgrade' ),
            Field::inst( 'clarity' ),
            Field::inst( 'color' ),
            Field::inst( 'lab' ),
            Field::inst( 'cert' ),
            Field::inst( 'totalprice' )
        )
        ->where( function ( $q ) use ($shapes){
            if(!is_null($shapes)){
                if(count($shapes) > 0 ){
                    $q->where( 'shape', 'Round', '!='); //$shapes[0]);
                        //->or_where( function ( $r ) use ( $shapes) {
                        //  for( $i = 1; $i<count($shapes); $i++ ) {
                        //      $r->or_where( 'shape', $shapes[$i] );
                        //   }
                        //} );
                }
            }
        })
        ->process( $_POST )
        ->json();
    }

I think this is very close. I have tried hard coding the where clause just to see if it works and it does.

However, the above code works on the first click after the second click of clearing the where clause, i.e. being empty I still get the Fatal error.

I really don't know what's the problem. No matter if separate the IF statements or not its still the same error. I don't know how to tell the Editor that on initial load let me see everything and when the user wants to filter results go ahead and do that.

What do I have to do to fix this fatal error?

allan

Could you show me what the error message is please? Could you also add ->debug( true ) immediately before the ->process() call so I can take a look at the SQL (it will be available in the JSON returned from the server).

Thanks,
Allan

cbasmadjian

I had to upgrade my Editor to get the "Debug" function to work. Once I did that I ran the tests you wanted.

Error Message I keep receiving.
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 72 bytes) in /../DataTables-Editor/Database/Driver/Mysql/Result.php on line 58

Network Tab when it does work.
[{,…}]
0
:
{,…}
bindings
:
[{name: ":where_0", value: "Emerald", type: null}]
0
:
{name: ":where_0", value: "Emerald", type: null}
query
:
"SELECT id as 'id', shape as 'shape', weight as 'weight', cutgrade as 'cutgrade', clarity as 'clarity', color as 'color', lab as 'lab', cert as 'cert', totalprice as 'totalprice' FROM wp_jj6sh9h88k_diamonds WHERE shape = :where_0 "

I ran this SQL query against my DB and I did get results. However, my DataTable does not update.

My console does show the button that I am clicking. But nothing is updating.

What am I missing?

https://diamondsbysc.com/diamond/diamonds-product-search/

Just in case I have updated my where function to this, the rest of the script above is still the same.

->where( function ( $q ) use ($shapes){
            if(!is_null($shapes)){
                if(count($shapes) > 0 ){
                    $q->where( 'shape', $shapes[0]);
                        //->or_where( function ( $r ) use ( $shapes) {
                        //  for( $i = 1; $i<count($shapes); $i++ ) {
                        //      $r->or_where( 'shape', $shapes[$i] );
                        //   }
                        //} );
                }
            }
        })

Help me out here Allan. Where am I going wrong with this?

cbasmadjian

I have updated my where clause to show all if nothing is selected. I thought the Fatal error was coming from an empty selection.

if(!is_null($shapes)){
                if(count($shapes) > 0 ){
                    $q->where( 'shape', $shapes[0]);
                        //->or_where( function ( $r ) use ( $shapes) {
                        //  for( $i = 1; $i<count($shapes); $i++ ) {
                        //      $r->or_where( 'shape', $shapes[$i] );
                        //   }
                        //} );
                } else{
                    $q->where( 'shape', "", "!=");
                }
            } 
            else {
                    $q->where( 'shape', "", "!=");
            }

When "Round" is selected I get...

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 72 bytes) in /.../DataTables-Editor/Ext/Ext.php on line 181

When "Pear" is selected I get...

{,…}
bindings
:
[{name: ":where_0", value: "Pear", type: null}]
0
:
{name: ":where_0", value: "Pear", type: null}
name
:
":where_0"
type
:
null
value
:
"Pear"
query
:
"SELECT  `id` as 'id', `shape` as 'shape', `weight` as 'weight', `cutgrade` as 'cutgrade', `clarity` as 'clarity', `color` as 'color', `lab` as 'lab', `cert` as 'cert', `totalprice` as 'totalprice' FROM  `wp_jj6sh9h88k_diamonds` WHERE `shape` = :where_0 "

Even though I see a proper SQL Select statement the DataTables doesn't update. It stays the same. I have checked the Console to see if any JavaScript errors were being triggered and only the debug statements of my selections.

I am so confused what am I doing wrong here?

cbasmadjian

How I configured the column called shape...

shape   varchar(50) utf8_unicode_ci

Is it the fact that it's a varchar that the DataTables doesn't understand how to compare in the Where Clause? I am grasping at straws here trying to understand how to get this to work.

allan

If you are still getting out of memory errors, it suggests that server-side processing is not being used. Are you POSTing the DataTables parameters to the server (example)? If not, start there.

If you are, can you give me a link to the page please?

Allan

cbasmadjian

Hi Allan,

In one of my previous responses I have linked you to the site. I will be clearer next time.

Link to site
https://diamondsbysc.com/diamond/diamonds-product-search/

Client site post
$('.shape').click( function() {
var shapes = ColorShapeSelection();
$.post( "../../wp-content/mu-plugins/invDiamonds.php", { diamondsInventory: "true", shapeResults: shapes } );

    } );

Apologies as I'm doing this over the phone and don't have access to the markups.

Do we need to do another join.me session to get this fixed/configured again?

allan

Sorry - I completely missed the link before. I see it now - thanks!

When I load the page you linked to, the table loads, and it does indeed look like it is correctly using server-side processing. Its fairly slow - taking about 1.3S to generate the response, but that might just be the database working fairly hard and needing to be indexed.

What do I need to do in order to trigger the out of memory error?

Allan

cbasmadjian

Allan,

At the top of the table there are a collection of buttons and such. Choose different shapes. Round, pear, Etc.

But make sure only one is selected. So select one then when done deselect then select another. You will see what i have been witnessing.

Thanks!

cbasmadjian

To help you understand the issue I have put a step-by-step instructions.

1. Select "Round"
2. Look in the Network Tab you will see the Fatal Error
3. DeSelect "Round"
4. Network Tab: You will see the Fatal Error
5. Select "Marquise"
6. Network Tab: You will get results
7. DeSelect "Marquise"
8. Network Tab: You get see the Fatal Error
9. Select "Pear"
10. Network Tab: You will see the results

Even though results get returned from the Editor it never updates the DataTable.

What's going on here?

allan

Okay - I see the error now - thanks!

It is sending a request to invDiamonds.php with the following parameters:

dimondsInventory: true
shapeResults[]: Round

There are no server-side processing parameters in the request. Unlike the first request when the page is loaded, which does include server-side processing parameters.

So the fatal error is need being caused by a non-server-side processing request.

In your code you have:

        $('.shape').click( function() {
            var shapes = ColorShapeSelection();
            $.post( "../../wp-content/mu-plugins/invDiamonds.php", { diamondsInventory: "true", shapeResults: shapes } );
        } );

I don't see any callback, so even if it did load the data, nothing would happen!

Instead of doing that, I would suggest you use the click event handler to call draw() and change your ajax.data to be a function that will get the shape information for the selected shaped and submit the data there. The draw(), since you are using server-side processing, will make a request to the server to get the new information.

Allan

cbasmadjian

Thank you Allan. I had to reread your post a few times to "get" it. But yes it did the trick.

One thing you did mention and yes I am seeing it more obviously is the load times which I believe you are correct on my indexing. Going to look for an article on how to correctly setup my huge database so it doesn't take so long to respond with data.