Using Data access objects DAO in yii

 Updating a record using DAO in yii 

$connection1=Yii::app()->db;
$sql1="update inwtrans set out_section='$sname',out_time='$tdt' where id=$max";
$command1=$connection1->createCommand($sql1)->execute();

Similarly Inserting a record example usig DAO is below 

$connection=Yii::app()->db;
$sql="insert into inw_trans ( inw_no, inw_year, sec_code, sub_scode, in_time, status, in_section, ipadd) values ($inwno,'$inwyr',$sec_code,$subsec,'$tdt','O','$sname','$ipadd')";
$command=$connection->createCommand($sql)->execute();

Error handling code to perform the transactions : 

$prevyr = intval(date('Y')-1);
$connection=Yii::app()->db;
$transaction = $connection->beginTransaction();
try {
     $sql="insert into inward2(fms_rno,ref_no,uid) select fms_rno,ref_no,uid from inward where inw_year='$prevyr';";
    $connection->createCommand($sql)->execute();
    $sql1="delete from inward where inw_year='$prevyr'";
    $connection->createCommand($sql1)->execute();
    $sql2="alter table inward auto_increment=1";
    $connection->createCommand($sql2)->execute();
    $sql3="insert into inw_trans2(inw_no,sec_code,sub_scode) select inw_no, sec_code, sub_scode from inw_trans where inw_year='prevyr'";
    $connection->createCommand($sql3)->execute();
    $sql4="delete from inw_trans where inw_year='$prevyr'";
    $connection->createCommand($sql4)->execute();
    $sql5="alter table inw_trans auto_increment = 1;";
    $connection->createCommand($sql5)->execute(); 
    $transaction->commit();
}catch (Exception $e) {
$transaction->rollBack();
}

Example of a query which return an array

$list= Yii::app()->db->createCommand('select * from post')->queryAll();

$rs=array();
foreach($list as $item){
    //process each item here
    $rs[]=$item['id'];
}
return $rs;

if you want to bind some params:

$list= Yii::app()->db->createCommand('select * from post where category=:category')->bindValue('category',$category)->queryAll();

if you just want to run a query return nothing return:

Yii::app()->db->createCommand('delete * from post')->query();

Database Access Objects DAO in YII

Database Access Objects

1. Creating DB Connections
2. Executing SQL Queries
3. Quoting Table and Column Names
4. Performing Transactions
5. Replication and Read-Write Splitting
6. Working with Database Schema

Built on top of PDO, Yii DAO (Database Access Objects) provides an object-oriented API for accessing relational databases. It is the foundation for other more advanced database access methods, including query builder and active record.

When using Yii DAO, you mainly need to deal with plain SQLs and PHP arrays. As a result, it is the most efficient way to access databases. However, because SQL syntax may vary for different databases, using Yii DAO also means you have to take extra effort to create a database-agnostic application.

Yii DAO supports the following databases out of box:

• MySQL
• MariaDB
• SQLite
• PostgreSQL
• CUBRID: version 9.3 or higher.
• Oracle
• MSSQL: version 2008 or higher.

Creating DB Connections

To access a database, you first need to connect to it by creating an instance of yii\db\Connection:

$db = new yii\db\Connection([
    'dsn' => 'mysql:host=localhost;dbname=example',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
]);

Because a DB connection often needs to be accessed in different places, a common practice is to configure it in terms of an application component like the following:

return [
    // ...
    'components' => [
        // ...
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=example',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8',
        ],
    ],
    // ...
];

You can then access the DB connection via the expression Yii::$app->db.

Tip: You can configure multiple DB application components if your application needs to access multiple databases.

When configuring a DB connection, you should always specify its Data Source Name (DSN) via the dsn property. The format of DSN varies for different databases. Please refer to the PHP manual for more details. Below are some examples:

• MySQL, MariaDB: mysql:host=localhost;dbname=mydatabase
• SQLite: sqlite:/path/to/database/file
• PostgreSQL: pgsql:host=localhost;port=5432;dbname=mydatabase
• CUBRID: cubrid:dbname=demodb;host=localhost;port=33000
• MS SQL Server (via sqlsrv driver): sqlsrv:Server=localhost;Database=mydatabase
• MS SQL Server (via dblib driver): dblib:host=localhost;dbname=mydatabase
• MS SQL Server (via mssql driver): mssql:host=localhost;dbname=mydatabase
• Oracle: oci:dbname=//localhost:1521/mydatabase

