select *, /* filter some column */
from report R
left join table1 t1 on (r.id = t1.r_id)
left join table2 t2 on (t1.id = t2.t1id)
left join table3 t3 on (r.id = t3.r_id)
left join table4 t4 on (t4.t3id = t3.id)
left join table5 t5 on (t5.t4id = t4.id)
left join table6 t6 on (t6.r_id = r.id)
The YII run this query and Yii gives back the good resault. Unfortunately, I have to import more than 7000 records, and Yii is slovest than native query. Yii runs time 11 secund and the native query runs 0.50 - 1 secunds.
I debug Yii-s query plan and i see that Yii builds good query. I think the object hierarchy builds slove when I get 7000 records.
I do not want to write query but sometimes I should raw data or query plan.
I’m not sure if I understood you correctly, but in cases when speed matters you can use DAO. This way you can benefit from the ease of use of CDbCommand and access the results row by row using CDbDataReader.
My problem, that I often list data in sort table or jqGrid (Data is showed about 10 rows). Here it is enough ActiveFinder, but i export table data and data collecion has more then 7000 records then it is slove.
I do not want to write two query every time, becouse Yii can build good query if i give with() structure.
I would like to define query plan in the model (with() structure and I store in model) and if necessary, I will be able to get raw data or query plan.
Your solution is good but unfortunately I import a list which has 7000 record then above cod is too slove.
If i list the data in sort table or jqgrid then I can use above cod (Here the user see max 50 records).
My list has a export button, wich imports all data. When I import all data then active record is slovest than native query.
I debug Yii and I saw that Query was builded by Yii. It is perfect! simular situoation (for example: import) I would like to get the query (and I run in DAO) or raw data which i can work.
Summary:
I would like to use Yii query builder and I want to define query once (with() stucture) and If necessary, I can get native query which i run in dao.
For example:
//This is general mod, which good little data and I use it simple list, but it is slove if i get 7000 records.
$resault = Report::model()->with('table1')->findAll($criteria);
//If necessary. I can get native query.
//return a sql select. (select * from report join tabla1 t1 on (t1.r_id = r.id) )
$sql = Report::model()->with('table1')->getQueryPlan($criteria);
//It is not object. It is simple array.
$rawData = Yii::app()->db->createCommand($sql)->queryAll();
//and i use rawData.
foreach($rawData as $key => $value) {
....
}
I would like to find a good solution above problem.
I see above code, but unfornutaley “else” branch it is not easy. I think, I have to develop a new behavior which will solve my problem, but it won’t be easy and I will suffer a lot off.
I know this question is ancient, but in case somebody is still looking for the solution…
// get a model instance:
$model = YourModelHere::model()->with('your_relation_here');
// build or create your criteria:
$criteria = $model->dbCriteria; // or new CDbCriteria() etc.
// get an ActiveFinder instance for your model:
$finder = $model->getActiveFinder($criteria->with);
// hack into CActiveFinder to obtain the join-tree:
$accessor = new ReflectionProperty('CActiveFinder', '_joinTree');
$accessor->setAccessible(true);
/** @var CJoinElement $joinTree */
$joinTree = $accessor->getValue($finder);
// construct your own join-query instance using the stolen join-tree:
$query = new CJoinQuery($joinTree, $criteria);
// build the query:
$joinTree->buildQuery($query);
// create the command for the query:
$command = $query->createCommand($model->commandBuilder);
// run the command:
$result = $command->query();
// iterate over results:
while ($row = $result->read()) {
// ...
}
So this looks awful - it’s doable, but there’s a good reason why this is hidden from you - because the selected columns are all aliased using gibberish names like t1_c0, etc. (just to make debugging SQL queries more fun.)
Basically the only way you get anything out of this approach, is with explicit $select in your CDbCriteria instance.
Makes me wish PHP 5.3 would go away and die already
As it turns out, I did need this functionality more than once, in the application I’m currently working on - so I went ahead and factored the above hack into a reusable method.
Add this method to your own CActiveRecord extension:
class YourActiveRecord extends CActiveRecord
{
/**
* Create and configure a CDbCommand instance based on current criteria.
*
* Think twice before using this method - use only in cases where being able to stream
* through the raw SQL record-sets is crucial, usually for performance reasons, when
* dealing with very large record sets.
*
* This often will not do what you expect, at least not in the first attempt - Yii AR
* writes queries that are optimized for Yii, and not always fit for human consumption.
*
* if you're going to grab the raw SQL command with this method:
*
* - DON'T use CActiveRecord::together() - you won't get what you were expecting!
* - DO add custom CDbCriteria::$select clauses, since Yii garbles column names by
* default; you probably want to be selective about which columns you select anyway.
*
* @param mixed $condition query condition or criteria.
* @param array $params parameters to be bound to an SQL statement.
*
* @return CDbCommand DANGER DANGER, ENTER THE GRAVE YARD CHAMBER
* @see http://www.youtube.com/watch?v=auqur-Nz-X8
*/
public function createCommand($condition = '', $params = array())
{
// create criteria and apply active scope:
$criteria = $this->getCommandBuilder()->createCriteria($condition, $params);
$this->applyScopes($criteria);
// get an ActiveFinder instance for the model:
$finder = $this->getActiveFinder($criteria->with ?: array());
// hack into CActiveFinder to obtain the join-tree:
static $accessor;
if (!$accessor) {
$accessor = new ReflectionProperty('CActiveFinder', '_joinTree');
$accessor->setAccessible(true);
}
/** @var CJoinElement $joinTree */
$joinTree = $accessor->getValue($finder);
// construct your own join-query instance using the stolen join-tree:
$query = new CJoinQuery($joinTree, $criteria);
// build the query:
$joinTree->buildQuery($query);
// create the command for the query:
return $query->createCommand($this->getCommandBuilder());
}
}
You can pass CDbCriteria or array (or null) to createCommand() - and as with findAll() etc. it will apply the active scope and criteria and clear them for your next query.
For all intents and purposes, this works like findAll() now, but gives you the raw command instead of executing and binding the results to model objects.
You can go ahead and wreck that for PHP 5.3 if you need to
I’ve reported an issue and a PR that solves it about adding a public createCommand method to CActiveFinder that would allow this without such hacks.
Unfortunately everybody is busy with Yii 2 and Yii 1.1.15 isn’t even near being released soon. So until then I use my own class that extends CActiveFinder. There is only one private method that needs to be copied.
