Multi SQL Table / Dynamic query

vincent123456

hello,

I want to use datatables to build arrays with

-> Many results (often more than 10 000)
-> Data from complex queries (many joints) and dynamic clauses (according to the user profile)
-> Lines containing HTML tags
-> Creating filters outside the table (status, user etc.)

I use datatables "classic mode" but the page is very long to load (HTML construction) ...
I tried the pipeline mode (https://datatables.net/examples/server_side/pipeline.html) which is not bad, but ssp library is designed for a single sql table.

What are good solutions?
I was thinking:
-> Create View to have only a "table" and using pipeline + ssp
-> Rewrite the library ssp completely ?
-> Use another method?

What is your feedback on this?

Thank you for your help

ignignokt

I ended up rewriting/modifying the ssp file to fit my needs. This is an example of what I did. You basically build your query, then wrap that in another query, making datatables handle all of your joins and crazy query as one query so it can still do sorting, searches, filtering, etc.

vincent123456

Hi ignignokt,

Thank for your feedback.
It's good to MultiTable, but it does'nt works with filter "Search" (in top right of array).

Filter search use function "filter" with table column.
(if ( $requestColumn['searchable'] == 'true' ) {
$binding = $this->bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
$globalSearch[] = "".$column['db']." LIKE ".$binding;
})

I have to keep this operation if I want to filter all the data in the table with pipeline.

What is your feedback on this?

Thank you for your help

ignignokt

For each table I also create an array of "searchable" columns that the filter will search for, which have the same name as the data in the query. This way I can let them search for data that is not in the table if I want (Like if I build a select box in a cell, I can still have the search show columns with the active select value). It also lets me not include columns, like if you don't want a search for a zip code to also search your column showing a dollar amount.

SELECT id, first_name as 'First Name', last_name as 'Last Name', city, state, zip FROM my_table

$searchable = array('First Name', 'Last Name', 'city', 'state');

Where it has code tags in here, they are just tilde. Not sure why it does that.

static function filter ($model,$request,$searchable){
    $where = "";
    if(isset($request['search']) && $request['search']['value'] != ''){
        $where = " WHERE";
        $i=0;
        foreach($searchable as $column){
            $searchvalue = $model->prepare('%'.$request['search']['value'].'%'); //escapes the value and adds wildcards
            if($i == 0){
                $where.=" `".$column."` LIKE ".$searchvalue."";
            }else{
                $where.=" OR `".$column."` LIKE ".$searchvalue."";
            }
            $i++;
        }
    }
    return $where;
}

vincent123456

Hi,

Thank for your feedback.
I find it hard to integrate your feedback.

Could you give me some more details on the code and classes to implement?

Thank you very much

reno1979

Hi ignignokt,

I have the same request as vincent123456, it seems you have a complete working solution for a dynamic query.

Best regards.

ignignokt

I submitted a more complete example to github here