Note that if you are connecting with a database via ODBC, you should configure the yii\db\Connection::$driverName property so that Yii can know the actual database type. For example,

'db' => [
    'class' => 'yii\db\Connection',
    'driverName' => 'mysql',
    'dsn' => 'odbc:Driver={MySQL};Server=localhost;Database=test',
    'username' => 'root',
    'password' => '',
],

Besides the dsn property, you often need to configure username and password. Please refer to yii\db\Connection for the full list of configurable properties.

Info: When you create a DB connection instance, the actual connection to the database is not established until you execute the first SQL or you call the open() method explicitly.

Tip: Sometimes you may want to execute some queries right after the database connection is established to initialize some environment variables (e.g., to set the timezone or character set). You can do so by registering an event handler for the afterOpen event of the database connection. You may register the handler directly in the application configuration like so:

'db' => [
    // ...
    'on afterOpen' => function($event) {
        // $event->sender refers to the DB connection
        $event->sender->createCommand("SET time_zone = 'UTC'")->execute();
    }
],

Executing SQL Queries

Once you have a database connection instance, you can execute a SQL query by taking the following steps:

1. Create a yii\db\Command with a plain SQL query;
2. Bind parameters (optional);
3. Call one of the SQL execution methods in yii\db\Command.

The following example shows various ways of fetching data from a database:

// return a set of rows. each row is an associative array of column names and values.
// an empty array is returned if the query returned no results
$posts = Yii::$app->db->createCommand('SELECT * FROM post')
            ->queryAll();

// return a single row (the first row)
// false is returned if the query has no result
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=1')
           ->queryOne();

// return a single column (the first column)
// an empty array is returned if the query returned no results
$titles = Yii::$app->db->createCommand('SELECT title FROM post')
             ->queryColumn();

// return a scalar value
// false is returned if the query has no result
$count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM post')
             ->queryScalar();

Note: To preserve precision, the data fetched from databases are all represented as strings, even if the corresponding database column types are numerical.

Binding Parameters

When creating a DB command from a SQL with parameters, you should almost always use the approach of binding parameters to prevent SQL injection attacks. For example,

$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
           ->bindValue(':id', $_GET['id'])
           ->bindValue(':status', 1)
           ->queryOne();

In the SQL statement, you can embed one or multiple parameter placeholders (e.g. :id in the above example). A parameter placeholder should be a string starting with a colon. You may then call one of the following parameter binding methods to bind the parameter values:

• bindValue(): bind a single parameter value
• bindValues(): bind multiple parameter values in one call
• bindParam(): similar to bindValue() but also support binding parameter references.

The following example shows alternative ways of binding parameters:

$params = [':id' => $_GET['id'], ':status' => 1];

$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
           ->bindValues($params)
           ->queryOne();
           
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status', $params)
           ->queryOne();

Parameter binding is implemented via prepared statements. Besides preventing SQL injection attacks, it may also improve performance by preparing a SQL statement once and executing it multiple times with different parameters. For example,

$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id');

$post1 = $command->bindValue(':id', 1)->queryOne();
$post2 = $command->bindValue(':id', 2)->queryOne();
// ...

Because bindParam() supports binding parameters by references, the above code can also be written like the following:

$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id')
              ->bindParam(':id', $id);

$id = 1;
$post1 = $command->queryOne();

$id = 2;
$post2 = $command->queryOne();
// ...

Notice that you bind the placeholder to the $id variable before the execution, and then change the value of that variable before each subsequent execution (this is often done with loops). Executing queries in this manner can be vastly more efficient than running a new query for every different parameter value.

Executing Non-SELECT Queries

The queryXyz() methods introduced in the previous sections all deal with SELECT queries which fetch data from databases. For queries that do not bring back data, you should call the yii\db\Command::execute() method instead. For example,

Yii::$app->db->createCommand('UPDATE post SET status=1 WHERE id=1')
   ->execute();

