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.
Get CActiveDataprovider to work with a sql statement.
Use CSqlDataProvider and somehow incorporate the defaultScope into the sql.
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.
Use ADO and try to incorporate the defaultScope into the criteria.
$criteria = $myCriteria->mergeWith($this->defaultScope());
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.