combining $search and $userid in where clause in server file

cpshart · December 2020

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

Hi, I am trying to combine some conditions in my server file using the ->where construct as follows

this extract (1/2) of my server file extracts only stocks with a currency code of GBX or GBP successfully

        ,Field::inst( 'dm_holdings.stock_id' )
            ->options( Options::inst()
                ->table( 'dm_stocks' )
                ->value( 'id' )
                ->label( array('symbol', 'name') )
                ->where( function ( $q ) use ( $search ) {
                             $q
                             ->where('dm_stocks.symbol', $search, 'LIKE' )
                             ->and_where( function ( $r ) {
                                $r->where('dm_stocks.currency','GBX');  
                                $r->or_where('dm_stocks.currency','GBP');   
                            });

this extract (2/2) only selects portfolios.code where the user_id is equal to the current logged in user given by $userid

        ,Field::inst( 'dm_portfolios.code' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->value( 'dm_portfolios.code')
                ->label( 'dm_portfolios.code' )
                ->leftJoin( 'dm_portfolios', 'dm_portfolios.id', '=', 'dm_holdings.portfolio_id' )
                ->where( function ( $q ) use ( $userid) {
                    $q
                    ->where( 'dm_holdings.user_id', $userid );
                } )
                )

I need to add 2 conditions for $userid condition to extract (1/2) code along the lines of

                ->where( function ( $q ) use ( $userid) {
                    $q
                    ->where( 'dm_stocks.user_id', $userid )
                    ->or_where( 'dm_stocks.user_id', '0' );
});

My question is how do I integrate the above code into extract (1/2) of my server file, I have tried but failed to understand how to combine the $search and $userid conditions.

Assuming you have access to my system (Pm'd previously) the files are

to demonstrate the problem
https://www.dividendview.co.uk/stock-holdings/

client file, should not be required, let me know if you require it

server file

/home/ukincome/public_html/Editor-PHP-1.9.6/controllers/dview-stock_holdings-1.01.php

Many Thanks

Colin

allan · December 2020

Hi Colin,

Just to clarify - are you asking how to add the currency WHERE condition to the SearchPane options? Can you show me the WHERE condition you are trying to build in SQL please?

Allan

cpshart · December 2020

Hi Allan

I need to restrict the stocks in the selection to dm_stocks.user_id = 0 (available to all users i.e. standard stocks, 99.9% of defined stocks) and dm_stocks.user_id = $userid (user specific stocks only defined and available to logged in user)

So if user_id = 2 for logged in user, I need selection where dm_stocks.user_id = 0 AND dm_stocks.user_id = 2

The currency extract (1/2) works, so it restricts the selection of stocks to GBX and GBP currency types. The issue is that section of code (1/2) will select all logged in users user specific stocks where dm_stocks.user_id = 0 and 1, 2, 3, ... etc

I need to know the syntax of how to integrate the user_id condition below

                ->where( function ( $q ) use ( $userid) {
                    $q
                    ->where( 'dm_stocks.user_id', $userid )
                    ->or_where( 'dm_stocks.user_id', '0' );
});

into extract (1/2) code below (which works), so add that condition to the currency condition for selection of standard stocks (user_id=0) and user specific stocks

,Field::inst( 'dm_holdings.stock_id' )
    ->options( Options::inst()
        ->table( 'dm_stocks' )
        ->value( 'id' )
        ->label( array('symbol', 'name') )
        ->where( function ( $q ) use ( $search ) {
                     $q
                     ->where('dm_stocks.symbol', $search, 'LIKE' )
                     ->and_where( function ( $r ) {
                        $r->where('dm_stocks.currency','GBX'); 
                        $r->or_where('dm_stocks.currency','GBP');  
                    });

webpage and server file shown above in call
client snippet
https://www.dividendview.co.uk/wp-admin/post.php?post=30366&action=edit

screen dump of debug and editor of extract (1/2) before addition of user_id code.

query: "SELECT DISTINCT  `id` as 'id', `symbol` as 'symbol', `name` as 'name' FROM  `dm_stocks` WHERE (`dm_stocks`.`symbol` LIKE :where_1 AND (`dm_stocks`.`currency` = :where_3 OR  `dm_stocks`.`currency` = :where_4 ))"

Many Thanks again

Colin

cpshart · December 2020

Hi Allan

Sorry, I should have said, it is not a searchPanes issue, thanks

Colin

allan · December 2020

Assuming I've understood correctly, then this should do what you need:

->where( function ( $q ) use ( $search ) {
    $q
        ->where( function ( $q ) use ( $userid) {
            $q
                ->where( 'dm_stocks.user_id', $userid )
                ->or_where( 'dm_stocks.user_id', '0' );
        })
        ->where('dm_stocks.symbol', $search, 'LIKE' )
        ->where( function ( $r ) {
            $r
                ->where('dm_stocks.currency','GBX')
                ->or_where('dm_stocks.currency','GBP');
        });
})

Allan

cpshart · December 2020

Many Thanks Allan,

I will test the above code today and let you know the results, it looks like your understanding of my problem is correct, I could not work out the nesting.

Best Colin

cpshart · December 2020

Hi Allan

I have added your code on a new version of the script
/home/ukincome/public_html/Editor-PHP-1.9.6/controllers/dview-stock_holdings-1.02.php and logged in as a userid = '1'

The value of $userid in line 5 must not be evaluating correctly to '1', despite holding the correct value in the code immediately above used for portfolio selection.

stocks with a userid of '1' excluded from the select list (e.g. RWAAB,BRAAIQ)
stocks with a userid of '0' included in select list
stocks with currency of GBX included in select list
stocks with currency of GBP included in select list

If I hardcode line 5 above, the select list is populated correctly
->where( 'dm_stocks.user_id', '1' )

all above stocks are included in select list

stocks where userid = 1 AND
stocks where userid = 0 AND
stocks where currency = 'GBX' AND
stocks where currency = 'GBP'

If I echo $userid at the top of the script the value returned is 1, and it is used evaluating correctly in other areas of the script.

nearly there I think, any ideas ?

Thanks

Colin

allan · December 2020

Nothing obviously wrong there and no indication of what might be overwriting it.

Try adding your echo $userid; statement just inside that anonymous function.

It would be worth adding ->debug(true) before the ->process() call (if you haven't already) and show me the JSON being returned from the server as well please. That will show the value that was bound.

Allan

cpshart · December 2020

Hi Allan

Thanks for getting back, yes it is a strange one, I have added the echo statements below, results shown after, the value being reset to ''.

Editor::inst( $db, 'dm_holdings' )

    ->debug(true)
    ->fields( 
        Field::inst( 'dm_holdings.id' )->set(false)
        ,Field::inst( 'dm_holdings.user_id' )
            ->setValue( $userid )            

        ,Field::inst( 'dm_holdings.portfolio_id' )
            ->options( Options::inst()
                ->table( 'dm_portfolios' )
                ->value( 'id' )
                ->label( array('code', 'name') )
                ->where( function ( $q ) use ( $userid) {
                    $q
                    ->where( 'dm_portfolios.user_id', $userid )
                    ->and_where('dm_portfolios.reporting_status', 'yes');
                } )
            )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Please select portfolio ..' )
            ) )
            
        ,Field::inst( 'dm_portfolios.code' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->value( 'dm_portfolios.code')
                ->label( 'dm_portfolios.code' )
                ->leftJoin( 'dm_portfolios', 'dm_portfolios.id', '=', 'dm_holdings.portfolio_id' )
                ->where( function ( $q ) use ( $userid) {echo 'line 65:$userid:' . $userid . ':';
                    $q
                    ->where( 'dm_holdings.user_id', $userid )
                    ->and_where('dm_portfolios.reporting_status', 'yes');
                } )
                )

        ,Field::inst( 'dm_holdings.stock_id' )
            ->options( Options::inst()
                ->table( 'dm_stocks' )
                ->value( 'id' )
                ->label( array('symbol', 'name') )
// added to restrict stocks userid = 0, Standard, + Custom for user user_id = $userid
// added restrict stocks to currency GBX and GBP
            ->where( function ( $q ) use ( $search ) {echo $userid;
                $q
                    ->where( function ( $q ) use ( $userid ) {echo 'line 82:$userid:' . $userid . ':';
                        $q
                            ->where( 'dm_stocks.user_id', $userid )
                            ->or_where( 'dm_stocks.user_id', '0' );
                    })
                    ->where('dm_stocks.symbol', $search, 'LIKE' )
                    ->where( function ( $r ) {
                        $r
                            ->where('dm_stocks.currency','GBX')
                            ->or_where('dm_stocks.currency','GBP');
                    });
            })  
            )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Please select stock ..' )
            ) )

Here is the JSON file

http://dividendview.co.uk/datatables_call_json.txt

I had set the debugging on, it is always on until when I go live.

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

<?php > ``` ?>

Let me know if you need more information.

Many Thanks

Colin

allan · December 2020

Weird! Are you using $userid anywhere else in your code? I don't see where it is being set in the above code at all.

Allan

allan · December 2020

one other thing you could try, if $userid is comng from a session variable, you could try using that directly - e.g. $_SESSION['userid'].

Allan

cpshart · December 2020

Hi Allan

I am setting the value of userid at the top of the script, I will explore options of getting its value in another way as you have suggested, and get back to you, thanks.

The userid is used throughout my system, as certainMySQL tables such as stocks are used for all users, but other tables such as stock portfolios (ISA, SIPP etc) , stock holdings are user specific.

method 1, currently used
server

    DataTables\Editor\SearchPaneOptions;

require( '../../wp-blog-header.php' );
 
global $current_user;
global $userid;

wp_get_current_user();
 
$search = '%';
$userid = $current_user->ID;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'dm_holdings' )

    ->debug(true)
    ->fields( 
        Field::inst( 'dm_holdings.id' )->set(false)
        ,Field::inst( 'dm_holdings.user_id' )
            ->setValue( $userid )

method 2
client

global $wpdb;    
global $current_user;   
get_currentuserinfo();
$user_id = $current_user->ID;

<input type='hidden' id='passuserid' value='<?php echo $current_user->ID; ?>'>

...etc
    var editor = new $.fn.dataTable.Editor({
        ajax: {
            url: "../../" + EDITOR_DIR + "/controllers/dview-stock_holdings-1.01.php",  
            data: function(d) {
                d.userid = $('#passuserid').val();
}

server

$userid = $_POST['userid'];

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'dm_holdings' )
... etc

Thanks

Colin

combining $search and $userid in where clause in server file

combining $search and $userid in where clause in server file

Answers

Howdy, Stranger!

Categories

DataTables

combining $search and $userid in where clause in server file

combining $search and $userid in where clause in server file

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables