SQLSTATE[42S22]: Column not found: 1054 Unknown column 'dm_stocks.symbol'

cpshart

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

Hi,

I am struggling with this error on my system, as I am using identical sections code in the server file shown below where one works and the other produces this error.

error: "An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'dm_stocks.symbol' in 'field list'"

The column symbol exists on the table dm_stocks

I am trying to integrate the fields dm_stocks.symboland dm_stocks.name into my searchPanes, but I am getting the above error for both fields.

I have provided the links to my system at the bottom of the call.
If you require access to my system let me know and I will PM the details, you may however already have the details, with thanks.

Extract of php server file

        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 ) {
                    $q->where( 'dm_portfolios.reporting_status', 'yes' );
                } )                
                ),

// restrict stocks search to currency GBX and GBP
        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');   
                            });            
                        })
            )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Please select stock ..' )
            ) ),

/* error section 1/ if uncommented  */      
        Field::inst( 'dm_stocks.symbol' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->value( 'dm_stocks.symbol')
                ->label( 'dm_stocks.symbol' )
                ->leftJoin( 'dm_stocks', 'dm_stocks.id', '=', 'dm_holdings.stock_id' )
            ),

        Field::inst( 'dm_stocks.name' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->value( 'dm_stocks.name')
                ->label( 'dm_stocks.name' )
                ->leftJoin( 'dm_stocks', 'dm_stocks.id', '=', 'dm_holdings.stock_id' )
            ),
//*/            
/* no error if section 2/ uncommented and  section 1/ commented
//      Field::inst( 'dm_stocks.symbol' ),
//      Field::inst( 'dm_stocks.name' ),

if you comment out section 1/ and uncomment section 2/ above there is no error and all works except there are no searchPanes for dm_stocks.symbol and dm_stocks.name as expected !!

If I try and implement searchPanes for the two fields (symbol, name) by uncommenting section 1/ and commenting section 2/ I get this error yet the same scetions of code work for my stock-dividends shown below.

client file extract

        ajax: {
            url: "../../" + EDITOR_DIR + "/controllers/dview-stock_holdings-1.01.php",          
            type: 'POST',
            order: [1, "asc"],
            data: function ( d ) {
                d.userid = $('#passuserid').val();
                                }
            },      
        columns: [
            { data: null,
                className: "details-control",
                orderable: false,
                defaultContent: ""
            },          
            { data: "dm_portfolios.code" },
            { data: "dm_stocks.symbol" },
            { data: "dm_stocks.name"},
            { data: "dm_holdings.quantity"},

Below is the Snippet and webpage creating the error
Edit Client Snippet
https://www.dividendview.co.uk/wp-admin/post.php?post=30366&action=edit

Run the Snippet
https://www.dividendview.co.uk/stock-holdings/

Below is the Snippet and webpage with sections of identical code where the dm_stocks.symbol and dm_stocks.name show in searchPanes with no errors

https://www.dividendview.co.uk/wp-admin/post.php?post=30313&action=edit

https://www.dividendview.co.uk/stock-dividends/

Any help would be much appreciated on this one.

Thanks

Colin

cpshart

Hi

I have commented out most irrelevant code on the client associated with the problem so it would be better to check the following stripped test files if you connect to my system

Client Snippet
https://www.dividendview.co.uk/wp-admin/post.php?post=31084&action=edit

Server File
public_html/Editor-PHP-1.9.4/controllers/dview-stock_holdings-test.php

Test the problem
https://www.dividendview.co.uk/stock-holdings-version-1-01-test/

Note the error has gone, but the symbol and name are not present in the searchPanes

Many Thanks Colin

allan

Looks like you need to add ->table( 'dm_stocks' ) to your SearchPaneOptions instance for the section 1 code.

Allan

cpshart

Hi Allan

Thanks for getting back, I will take a look at this tomorrow.

Best regards Colin

cpshart

Hi Allan

Unfortunately I cannot get the dm_stocks.symbol and dm_stocks.name searchPanes to work.

I have added the code as you suggested, but it throws an error on the Field::inst( 'dm_portfolios.code' ) code which was working previously.

The following code section below results in the error

table id=dm_holdings - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'dm_holdings.portfolio_id' in 'on clause'

        Field::inst( 'dm_stocks.symbol' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->table( 'dm_stocks')
                ->value( 'dm_stocks.symbol')
                ->label( 'dm_stocks.symbol' )
                ->leftJoin( 'dm_stocks', 'dm_stocks.id', '=', 'dm_holdings.stock_id' )
            ),

The following code change in the server file produces no errors and shows the searchPanes for the symbol, but when you select a value(s) in the searchPane the table displays no rows

        Field::inst( 'dm_stocks.symbol' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->table( 'dm_holdings' )
                ->value( 'stock_id')
                ->label( 'dm_stocks.symbol' )
                ->leftJoin( 'dm_stocks', 'dm_stocks.id', '=', 'dm_holdings.stock_id' )
            ),

I have added the above code for the stock name which behaves in the same manner.

Note the Portfolio searchPane filters correctly, I have tried adding the exact equivalent code for stock symbols and it does not work.

If you could help again, it would be appreciated as I am very keen to have this functionality on the webpage.

Many Thanks

Colin

allan

Hi Colin,

Field::inst( 'dm_stocks.symbol' )
    ->searchPaneOptions( SearchPaneOptions::inst()
        ->table( 'dm_stocks')
        ->value( 'dm_stocks.symbol')
        ->label( 'dm_stocks.symbol' )
        ->leftJoin( 'dm_stocks', 'dm_stocks.id', '=', 'dm_holdings.stock_id' )
    ),

Seems a bit odd - you've got a left join onto the same existing table, but also referencing dm_holdings? It doesn't look like a left join is required at all there:

Field::inst( 'dm_stocks.symbol' )
    ->searchPaneOptions( SearchPaneOptions::inst()
        ->table( 'dm_stocks' )
        ->value( 'symbol' )
        ->label( 'symbol' )
    ),

Allan

cpshart

Hi Allan

If I add the code

        Field::inst( 'dm_stocks.symbol' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->table( 'dm_stocks')
                ->value( 'symbol')
                ->label( 'symbol' )
            ),

I get the error shown above again.

An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'dm_holdings.portfolio_id' in 'on clause

on this query

SELECT  `symbol` as 'value', COUNT(*) as count FROM  `dm_stocks` LEFT JOIN `dm_portfolios` ON `dm_portfolios`.`id` = `dm_holdings`.`portfolio_id`  GROUP BY symbol

which if you run in phpMyAdmin it will produce the above error.

Thanks

Colin

allan

I've got a sneaky feeling this is a bug in the SearchPanes / Editor PHP code - I remember discussing it with Sandy a little while back.

I've just tagged up and released Editor 1.9.6 - could you update to the PHP libraries that come with that please?

Thanks,
Allan

cpshart

Hi Allan

I hope you are correct as I have stripped the code again and it is still creating this error.

I will do the update in the next hour and report back, with thanks

Best Colin

cpshart

Hi Allan

Great news !!, huge thank you it has fixed the problem.

I also noticed the working code is much simpler

        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' );
                            })
            )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Please select stock ..' )
            ) ),    
        Field::inst( 'dm_stocks.symbol' )
            ->searchPaneOptions( SearchPaneOptions::inst()),
        Field::inst( 'dm_stocks.name' )
            ->searchPaneOptions( SearchPaneOptions::inst()),

Best regards Colin