DataTables Editor - Querying Postgresql JSONb Data

sirchunk

Using DataTables Editor, this is how I am accessing data in a Postgresql JSONb column

$editor = DataTables\Editor::inst( $db, 'data', 'id' )
            ->fields(
                DataTables\Editor\Field::inst( 'data.id', 'id' ),
                DataTables\Editor\Field::inst( 'xdata.dump->\'results\'->\'taxonomy_data\'->>\'estimate_cost\'', 'est_cost' ),
                DataTables\Editor\Field::inst( 'data.name', 'name' ),
            )
            ->leftJoin( 'extra_data AS xdata', 'xdata.id', '=', data.id' )
            ->debug( true )
            ->process( $_POST ) )
            ->json();

<table id="tblFindCosts" class="display" style="width:100%">
    <thead>
        <tr>
            <th></th>
            <th>ID</th>                            
            <th>Est Cost</th>
            <th>Name</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th></th>
            <th>ID</th>                              
            <th>Est Cost</th>
            <th>Name</th>
        </tr>
    </tfoot>
</table>

var dataTable = $('#tblFindCosts').DataTable( {
    serverSide: true,
    filter: false,
    ajax: {
        type: 'POST',
        url: '/costs/get-table-data',      
        error: function (xhr, error, code) {
            console.log(xhr.responseJSON.text);
            console.log(code);
        }
    },
    columns: [
        {
            class: 'details-control',
            orderable: false,
            searchable: false,
            data: null,
            defaultContent: ''
        },
        { data: 'id' },
        { data: 'est_cost' },
        { data: 'name' }
    ],
    order: [
        [ 1, 'asc' ]
    ],
    processing: true,
    language: {
        processing: '<span class="fa fa-spinner fa-spin fa-3x fa-fw"></span>Processing...'
    }
});

The problem is the 'Est Cost' column in the table isn't displaying the data.

If I was change
DataTables\Editor\Field::inst( 'xdata.dump->\'results\'->\'taxonomy_data\'->>\'estimate_cost\'', 'est_cost' )
to
DataTables\Editor\Field::inst( 'xdata.dump->\'results\'->>\'taxonomy_data\'', 'est_cost' )
then it shows the all the data in the taxonomy_data array.

If I was to create a WHERE clause based on the data, it works and only returns the costs specified.

If I was to use the SQL query DataTables Editor is creating to query the database and use something else like DBeaver, it works and returns the data as it should.

Why isn't DataTables Editor showing the data in the Est Cost column?

allan

Can you show me the SQL that Editor is generating for that please? We've not actually got a jsonb column in our test suite, so it is quite possible there is something odd going on there.

Thanks,
Allan

sirchunk

Hi Allan

SELECT  
    data.id as "data.id",
    xdata.dump->'results'->'taxonomy_data'->>'estimate_cost' as "xdata.dump->'results'->'taxonomy_data'->>'estimate_cost'", 
    data.name as "data.name" 
FROM  data 
LEFT JOIN extra_data AS xdata ON xdata.id = data.id 
ORDER BY data.id asc  
LIMIT 10

I can't work out why. It's a strange one.

sirchunk

NOTE: in my original post I missed a hyphen in the JOIN statement.
Can't edit now.

It should be
->leftJoin( 'extra_data AS xdata', 'xdata.id', '=', 'data.id' )

sirchunk

Just to add, if I was to add a WHERE clauses then it works and only displays items with the criteria.
I just can't work out why it's not displaying the estimate cost values in the table.

$editor = DataTables\Editor::inst( $db, 'data', 'id' )
    ->fields(
        DataTables\Editor\Field::inst( 'data.id', 'id' ),
        DataTables\Editor\Field::inst( 'xdata.dump->\'results\'->\'taxonomy_data\'->>\'estimate_cost\'', 'est_cost' ),
        DataTables\Editor\Field::inst( 'data.name', 'name' ),
    )
    ->leftJoin( 'extra_data AS xdata', 'xdata.id', '=', 'data.id' )
    ->where( function ( $q ) use ( $request ) {

        if ( isset( $request['filter'] ) ) {

            // Filter by cost
            if( $request['filter']['cost_price_min'] != '' && $request['filter']['cost_price_max'] != '' ) {
                $q->and_where( function ( $r ) use( $request ) {
                    $r->where( '(xdata.dump->\'results\'->\'taxonomy_data\'->>\'estimate_cost\')::numeric', $request['filter']['cost_price_min'], '>=' );
                    $r->where( '(xdata.dump->\'results\'->\'taxonomy_data\'->>\'estimate_cost\')::numeric', $request['filter']['cost_price_max'], '<=' );
                } );
            }
            else if( $request['filter']['cost_price_min'] != '' ) {
                $q->where( '(xdata.dump->\'results\'->\'taxonomy_data\'->>\'estimate_cost\')::numeric', $request['filter']['cost_price_min'], '>=' );
            }
            else if( $request['filter']['cost_price_max'] != '' ) {
                $q->where( '(xdata.dump->\'results\'->\'taxonomy_data\'->>\'estimate_cost\')::numeric', $request['filter']['cost_price_max'], '<=' );
            }

        }

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

SELECT  
    data.id as "data.id",
    xdata.dump->'results'->'taxonomy_data'->>'estimate_cost' as "xdata.dump->'results'->'taxonomy_data'->>'estimate_cost'", 
    data.name as "data.name" 
FROM  data 
LEFT JOIN extra_data AS xdata ON xdata.id = data.id 
WHERE ((xdata.dump->'results'->'taxonomy_data'->>'estimate_cost')::numeric >= 100 
AND (xdata.dump->'results'->'taxonomy_data'->>'estimate_cost')::numeric <= 110 ) 
ORDER BY data.id  asc  
LIMIT 10

sirchunk

Sorting the column also works.

P.S. I can view the value as I can expand the row to display the data within a child row, I just need it to display on the main row

allan

Thanks for the updates. Can you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

That should let me see what is needed to get this working.

Thanks,
Allan

sirchunk

OK, I think I have found the issue.

Is there any way to manually set the alias when calling Field::inst?

The reason being the alias is being truncated when ->fetch is called in _get() function within Editor.php

SQL:
xdata.dump->'results'->'taxonomy_data'->>'estimate_cost' as "xdata.dump->'results'->'taxonomy_data'->>'estimate_cost'",
Is returned as
xdata.dump->'results'->'taxonomy_data'->>'estimate_cost' as "xdata.dump->'results'->'taxonomy_data'->>'e'",

Being able to set the alias as the DT name would be great as I'm already setting that.
DataTables\Editor\Field::inst( 'xdata.dump->\'results\'->\'taxonomy_data\'->>\'estimate_cost\'', 'est_cost' ),

Which would make the SQL:
xdata.dump->'results'->'taxonomy_data'->>'estimate_cost' as "est_cost'",