yii 多表联合查询的几种方法

 

yii多表联合查询，

第一种，用command，自己拼接sql语句执行查询

 

第二种，用AR，model需继承下面的ar，执行queryall或queryrow方法

<?php
//application/components/BaseActiveRecord.php
class BaseActiveRecord extends CActiveRecord{

    /**
     * Returns the static model of the specified AR class.
     * The model returned is a static instance of the AR class.
     * It is provided for invoking class-level methods (something similar to static class methods.)
     *
     * <pre>
     * public static function model($className=__CLASS__)
     * {
     *     return parent::model($className);
     * }
     * </pre>
     *
     * @param string $className active record class name.
     * @return DBActiveRecord active record model instance.
     */
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    
    /**
     * 获取字段rawName加表别名前缀,主要联表时候防止where条件中字段冲突用的
     * @param string $columnName
     * @return string
     */
    public function getColumnRawName($columnName)
    {
        $prefix = $this->getTableAlias(true) . '.';
        $columns = $this->tableSchema->columns;
        if (isset($columns[$columnName]))
        {
            return $prefix.$columns[$columnName]->rawName;
        }
        else
        {
            return $columnName;
        }
    }
    
    /**
     * 
     * @param mixed $criteria
     */
    public function queryAll($criteria = NULL)
    {
        if ( ! empty($criteria))
        {
            $this->getDbCriteria()->mergeWith($criteria);
        }
        
        $result = $this->getCommandBuilder()
            ->createFindCommand($this->tableSchema, $this->getDbCriteria())
            ->queryAll();
        
        $this->setDbCriteria(NULL);
        
        return $result;
    }
    
    public function queryRow($criteria = NULL)
    {
        if ($criteria != NULL)
        {
            $this->getDbCriteria()->mergeWith($criteria);
        }
        
        $result = $this->getCommandBuilder()
            ->createFindCommand($this->tableSchema, $this->getDbCriteria())
            ->queryRow();
        
        $this->setDbCriteria(NULL);
        
        return $result;
    }
    
    public function compare($column, $value, $partialMatch = FALSE, $operator = 'AND')
    {
        $criteria = new \CDbCriteria;
        $column = $this->getColumnRawName($column);
        
        if ($value === array())
        {
            $criteria->condition = "1 = 0";
        }
        else if ($value === '')
        {
            $criteria->condition = $column." = ''";
        }
        else
        {
            $criteria->compare($column, $value, $partialMatch, $operator, TRUE);
        }
        
        $this->getDbCriteria()->mergeWith($criteria);
        
        return $this;
    }
    
    
}

还有一种就是用ar的relation，做的关联容易忘记，这种不好用！