Active Record, Dao, Defaultscope(), Stored Procedures

Hi guys

For normal CRUD operations I use AR.

In AR, I use a behavior and defaultScope() to constantly filter all table records - based on the user’s access permissions (row level access control).

But for large complex reports with sql statements nested inside other sql statements, I think I should be using DAO.

The problem is that with DAO, I will loose the defaultScope (I think), and subsequently my row-level-access-control system.

What can be done about this in Yii-2?

  1. Maybe AR and DAO can share the same defaultScope.

  2. Maybe AR and DAO can obtain their data through the same database "view" or "stored procedure". You can then build the row-level filtering criteria into the stored procedure.

Maybe Yii-1 can do something like this already?

Guys, I think this is an important question for the future of any framework.

I think a good approach would be for both AR and DAO to be able to read data via stored procedures in the database.

That way, you can build your row-level-access-control directly into the stored procedures, where Yii and other external applications - such as Jaspersoft Reports - can incorporate it into their sql.

Any thoughts?

Here is what I would do if you want to use DAO in Yii 1.x with your defaultScope(). I didn’t test this, it is just a quick guess but it should work. You could wrap this in your own method


public function doTheHarlemShake()

{

    $myCriteria = new CDbCriteria; //here you add you own conditions etc.

    $criteria = $myCriteria->mergeWith($this->defaultScope()); //you merge ith with the default scope of your model

    $builder =$this->getDbConnection()->getCommandBuilder();

    $command = $builder->createCommand($criteria->toArray()); //create the command and pass it an array representation of the criteria

    $command->execute();

}

Let me know if that works for you

Point 2: You can use views already, they just can’t be updated but your model can query them like any other table. I don’t know about using stored procedures though.

Thanx Haensel

I will give it a go and report back.

Haensel

If I am not mistaken, the problem with using CDbCriteria, is that you are then back to setting up the criteria in an AR way.

I want to use plain sql statements (because I query many tables simultaneously) and then still incorporate defaultScope. Is that possible?

UPDATE: Here is what I settled on - for now: link.