Yii Framework Forum: Cactivedataprovider Csqldataprovider And Sql - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Cactivedataprovider Csqldataprovider And Sql Rate Topic: -----

#1 User is offline   Gerhard Liebenberg 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 325
  • Joined: 07-January 12
  • Location:Stillbay - Western Cape - South Africa

Posted 08 May 2013 - 09:57 AM

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.
0

#2 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,308
  • Joined: 17-January 09
  • Location:Russia

Posted 08 May 2013 - 10:06 AM

I'd go with #2.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#3 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,336
  • Joined: 12-October 09
  • Location:Croatia

Posted 08 May 2013 - 12:14 PM

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?
Find more about me.... btw. Do you know your WAN IP?
0

#4 User is offline   Gerhard Liebenberg 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 325
  • Joined: 07-January 12
  • Location:Stillbay - Western Cape - South Africa

Posted 08 May 2013 - 02:29 PM

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.
0

#5 User is offline   Gerhard Liebenberg 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 325
  • Joined: 07-January 12
  • Location:Stillbay - Western Cape - South Africa

Posted 09 May 2013 - 04:39 PM

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.
2

#6 User is offline   Gerhard Liebenberg 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 325
  • Joined: 07-January 12
  • Location:Stillbay - Western Cape - South Africa

Posted 28 May 2013 - 07:18 AM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users