The yii\db\Command::execute() method returns the number of rows affected by the SQL execution.

For INSERT, UPDATE and DELETE queries, instead of writing plain SQLs, you may call insert(), update(), delete(), respectively, to build the corresponding SQLs. These methods will properly quote table and column names and bind parameter values. For example,

// INSERT (table name, column values)
Yii::$app->db->createCommand()->insert('user', [
    'name' => 'Sam',
    'age' => 30,
])->execute();

// UPDATE (table name, column values, condition)
Yii::$app->db->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

// DELETE (table name, condition)
Yii::$app->db->createCommand()->delete('user', 'status = 0')->execute();

You may also call batchInsert() to insert multiple rows in one shot, which is much more efficient than inserting one row at a time:

// table name, column names, column values
Yii::$app->db->createCommand()->batchInsert('user', ['name', 'age'], [
    ['Tom', 30],
    ['Jane', 20],
    ['Linda', 25],
])->execute();

Note that the aforementioned methods only create the query and you always have to call execute() to actually run them.

Quoting Table and Column Names

When writing database-agnostic code, properly quoting table and column names is often a headache because different databases have different name quoting rules. To overcome this problem, you may use the following quoting syntax introduced by Yii:

• [[column name]]: enclose a column name to be quoted in double square brackets;
• {{table name}}: enclose a table name to be quoted in double curly brackets.

Yii DAO will automatically convert such constructs into the corresponding quoted column or table names using the DBMS specific syntax. For example,

// executes this SQL for MySQL: SELECT COUNT(`id`) FROM `employee`
$count = Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{employee}}")
            ->queryScalar();

Using Table Prefix

If most of your DB tables names share a common prefix, you may use the table prefix feature provided by Yii DAO.

First, specify the table prefix via the yii\db\Connection::$tablePrefix property:

return [
    // ...
    'components' => [
        // ...
        'db' => [
            // ...
            'tablePrefix' => 'tbl_',
        ],
    ],
];

Then in your code, whenever you need to refer to a table whose name contains such a prefix, use the syntax {{%table_name}}. The percentage character will be automatically replaced with the table prefix that you have specified when configuring the DB connection. For example,

// executes this SQL for MySQL: SELECT COUNT(`id`) FROM `tbl_employee`
$count = Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{%employee}}")
            ->queryScalar();

Performing Transactions

When running multiple related queries in a sequence, you may need to wrap them in a transaction to ensure the integrity and consistency of your database. If any of the queries fails, the database will be rolled back to the state as if none of these queries were executed.

The following code shows a typical way of using transactions:

Yii::$app->db->transaction(function($db) {
    $db->createCommand($sql1)->execute();
    $db->createCommand($sql2)->execute();
    // ... executing other SQL statements ...
});

The above code is equivalent to the following, which gives you more control about the error handling code:

$db = Yii::$app->db;
$transaction = $db->beginTransaction();

try {
    $db->createCommand($sql1)->execute();
    $db->createCommand($sql2)->execute();
    // ... executing other SQL statements ...
    
    $transaction->commit();
    
} catch(\Exception $e) {

    $transaction->rollBack();
    
    throw $e;
}

By calling the beginTransaction() method, a new transaction is started. The transaction is represented as a yii\db\Transaction object stored in the$transaction variable. Then, the queries being executed are enclosed in a try...catch... block. If all queries are executed successfully, thecommit() method is called to commit the transaction. Otherwise, if an exception will be triggered and caught, the rollBack() method is called to roll back the changes made by the queries prior to that failed query in the transaction. throw $e will then re-throw the exception as if we had not caught it, so the normal error handling process will take care of it.

Specifying Isolation Levels

Yii also supports setting isolation levels for your transactions. By default, when starting a new transaction, it will use the default isolation level set by your database system. You can override the default isolation level as follows,

$isolationLevel = \yii\db\Transaction::REPEATABLE_READ;

Yii::$app->db->transaction(function ($db) {
    ....
}, $isolationLevel);
 
// or alternatively

$transaction = Yii::$app->db->beginTransaction($isolationLevel);

Yii provides four constants for the most common isolation levels:

