Having an issue with a filter not working

Gstg

We have multiple filters and we've been working really well with them. I ran into one that doesn't seem to work, and I can't figure it out after 2 days. There are 2 filters (buyers & providers). We are trying to limit the reviews with a button filter. By default a user will see both sets of reviews, but they can use a filter to only see reviews as a buyer (buyer_user_id) or as a provider (provider_user_id). The buyer filter seems to work fine. But for some reason we are not seeing the filter work. Linking the base code and the ajax code that it calls.

Base code:

$(document).ready( function () {

    var title;

    var editor3 = new $.fn.DataTable.Editor({
               "processing": true,
                "serverSide": true,
        ajax:  "/DataTables/profiles_reviews.php",
            table: "#sort_table3",
            fields: [
            {
                          label:     'Buyer Name:',
                          name:      'buyer_user_name',
            },
            {
                          label:     'Provider Name:',
                          name:      'provider_user_name',
            },
            {
                          label:     'Rating:',
                          name:      'rating',
            },
            {
                          label:     'Review:',
                          name:      'review',
            }
         ]
     });

        var Bought = false;
        var Sold = false;
  
    var table3 = $('#sort_table3')
        .DataTable( {
            ajax: {
                url: '/DataTables/profiles_reviews.php',
                data: function (d) {
                    d.Sold = Sold;
                    d.Bought = Bought;
                }
            },
                        "language": {
                               "info": "Showing _START_ to _END_ of _TOTAL_",
                                "infoFiltered": "(from _MAX_)"
                        },
            pageLength: 8,
            //responsive: true,
            scrollX: true,
            //scrollY: 410,
            scrollCollapse: true,
//          search: {"search": myCleanSearch},
            lengthMenu: [
                [ 8, 12, 24, 40, -1 ],
                [ '8 rows', '12 rows', '24 rows', '40 rows', 'Show all' ]
            ],
            dom: '<"row row_3 row"FiB>rtp', // "F=Custom, Q=Adv Search, l=Page length, r=pRoccessing wait, t=table, i=Bottom info # of entries, p=Bottom Pages, B=Buttons?",
            columns: [
                { data: 'buyer_user_name',
                        render: function ( data, type, row ) {
                        return '<a href="/profile/?u='+row.buyer_user_id+' " style="color:blue">'+row.buyer_user_name + '</a>';
                    }                                      
                },
                { data: 'provider_user_name',
                        render: function ( data, type, row ) {
                        return '<a href="/profile/?u='+row.provider_user_id+' " style="color:blue">'+row.provider_user_name + '</a>';
                    }                                      
                },
                {  data: 'rating',
                    render: function ( data, type, row ) {
                        return '<span class="fa fa-star checked"></span>'.repeat(row.rating);
                    }
                },
                { data: 'review'}
            ],
            select: true,
            buttons: [
                 {
                    extend: 'collection',
                    name: 'filter',
                    text: 'Filter',
                    buttons: [
                            {
                                text: 'Bought',
                                name: 'Bought',
                                init: function (dt) {
                                    if (Bought) {
                                        this.active(true);
                                    }
                                },
                                action: function () {
                                    var that = this;
                                    if (Bought) {
                                        Bought = false;
                                        this.active(false);
                                        this.ajax.reload();
                                                                                changeFilterState(table3);
                                    }
                                    else {
                                        Bought = true;
                                        this.active(true);
                                        this.ajax.reload();
                                                                                changeFilterState(table3);
                                    }
                                }
                            },
                            {
                                text: 'Sold',
                                name: 'Sold',
                                init: function (dt) {
                                    if (Sold) {
                                        this.active(true);
                                    }
                                },
                                action: function () {
                                    var that = this;
                                    if (Sold) {
                                        Sold = false;
                                        this.active(false);
                                        this.ajax.reload();
                                                                                changeFilterState(table3);
                                    }
                                    else {
                                        Sold = true;
                                        this.active(true);
                                        this.ajax.reload();
                                                                                changeFilterState(table3);
                                         }
                                      }
                                },

Ajax code (/DataTables/profiles_reviews.php).

<?php

session_start();


// DataTables PHP library and database connection
include( "Editor-PHP-2.0.5/lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

if (isset($_POST['action'])) {
    echo '{}';
    return;
}

// --------------- SET ENCRYPTION - if not already set  -----------------------
$encr_iv=$_SESSION['encr_iv'];
$encr_key=$_SESSION['encr_key'];
// ---------------------------------------------------------------------------------

// Build our Editor instance and process the data coming from _POST
 Editor::inst( $db, 'profiles_paid_reviews', 'paid_id' )
    ->fields(
        Field::inst( 'rating' ),
        Field::inst( 'buyer_user_name' )->getFormatter( function ( $val, $data ) {
             return urldecode($val);
             } ),
        Field::inst( 'provider_user_name' )->getFormatter( function ( $val, $data ) {
             return urldecode($val);
             } ),
        Field::inst( 'review' )->getFormatter( function ( $val, $data ) {
             return urldecode($val);
             } ),
        Field::inst( 'paid_id' )->getFormatter( function ( $val, $data ) use ($encr_key, $encr_iv) {
             return urlencode(openssl_encrypt($val, "AES-256-CBC", $encr_key, 0, $encr_iv));
             } ),
        Field::inst( 'user_id' )->getFormatter( function ( $val, $data ) use ($encr_key, $encr_iv) {
             return urlencode(openssl_encrypt($val, "AES-256-CBC", $encr_key, 0, $encr_iv));
             } ),
        Field::inst( 'buyer_user_id' )->getFormatter( function ( $val, $data ) use ($encr_key, $encr_iv) {
             return urlencode(openssl_encrypt($val, "AES-256-CBC", $encr_key, 0, $encr_iv));
             } ),
        Field::inst( 'provider_user_id' )->getFormatter( function ( $val, $data ) use ($encr_key, $encr_iv) {
             return urlencode(openssl_encrypt($val, "AES-256-CBC", $encr_key, 0, $encr_iv));
             } )
    )
    ->where('rating', "1", '>=')
    ->where(function ($x) {
            $x->where('buyer_user_id', $_SESSION['user']);
                $x->or_where('provider_user_id', $_SESSION['user']);
            })
    ->where(function ($x) {
        //var_dump($_GET['Bought']);
        if ( $_GET['Bought'] <> 'false' ) {
            $x->where('buyer_user_id', $_SESSION['user']);
        }
     })
    ->where(function ($x) {
        if ($_GET['Sold'] <> 'false' ) {
            $x->where('provider_user_id', $_SESSION['user']);
            }
     })
    ->process( $_POST )
    ->json();

I was thinking it might be the:

                data: function (d) {
                    d.Sold = Sold;
                    d.Bought = Bought;
                }

Maybe I was passing the variables wrong? But I've tried every permutation. I've even changed the Bought filter to be the provider_user_id, but it still didn't work. The first section (below) DOES work and restricts for both buyer_user_id OR provider_user_id, indicating that the provider_user_id works.

    ->where(function ($x) {
            $x->where('buyer_user_id', $_SESSION['user']);
                $x->or_where('provider_user_id', $_SESSION['user']);
            })

Thanks again for your time.

allan

Hi,

Thanks for your question here and I'm so sorry I've not replied to your e-mails yet. I will catch up on them when I'm in the office tomorrow!

Before then though, looking at the above, I think the issue is going to be with the SQL grouping (or lack thereof).

    ->where('rating', "1", '>=')
    ->where(function ($x) {
            $x->where('buyer_user_id', $_SESSION['user']);
                $x->or_where('provider_user_id', $_SESSION['user']);
            })
    ->where(function ($x) {
        //var_dump($_GET['Bought']);
        if ( $_GET['Bought'] <> 'false' ) {
            $x->where('buyer_user_id', $_SESSION['user']);
        }
     })
    ->where(function ($x) {
        if ($_GET['Sold'] <> 'false' ) {
            $x->where('provider_user_id', $_SESSION['user']);
            }
     })

Doesn't introduce any grouping into the SQL WHERE statement, so it is going to be (depending on input):

WHERE
  rating >= 1 AND
  buyer_user_id = ? OR
  provider_user_id = ? AND
  buyer_user_id = ? AND
  provider_user_id = ?

See the problem there? You can see the SQL that is generated by adding ->debug(true) just before the ->process() call, and that will mean the SQL will be returned into the JSON to the client-side.

I think what you might want to do is have a single function to do all of these things:

->where( function ($q) {
  $q->where('rating', '1', '>=');
  
  if ( $_GET['Bought'] <> 'false' ) {
    $q->where('buyer_user_id', $_SESSION['user']);
  }
  else if ( $_GET['Sold'] <> 'false' ) {
    $q->where('provider_user_id', $_SESSION['user']);
  }
  else {
    // Note the inner function to provide grouping
    $q->where( function ($r) {
      $r->where('buyer_user_id', $_SESSION['user']);
      $r->or_where('provider_user_id', $_SESSION['user']);
    });
  }
})

I'm not sure I've got the logic exactly right here - it depends a little on what the buttons allow on the client-side - can they both be false / true at the same time - are they each a simple toggle and all four combinations are valid? If so, another if will be needed.

Regards,
Allan

Gstg

Thanks. I didn't see that the first filter was over-riding with the AND function. I"ve got it working.

Gstg

Thanks again Allan. Your support has been amazing in this process.

I've been able to work through most of the issues I emailed you about. ignore the previous emails. I emailed you tonight with the only 2 small issues left 8-)