Render_sql not working as expected?

Suite and Optimus Suite 5
#1

I am using the render_sql() method of the gridConnector in a Yii Framework to populate a Grid with a filtered list from a table, using this code:

$grid = new GridConnector($current_model, "PHPYii");
$grid->configure("-", "id", $column_ids);
$sql = "SELECT $column_ids FROM `$table_name` WHERE $sub_filter";
$grid->render_sql($sql,"id",$column_ids);

$column_ids is a comma-separated list of column names.
$sub_filter is in the form “field_name=value”, and is valid.
$current_model is a valid instance of the Model referenced by $table_name.
The SQL statement $sql is valid.

But the grid displays the entire table, instead of a filtered list.
In other contexts, this method is working fine.
I can’t see what I’m doing wrong here…

I’m using v4.1.

#2

When you are providing the model to the connector, code will not use the provided SQL, it will call the method of the model instead.

To fix the issue, just provide the filtered set of data for connector, something like next

$data = Events::model()->findAll(array("condition" => "event_id > 1", "order"=>"event_id")); $grid = new GridConnector($data, "PHPYii");

#3

Thanks Stanislav

This is what I’ve used: (it works, using $sub_filter in the form “field_name=value”)

	$data = $current_model::model()->findAll($sub_filter);
	$grid = new GridConnector($data, "PHPYii");
	$grid->render_array($data,"id",$column_ids);
#4

My table has an auto-increment integer ID field as Primary Key.

One thing I have noticed is that, when using grid->render() the actual ID values (1,2,3, etc) are shown.
When using render_array(), the values shown are different, and are shown in HEX (e.g. 1460390265x0).
The data is saved with the correct auto-increment value.

What’s going on, and can I change this?

#5

Try to change your code like next

$data = $current_model::model()->findAll($sub_filter); $grid = new GridConnector($data, "PHPYii"); $grid->render_table("-","id",$column_ids);

When model provided, render_table will read data from the model, so first parameter of the command can contain anything.

It seems the data in the render_array command doesn’t contain “id” property, so connector generates random ids.

#6

Thanks Stanislav

I tried modifying the code as you suggested. Unfortunately, this gives me an error:

Fatal error:  Call to a member function findAll() on array in /dhtmlxConnector/codebase/db_phpyii.php on line 16
#7

Sorry, I have provided the wrong code, it was for previous version.
Try to use something like next

$connector = new GridConnector(null, "PHPYii"); $connector->configure( (new Vacancy())->findAll(["manager_id" => 1]), "id", "position_id" ); $connector->render();

Use the result set as the first argument of configure method ( id, and list of fields are the second and the third arguments )

#8

Thanks Stanislav
I’ve used this code, which I think is as per your suggestion:

$data = $current_model::model()->findAll($sub_filter);
$grid = new GridConnector(null, "PHPYii");
$grid->configure($data, "id", $column_ids);
$grid->render()

This gives the error:

PHP Error [2]
trim() expects parameter 1 to be string, array given (/dhtmlxConnector/codebase/db_common.php:196)

I believe this is triggered by the third line of the code snippet.

#9

Are you using the default or a “modern” branch of connector? You can install a modern branch by using

composer require dhtmlx/connector-php

or grab from the github

github.com/DHTMLX/connector-php/tree/modern

Yii code will look like next.

[code]<?php
namespace app\controllers;

use yii\rest\ActiveController;
use yii\web\Controller;
use app\models\Vacancy;
use Dhtmlx\Connector\GridConnector;

class UserController extends Controller
{
public $modelClass = ‘app\models\Vacancy’;
public function actionGrid(){

	$connector = new GridConnector(null, "PHPYii");     
    $connector->configure(                              
        (new Vacancy())->findAll(["manager_id" => 1]),
        "id", "position_id"  
    );                                                  
    $connector->render();    
}

}[/code]