DataTables ssp.class.php (server processing) for PostgreSQL, add array support?

Question

I have a converted ssp.class.php for PostgreSQL which works fine. However, I need to add ARRAY support to it.

I am hoping someone can give me some guidance/tips on the best way to do approach this, and/or give some example code if possible. I would appreciate it a lot.

You can get the modified file here: ssp.class.pg.php

About DataTables server side processing: https://datatables.net/examples/data_sources/server_side with an example.

user2880254 user2880254 · Accepted Answer · 2019-08-07T05:05:36

I'm a little late to the party, but I found this question while searching google for an answer and wanted to post what I figured out to help the next person who might stumble upon this.

Note: I am using the referenced ssp.class.pg.php

My Solution - Use Heredoc

Using heredoc, you can better define your query using array functions, inner and outer joins, subqueries etc.

In my example, I have a PostgreSQL table that contains a jsonb column named properties. I want to select the value from the last_seen key.

Using Heredoc, I define my table as a query that will return the users last seen date value as a column.

$table = <<<EOT
(
SELECT
id, 
unique_id, 
properties->>'last_seen' as last_seen
FROM users
WHERE token = '$token'
) temp
EOT;

Then, I can define my columns for the SSP PG class using the returned columns from my table query.

$columns = array(
            array( 'db' => 'id', 'dt' => 'id' ),
            array( 'db' => 'unique_id', 'dt' => 'unique_id' ),
            array( 'db' => 'last_seen', 'dt' => 'last_seen' ));

Finally, I'll use the defined variables to call the simple function.

$data_result = $ssp_pg->simple($request, $conn, $table, $primaryKey, $columns);

DataTables ssp.class.php (server processing) for PostgreSQL, add array support?

1 Answers