DAO - Database Access Objects (Objetos de acceso a base de datos)

Si hemos trabajando con la primera versión de Yii, recordaremos que Yii provee una API orientada al acceso de bases de datos relacionales. En esta versión el concepto se mantiene, pero se lo hace de una manera diferente.

El DAO de Yii está construido sobre la base de PDO (PHP Data Objects - Objetos de datos de PHP) , y el cual provee métodos más avanzados para el acceso a la base de datos, incluyendo Query Builder (Constructor de Consultas) y Active Record (Registro Activo).

Al utilizar el DAO de Yii, solo se requiere de la consulta SQL y arreglos PHP. El resultado, es que accedemos a la base de datos de una manera eficiente.

El DAO de Yii soporta las siguientes bases de datos:

• MySQL
• MariaDB
• SQLite
• PostgreSQL
• CUBRID: versión 9.3 o superior.
• Oracle
• MSSQL: versión 2008 o superior.

Creando conexiones a bases de datos

Para acceder a una base de datos, se debe conectar a ella mediante la creación de una instancia de yii\db\Connection:

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

Puesto que es muy seguro que necesitemos conectarnos a nuestra base de datos desde algunas partes de nuestro desarrollo, lo común es configurarlo como un componente de la aplicación:

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

Para acceder a la base de datos, se utiliza Yii::$app->db.

Dependiendo de la base de datos que utilicemos, depende el nombre de origen de datos (DSN). Por ejemplo:

• MySQL, MariaDB: mysql:host=localhost;dbname=mibasededatos
• SQLite: sqlite:/path/al/archivo/base_de_datos
• PostgreSQL: pgsql:host=localhost;port=5432;dbname=mibasededatos
• CUBRID: cubrid:dbname=demodb;host=localhost;port=33000
• MS SQL Server (vía sqlsrv driver): sqlsrv:Server=localhost;Database=mibasededatos
• MS SQL Server (vía dblib driver): dblib:host=localhost;dbname=mibasededatos
• MS SQL Server (vía mssql driver): mssql:host=localhost;dbname=mibasededatos
• Oracle: oci:dbname=//localhost:1521/mibasededatos

Si nos conectamos a través de ODBC hay que configurar la propiedad yii\db\Connection::$driverName de manera similar a:

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

Obviamente, también es necesario especificar el usuario y clave.

Es importante conocer que, al configurar nuestra instancia de conexión a base de datos, la conexión no se establece hasta que ejecutemos nuestra primera consulta SQL o llamemos explícitamente al método open().

Ejecutando consultas SQL

Para ejecutar consultas SQL debemos seguir los siguientes pasos:

1. Crear la consulta SQL con yii\db\Command;
2. Especificar los valores de nuestros parámetros (opcional);
3. Ejecutar la consulta llamando a uno de los métodos de yii\db\Command.

A continuación unos cuantos ejemplos:

$db = new yii\db\Connection(...datos_de_conexion...);

// retorna un conjunto de filas. cada fila es una matriz asociativa de nombres y valores de las columnas.
// un arreglo vacío es devuelto si no hay resultados
$posts = $db->createCommand('SELECT * FROM post')
            ->queryAll();

// retorna una única fila (la primera)
// se retorna false si no hay resultados
$post = $db->createCommand('SELECT * FROM post WHERE id=1')
           ->queryOne();

// retorna a única columna (la primera)
// un arreglo vacío es devuelto si no hay resultados
$titles = $db->createCommand('SELECT title FROM post')
             ->queryColumn();

// retorna un escalar
// se retorna false si no hay resultados
$count = $db->createCommand('SELECT COUNT(*) FROM post')
             ->queryScalar();

Nota: Para conservar la precisión, los datos obtenidos de bases de datos están representados como cadenas, incluso si los tipos de columna de base de datos correspondientes son numéricos.

Es posible que, exactamente después de establecer la conexión a nuestra base de datos, necesitemos realizar alguna consulta, como por ejemplo para ajustar la zona horaria o el juego de carácteres. Para ello, podemos utilizar el controlador de eventos yii\db\Connection::EVENT_AFTER_OPEN de manera similar a:

return [
    // ...
    'components' => [
        // ...
        'db' => [
            'class' => 'yii\db\Connection',
            // ...
            'on afterOpen' => function($event) {
                // $event->sender se refiere a la conexión a BD
                $event->sender->createCommand("SET time_zone = 'UTC'")->execute();
            }
        ],
    ],
    // ...
];

Parámetros

Cuando creamos un comando de base de datos desde un SQL con parámetros, debemos hacerlo utilizando el método de parámetros vinculantes para prevenir ataques de inyección SQL. Por ejemplo,

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

En la sentencia SQL, se puede incrustar uno o varios marcadores de posición (por ejemplo :id en el ejemplo anterior). Un marcador de posición de parámetro debe ser una cadena que comienza con dos puntos. A continuación, se puede llamar a uno de los métodos correspondientes para enlazar los valores de los parámetros:

• bindValue(): enlaza con un valor de parámetro único
• bindValues(): enlaza valores de parámetro múltiples en una sola llamada
• bindParam(): similar a bindValue() pero soporta referencias a parámetros

Los siguientes ejemplos muestran las diferentes alternativas:

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

El enlace de parámetros es implementado a través de sentencias preparadas, lo que a más de ayudar a evitar ataques de inyección SQL también mejora el rendimiento mediante la preparación de una sentencia SQL una sola vez y ejecutarla varias veces con diferentes parámetros. Por ejemplo,

$command = $db->createCommand('SELECT * FROM post WHERE id=:id');
$post1 = $command->bindValue(':id', 1)->queryOne();$post2 = $command->bindValue(':id', 2)->queryOne();

Debido a que bindParam() admite parámetros vinculantes por referencia, el código anterior también puede escribirse como el siguiente:

$command = $db->createCommand('SELECT * FROM post WHERE id=:id')
              ->bindParam(':id', $id);
$id = 1;$post1 = $command->queryOne();
$id = 2;$post2 = $command->queryOne();

Ejecución de consultas No-Select

Hasta el momento hemos visto los métodos queryAlgoMas() que permiten realizar consultas a la base de datos a través de una sentencia del tipo select. Es posible que también requiramos ejecutar sentencias como UPDATE, DELETE, INSERT para lo cual podemos utilizar el método yii\db\Command::execute() de manera similar a la siguiente:

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

El método  yii\db\Command::execute() retorna el número de filas afectadas por la sentencia.

También podemos llamar a  insert(), update(), delete() para construir el correspondiente SQL indicando el nombre de la tabla, columnas y valores.

// INSERT (tabla, columnas y valores)
$db->createCommand()->insert('usuario', [
    'nombre' => 'Santiago',
    'edad' => 30,
])->execute();

// UPDATE (tabla, columnas y valores, condición)
$db->createCommand()->update('usuario', ['estado' => 1], 'edad > 30')->execute();
// DELETE (tabla, condición)
$db->createCommand()->delete('usuario', 'estado = 0')->execute();

Y si lo que requerimos es insertar múltiples filas se puede utilizar batchInsert(), lo cual es más eficiente al momento de insertar varias filas que de una en una.

// tabla, columnas, valores
$db->createCommand()->batchInsert('usuario', ['nombre', 'edad'], [
    ['Tomás', 30],
    ['Lorena', 20],
    ['Linda', 25],
])->execute();

También te puede interesar:
Trabajando con bases de datos
Generador de Consultas (Query Builder)
Active Record (Registro Activo)