• yii\db\Transaction::READ_UNCOMMITTED - the weakest level, Dirty reads, non-repeatable reads and phantoms may occur.
• yii\db\Transaction::READ_COMMITTED - avoid dirty reads.
• yii\db\Transaction::REPEATABLE_READ - avoid dirty reads and non-repeatable reads.
• yii\db\Transaction::SERIALIZABLE - the strongest level, avoids all of the above named problems.

Besides using the above constants to specify isolation levels, you may also use strings with a valid syntax supported by the DBMS that you are using. For example, in PostgreSQL, you may use SERIALIZABLE READ ONLY DEFERRABLE.

Note that some DBMS allow setting the isolation level only for the whole connection. Any subsequent transactions will get the same isolation level even if you do not specify any. When using this feature you may need to set the isolation level for all transactions explicitly to avoid conflicting settings. At the time of this writing, only MSSQL and SQLite are affected by this limitation.

Note: SQLite only supports two isolation levels, so you can only use READ UNCOMMITTED and SERIALIZABLE. Usage of other levels will result in an exception being thrown.

Note: PostgreSQL does not allow setting the isolation level before the transaction starts so you can not specify the isolation level directly when starting the transaction. You have to call yii\db\Transaction::setIsolationLevel() in this case after the transaction has started.

Nesting Transactions

If your DBMS supports Savepoint, you may nest multiple transactions like the following:

Yii::$app->db->transaction(function ($db) {
    // outer transaction
    
    $db->transaction(function ($db) {
        // inner transaction
    });
});

Or alternatively,

$db = Yii::$app->db;
$outerTransaction = $db->beginTransaction();
try {
    $db->createCommand($sql1)->execute();

    $innerTransaction = $db->beginTransaction();
    try {
        $db->createCommand($sql2)->execute();
        $innerTransaction->commit();
    } catch (\Exception $e) {
        $innerTransaction->rollBack();
        throw $e;
    }

    $outerTransaction->commit();
} catch (\Exception $e) {
    $outerTransaction->rollBack();
    throw $e;
}

Replication and Read-Write Splitting

Many DBMS support database replication to get better database availability and faster server response time. With database replication, data are replicated from the so-called master servers to slave servers. All writes and updates must take place on the master servers, while reads may also take place on the slave servers.

To take advantage of database replication and achieve read-write splitting, you can configure a yii\db\Connection component like the following:

