Cactivedataprovider Csqldataprovider And Sql

Hi guys

I have complex reports where I need to query various tables simultaneously.

However, as Softarc explained in his Drills wiki, this can become a mission; and it almost becomes mission-impossible if you have to query through five or seven tables.

In these circumstances, it might be much easier to use a visual query builder to create the sql statement for you; and then to copy that sql statement to your code.

The problem is that I don’t think I can use this sql statement in CActiveDataprovider. It works in CSqlDataProvider, but then my defaultScope() is lost - and I need defaultScope() for row-level-access-control.

Option1:

Get CActiveDataprovider to work with a sql statement.

Option2:

Use CSqlDataProvider and somehow incorporate the defaultScope into the sql.

Option3:

Use a view in the database that contains the sql. Get the model to read from this view. Hopefully I should be able to incorporate the model’s defaultScope as well.

Option4:

Use ADO and try to incorporate the defaultScope into the criteria.

$criteria = $myCriteria->mergeWith($this->defaultScope());

Option5:

Use ‘stored procedures’ in the db to which you can send parameters. This will replace my row-level-access-control in defaultScope. However, I don’t think Yii 1 works with stored procedures.

Obviously there might be more/better options.

What are your thoughts? Many thanx.

I’d go with #2.

CSqlDataProvider is meant to be used with custom SQL… so the only problem you have is about defaultScope… you just need to add it to the custom SQL… would that be a problem for your solution?

Hi Maurizio

Yes that would be a problem because the defaultScope receives quite a number of parameters and performs various row-level-access-control tests regarding the user’s access permissions. And just like with defaultScope, these tests will have to be applied to all tables in the query.

Here is a scaled down example of a defaultScope that is similar to my defaultScope.

I will see how far I get using Query Builder.

Looks like Query Builder also gets quite tricky if you have complex queries and sub-queries.

So currently, the closest I can get to what I need is by using plain sql text that incorporates parameters.

The only drawback is that I have to take the tests performed in my defaultScope(), and duplicate them in my model’s search() function to set $par1, $par2 and $par3.

Here is an example of a simple query.


public function search()

{

	/* Set the values of $par1, $par2 and $par3 */

	...

	

	/* Do the sql */

	$fields = '

		tbl_1.tbl_1_id,

		tbl_1.tbl_1_field1,

		tbl_2.tbl_2_id,

		tbl_3.tbl_3_id,

		tbl_3.tbl_3_field1,

	';


	$from = '

		FROM tbl_2

	  		RIGHT OUTER JOIN tbl_1

	    			ON tbl_2.tbl_1_id = tbl_1.tbl_1_id

	  		LEFT OUTER JOIN tbl_3

	    			ON tbl_2.tbl_3_id = tbl_3.tbl_3_id

	';


	$where = '

		WHERE	`tbl_1_field6`	>= :par1 AND

			`tbl_1_field6`	<= :par2 AND

			`tbl_1_field7`	>= :par3

	';


	$params = array(

		':par1' => $par1,

		':par2' => $par2,

		':par3' => $par3,

	);


	$countSQL = 

		'SELECT COUNT(*)' .

		$from .

		$where;


	$selectSQL = 

		'SELECT' .

		$fields .

		$from .

		$where;


	$command=Yii::app()->db->createCommand($countSQL);

	foreach ($params as $key => $val)

	{

		$command->bindParam($key, $val, PDO::PARAM_STR);

	}

	$count = $command->queryScalar(); /* return first field in first row */




	return new CSqlDataProvider($selectSQL, array(

		'keyField' => 'tbl_1_id',

		'totalItemCount'=>$count,

		'params'=>$params,

		'sort'=>array(

	        	'attributes'=>array(

	            		'tbl_1_field1'

		        ),

			'defaultOrder'=>array(

				'tbl_1_field1'=>CSort::SORT_ASC,

			),

	    ),

	    'pagination'=>array(

	        'pageSize'=>10,

	    ),

	));

}

Thanx guys.

For those interested, the above sql method can also become a huge mission:

I want to show all the parent records with their child records via a junction table. Both parent and child tables must be filtered based on the user’s access permissions.

I use LEFT OUTER JOIN to include all the parent records, regardless of whether a parent record also have child records or not. That works well.

But the moment I include the filters for the child records in the WHERE clause, the final result is changed.

So the answer is to first get all valid child records in a subquery, where they can be filtered in their own WHERE clause. Then the subquery is used in the root-query, where you now filter only the parent records, which again produces the correct result.

However, if you only use a few records to test your query, the results can be easily inspected. But in a large database it is difficult to know if your query is returning all the valid records - especially if you have to use more subqueries to load additional data from other tables as well.

And besides from that, even if the query does work, you still have to convert all the filtering criteria to separate parameters in you model’s sql code - for ALL the tables you queried. This is something that programmers might easily forget to do.

So I think I’m back to active record, where defaultScope() will ensure that this filtering is performed. But instead of eager loading I will have to use lazy loading and first just retrieve the parent records. Then in the view, while printing each parent record, I will further retrieve any child records linked to that parent record - either through the parent record’s relation to the child record, or through a separate dataprovider for the child records.

This will surely be slower, but you will not miss valid records and all records will be correctly filtered by defaultScope(). Slower yes, but accurate.