[
    'class' => 'yii\db\Connection',

    // configuration for the master
    'dsn' => 'dsn for master server',
    'username' => 'master',
    'password' => '',

    // common configuration for slaves
    'slaveConfig' => [
        'username' => 'slave',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of slave configurations
    'slaves' => [
        ['dsn' => 'dsn for slave server 1'],
        ['dsn' => 'dsn for slave server 2'],
        ['dsn' => 'dsn for slave server 3'],
        ['dsn' => 'dsn for slave server 4'],
    ],
]

The above configuration specifies a setup with a single master and multiple slaves. One of the slaves will be connected and used to perform read queries, while the master will be used to perform write queries. Such read-write splitting is accomplished automatically with this configuration. For example,

// create a Connection instance using the above configuration
Yii::$app->db = Yii::createObject($config);

// query against one of the slaves
$rows = Yii::$app->db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();

// query against the master
Yii::$app->db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

Info: Queries performed by calling yii\db\Command::execute() are considered as write queries, while all other queries done through one of the "query" methods of yii\db\Command are read queries. You can get the currently active slave connection via Yii::$app->db->slave.

The Connection component supports load balancing and failover between slaves. When performing a read query for the first time, the Connectioncomponent will randomly pick a slave and try connecting to it. If the slave is found "dead", it will try another one. If none of the slaves is available, it will connect to the master. By configuring a server status cache, a "dead" server can be remembered so that it will not be tried again during a certain period of time.

Info: In the above configuration, a connection timeout of 10 seconds is specified for every slave. This means if a slave cannot be reached in 10 seconds, it is considered as "dead". You can adjust this parameter based on your actual environment.

You can also configure multiple masters with multiple slaves. For example,

[
    'class' => 'yii\db\Connection',

    // common configuration for masters
    'masterConfig' => [
        'username' => 'master',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of master configurations
    'masters' => [
        ['dsn' => 'dsn for master server 1'],
        ['dsn' => 'dsn for master server 2'],
    ],

    // common configuration for slaves
    'slaveConfig' => [
        'username' => 'slave',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of slave configurations
    'slaves' => [
        ['dsn' => 'dsn for slave server 1'],
        ['dsn' => 'dsn for slave server 2'],
        ['dsn' => 'dsn for slave server 3'],
        ['dsn' => 'dsn for slave server 4'],
    ],
]

The above configuration specifies two masters and four slaves. The Connection component also supports load balancing and failover between masters just as it does between slaves. A difference is that when none of the masters are available an exception will be thrown.

Note: When you use the masters property to configure one or multiple masters, all other properties for specifying a database connection (e.g.dsn, username, password) with the Connection object itself will be ignored.

By default, transactions use the master connection. And within a transaction, all DB operations will use the master connection. For example,

$db = Yii::$app->db;
// the transaction is started on the master connection
$transaction = $db->beginTransaction();

try {
    // both queries are performed against the master
    $rows = $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
    $db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

    $transaction->commit();
} catch(\Exception $e) {
    $transaction->rollBack();
    throw $e;
}

If you want to start a transaction with the slave connection, you should explicitly do so, like the following:

$transaction = Yii::$app->db->slave->beginTransaction();

Sometimes, you may want to force using the master connection to perform a read query. This can be achieved with the useMaster() method:

$rows = Yii::$app->db->useMaster(function ($db) {
    return $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
});

You may also directly set Yii::$app->db->enableSlaves to be false to direct all queries to the master connection.

Working with Database Schema

Yii DAO provides a whole set of methods to let you manipulate the database schema, such as creating new tables, dropping a column from a table, etc. These methods are listed as follows:

• createTable(): creating a table
• renameTable(): renaming a table
• dropTable(): removing a table
• truncateTable(): removing all rows in a table
• addColumn(): adding a column
• renameColumn(): renaming a column
• dropColumn(): removing a column
• alterColumn(): altering a column
• addPrimaryKey(): adding a primary key
• dropPrimaryKey(): removing a primary key
• addForeignKey(): adding a foreign key
• dropForeignKey(): removing a foreign key
• createIndex(): creating an index
• dropIndex(): removing an index

These methods can be used like the following:

// CREATE TABLE
Yii::$app->db->createCommand()->createTable('post', [
    'id' => 'pk',
    'title' => 'string',
    'text' => 'text',
]);

The above array describes the name and types of the columns to be created. For the column types, Yii provides a set of abstract data types, that allow you to define a database agnostic schema. These are converted to DBMS specific type definitions dependent on the database, the table is created in. Please refer to the API documentation of the createTable()-method for more information.

Besides changing the database schema, you can also retrieve the definition information about a table through the getTableSchema() method of a DB connection. For example,

$table = Yii::$app->db->getTableSchema('post');

The method returns a yii\db\TableSchema object which contains the information about the table's columns, primary keys, foreign keys, etc. All these information are mainly utilized by query builder and active record to help you write database-agnostic code.

Yii Examples of Using CDbCriteria

Examples of Using CDbCriteria
Basic Usage

$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Products = Product::model()->findAll($Criteria);

OR

//An example using the constructor to populate the properties.
$Criteria = new CDbCriteria(array('condition' => 'price > 30'));
$Products = Product::model()->findAll($Criteria);

Personally, I like to go with the first approach. I think it’s generally easier to read, but that’s just my personal preference.
Adding A Limit

$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Products = Product::model()->findAll($Criteria);

Limit with Offset

$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Criteria->offset = 1;
$Products = Product::model()->findAll($Criteria);

Ordering Results

$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Criteria->offset = 1;
$Criteria->order = "name ASC";
$Products = Product::model()->findAll($Criteria);

Limiting Selected Fields

$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Criteria->offset = 1;
$Criteria->order = "name ASC";
$Criteria->select = "id, name";
$Products = Product::model()->findAll($Criteria);

Example relation with :

$criteria = new CDbCriteria;
$criteria->with = array('foreign_table1',
                        'foreign_table2', 
                        'foreign_table2.foreign_table3');
$criteria->together = true; // ADDED THIS
$criteria->select = array('id');
$criteria->condition = "foreign_table1.col1=:col_val AND 
                        foreign_table3.col3=:col_val2";
$criteria->params = array(':col_val' => some_val, ':col_val2' => other_val);
$criteria->order = 'foreign_table3.col5 DESC';
$criteria->limit = 10;

Getting Maximum ID through Criteria : 

$criteria=new CDbCriteria;                                                                                      
$criteria->select = 'max(id) as id';                                                                            
$criteria->condition = 'inwno=:param and iyear=:param2';                                          
$criteria->params = array(':param' => $inwno,':param2' => $inwyr);                            
$transId=InwTrans::model()->find($criteria);                                                             

$max=$transId['id'];                                                                                               

You have to supply the member id(s) array itself to the

addInCondition

, 

$criteria = new CDbCriteria;
$criteria->compare('project.ID',$project_id);
$criteria->addInCondition('t.REF_USER',$members);

$user2groups = User2group::model()
                        ->with('group')
                        ->with('group.project')
                        ->findAll($criteria);

These options all result in the exact same query: SELECT (..) WHERE (t.id=:ycp0). 

Choose the one that you like best:

$criteria = new CDbCriteria;
// Classic method
$criteria->addCondition('t.id = :id');
$criteria->params = array(':id' => Yii::app()->user->id);
// Often used in search functions. Note: if passed value is empty, the WHERE is not added!
$criteria->compare('t.id', Yii::app()->user->id);
// This is my current favorite
$criteria->addColumnCondition(array('t.id' => Yii::app()->user->id));
// A bit weird here, but you can also do this
$criteria->addInCondition('t.id', array(Yii::app()->user->id));

In my case, I was trying to return the results of my query in a descending fashion, depending on a foreign table's column. So I used code like


$criteria = new CDbCriteria;
$criteria->with = array('foreign_table1',
                        'foreign_table2', 
                        'foreign_table2.foreign_table3');
$criteria->select = array('id');
$criteria->condition = "foreign_table1.col1=:col_val AND 
                        foreign_table3.col3=:col_val2";
$criteria->params = array(':col_val' => some_val, ':col_val2' => other_val);
$criteria->order = 'foreign_table3.col5 DESC';
$criteria->limit = 10;



So, my SQL would crash because I would get a column not found error, or an access error. I spent a day focusing on my condition statement, when the problem was actually with the use of the limit command. Without the LIMIT command, I got my results. With the LIMIT command, an error

So, based upon looking around in the forum, the reason was that I should have used TOGETHER, as so:


$criteria = new CDbCriteria;
$criteria->with = array('foreign_table1',
                        'foreign_table2', 
                        'foreign_table2.foreign_table3');
$criteria->together = true; // ADDED THIS
$criteria->select = array('id');
$criteria->condition = "foreign_table1.col1=:col_val AND 
                        foreign_table3.col3=:col_val2";
$criteria->params = array(':col_val' => some_val, ':col_val2' => other_val);
$criteria->order = 'foreign_table3.col5 DESC';
$criteria->limit = 10;



Because TOGETHER links together your foreign tables and allows you to execute one sql statement, this is what is needed not only to make LIMIT for a foreign table's column to work





If your model has a HAS_MANY in relations and you want to join it up (in case for example you want to search in the related table as well), then next to setting up the with, you also need to set the together to true. So in your model you have:


class MyFirstTable extends CActiveRecord {
...
    public function relations() {
        return array(
            'mySecondTable' => array(self::HAS_MANY, 'MySecondTable', 'second_table_id'),
        );
    }
...
}



Then to be able to search in the second table data as well you need to have the search method like this:


public function searchWithRelated() {
        $criteria = new CDbCriteria;
 
        $criteria->together = true; //without this you wont be able to search the second table's data
        $criteria->with = array('mySecondTable');
        $criteria->compare('id', $this->id, true);
        $criteria->compare('mySecondTable.column', $this->mySecondTable_column, true);
 
        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'sort'=>array(
                'defaultOrder'=>'t.id DESC',
            ),
            'pagination' => array(
                'pageSize' => 100,
            ),
        ));
    }