Explorador de bases de datos

Para utilizar Database Explorer, comience con una tabla – llame a table() en un objeto Nette\Database\Explorer. La forma más sencilla de obtener una instancia de objeto de contexto se describe aquí, o, para el caso en que Nette Database Explorer se utilice como herramienta independiente, puede crearse manualmente.

$books = $explorer->table('book'); // db table name is 'book'

La llamada devuelve una instancia del objeto Selección, sobre el que se puede iterar para recuperar todos los libros. Cada elemento (una fila) está representado por una instancia de ActiveRow con datos asignados a sus propiedades:

foreach ($books as $book) {
	echo $book->title;
	echo $book->author_id;
}

La obtención de una fila específica se realiza mediante el método get(), que devuelve directamente una instancia de ActiveRow.

$book = $explorer->table('book')->get(2); // returns book with id 2
echo $book->title;
echo $book->author_id;

Veamos un caso de uso común. Necesita obtener libros y sus autores. Es una relación común 1:N. La solución más utilizada es obtener los datos mediante una consulta SQL con uniones de tablas. La segunda posibilidad es obtener los datos por separado, ejecutar una consulta para obtener los libros y luego obtener un autor para cada libro mediante otra consulta (por ejemplo, en su ciclo foreach). Esto podría optimizarse fácilmente para ejecutar sólo dos consultas, una para los libros y otra para los autores necesarios, y así es exactamente como lo hace Nette Database Explorer.

En los ejemplos siguientes, trabajaremos con el esquema de base de datos de la figura. Hay enlaces OneHasMany (1:N) (autor del libro author_id y posible traductor translator_id, que puede ser null) y enlaces ManyHasMany (M:N) entre el libro y sus etiquetas.

En GitHub se puede encontrar un ejemplo, que incluye un esquema.

Estructura de la base de datos utilizada en los ejemplos

El siguiente código lista el nombre del autor de cada libro y todas sus etiquetas. Discutiremos en un momento cómo funciona esto internamente.

$books = $explorer->table('book');

foreach ($books as $book) {
	echo 'title:      ' . $book->title;
	echo 'written by: ' . $book->author->name; // $book->author is row from table 'author'

	echo 'tags: ';
	foreach ($book->related('book_tag') as $bookTag) {
		echo $bookTag->tag->name . ', '; // $bookTag->tag is row from table 'tag'
	}
}

Le agradará la eficiencia con la que funciona la capa de base de datos. El ejemplo anterior hace un número constante de peticiones que se parecen a esto:

SELECT * FROM `book`
SELECT * FROM `author` WHERE (`author`.`id` IN (11, 12))
SELECT * FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 4, 2, 3))
SELECT * FROM `tag` WHERE (`tag`.`id` IN (21, 22, 23))

Si utiliza la caché (activada por defecto), ninguna columna será consultada innecesariamente. Después de la primera consulta, la caché almacenará los nombres de las columnas utilizadas y Nette Database Explorer ejecutará consultas sólo con las columnas necesarias:

SELECT `id`, `title`, `author_id` FROM `book`
SELECT `id`, `name` FROM `author` WHERE (`author`.`id` IN (11, 12))
SELECT `book_id`, `tag_id` FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 4, 2, 3))
SELECT `id`, `name` FROM `tag` WHERE (`tag`.`id` IN (21, 22, 23))

Selecciones

Ver posibilidades cómo filtrar y restringir filas Nette\Database\Table\Selection:

Podemos utilizar una interfaz fluida, por ejemplo $table->where(...)->order(...)->limit(...). Varias condiciones where o whereOr se enlazan mediante el operador AND.

donde()

Nette Database Explorer puede añadir automáticamente los operadores necesarios para los valores pasados:

Puede proporcionar un marcador de posición incluso sin el operador de columna. Estas llamadas son las mismas.

$table->where('id = ? OR id = ?', 1, 2);
$table->where('id ? OR id ?', 1, 2);

Esta función permite generar el operador correcto en función del valor:

$table->where('id ?', 2);    // id = 2
$table->where('id ?', null); // id IS NULL
$table->where('id', $ids);   // id IN (...)

Selección maneja correctamente también las condiciones negativas, funciona para matrices vacías también:

$table->where('id', []);       // id IS NULL AND FALSE
$table->where('id NOT', []);   // id IS NULL OR TRUE
$table->where('NOT (id ?)', $ids);  // NOT (id IS NULL AND FALSE)

// this will throws an exception, this syntax is not supported
$table->where('NOT id ?', $ids);

whereOr()

Ejemplo de uso sin parámetros:

// WHERE (user_id IS NULL) OR (SUM(`field1`) > SUM(`field2`))
$table->whereOr([
	'user_id IS NULL',
	'SUM(field1) > SUM(field2)',
]);

Se utilizan los parámetros. Si no especifica un operador, Nette Database Explorer añadirá automáticamente el apropiado:

// WHERE (`field1` IS NULL) OR (`field2` IN (3, 5)) OR (`amount` > 11)
$table->whereOr([
	'field1' => null,
	'field2' => [3, 5],
	'amount >' => 11,
]);

La clave puede contener una expresión que contenga signos de interrogación comodín y luego pasar parámetros en el valor:

// WHERE (`id` > 12) OR (ROUND(`id`, 5) = 3)
$table->whereOr([
	'id > ?' => 12,
	'ROUND(id, ?) = ?' => [5, 3],
]);

order()

Ejemplos de uso:

$table->order('field1');               // ORDER BY `field1`
$table->order('field1 DESC, field2');  // ORDER BY `field1` DESC, `field2`
$table->order('field = ? DESC', 123);  // ORDER BY `field` = 123 DESC

select()

Ejemplos de uso:

$table->select('field1');                 // SELECT `field1`
$table->select('col, UPPER(col) AS abc'); // SELECT `col`, UPPER(`col`) AS abc
$table->select('SUBSTR(title, ?)', 3);    // SELECT SUBSTR(`title`, 3)

limit()

Ejemplos de uso:

$table->limit(1);     // LIMIT 1
$table->limit(1, 10); // LIMIT 1 OFFSET 10

page()

Una forma alternativa de establecer el límite y el desplazamiento:

$page = 5;
$itemsPerPage = 10;
$table->page($page, $itemsPerPage); // LIMIT 10 OFFSET 40

Obteniendo el último número de página, pasado a la variable $lastPage:

$table->page($page, $itemsPerPage, $lastPage);

group()

Ejemplos de uso:

$table->group('field1');          // GROUP BY `field1`
$table->group('field1, field2');  // GROUP BY `field1`, `field2`

having()

Ejemplos de uso:

$table->having('COUNT(items) >', 100);  // HAVING COUNT(`items`) > 100

Filtrado por otro valor de tabla

A menudo es necesario filtrar los resultados por alguna condición que implique otra tabla de la base de datos. Este tipo de condiciones requieren la unión de tablas. Sin embargo, ya no es necesario escribirlas.

Supongamos que necesita obtener todos los libros cuyo autor se llame “Jon”. Todo lo que tiene que escribir es la clave de unión de la relación y el nombre de la columna de la tabla unida. La clave de unión se deriva de la columna que hace referencia a la tabla que desea unir. En nuestro ejemplo (véase el esquema de la base de datos) se trata de la columna author_id, y basta con utilizar sólo la primera parte de la misma – author (el sufijo _id puede omitirse). name es una columna de la tabla author que queremos utilizar. Una condición para el traductor de libros (que está conectado por la columna translator_id ) se puede crear con la misma facilidad.

$books = $explorer->table('book');
$books->where('author.name LIKE ?', '%Jon%');
$books->where('translator.name', 'David Grudl');

La lógica de la clave de unión se rige por la implementación de Conventions. Le animamos a utilizar DiscoveredConventions, que analiza sus claves externas y le permite trabajar fácilmente con estas relaciones.

La relación entre el libro y su autor es 1:N. La relación inversa también es posible. La llamamos backjoin. Veamos otro ejemplo. Queremos obtener todos los autores que han escrito más de 3 libros. Para hacer la unión inversa usamos la sentencia : (colon). Colon means that the joined relationship means hasMany (and it's quite logical too, as two dots are more than one dot). Unfortunately, the Selection class isn't smart enough, so we have to help with the aggregation and provide a GROUP BY, también la condición tiene que ser escrita en forma de sentencia HAVING.

$authors = $explorer->table('author');
$authors->group('author.id')
	->having('COUNT(:book.id) > 3');

Puede que haya observado que la expresión de unión hace referencia al libro, pero no está claro si estamos uniendo a través de author_id o translator_id. En el ejemplo anterior, Selection une a través de la columna author_id porque se ha encontrado una coincidencia con la tabla de origen: la tabla author. Si no hubiera tal coincidencia y hubiera más posibilidades, Nette lanzaría una AmbiguousReferenceKeyException.

Para realizar una unión a través de la columna translator_id, proporcione un parámetro opcional dentro de la expresión de unión.

$authors = $explorer->table('author');
$authors->group('author.id')
	->having('COUNT(:book(translator).id) > 3');

Veamos algunas expresiones de unión más difíciles.

Queremos encontrar todos los autores que han escrito algo sobre PHP. Todos los libros tienen etiquetas por lo que deberíamos seleccionar aquellos autores que hayan escrito algún libro con la etiqueta PHP.

$authors = $explorer->table('author');
$authors->where(':book:book_tags.tag.name', 'PHP')
	->group('author.id')
	->having('COUNT(:book:book_tags.tag.id) > 0');

Consultas agregadas

El método count() sin ningún parámetro especificado selecciona todos los registros y devuelve el tamaño del array, lo cual es muy ineficiente. Por ejemplo, si necesita calcular el número de filas para la paginación, especifique siempre el primer argumento.

Escapar y citar

Database Explorer es inteligente y escapa parámetros y comillas identificadores para usted. Sin embargo, es necesario seguir estas reglas básicas:

• las palabras clave, funciones y procedimientos deben ir en mayúsculas
• columnas y tablas deben ir en minúsculas
• pase variables como parámetros, no las concatene

->where('name like ?', 'John'); // WRONG! generates: `name` `like` ?
->where('name LIKE ?', 'John'); // CORRECT

->where('KEY = ?', $value); // WRONG! KEY is a keyword
->where('key = ?', $value); // CORRECT. generates: `key` = ?

->where('name = ' . $name); // WRONG! sql injection!
->where('name = ?', $name); // CORRECT

->select('DATE_FORMAT(created, "%d.%m.%Y")'); // WRONG! pass variables as parameters, do not concatenate
->select('DATE_FORMAT(created, ?)', '%d.%m.%Y'); // CORRECT

El uso incorrecto puede producir agujeros de seguridad

Obtención de datos

Insertar, Actualizar y Borrar

El método insert() acepta un array de objetos Traversable (por ejemplo ArrayHash que devuelve formularios):

$row = $explorer->table('users')->insert([
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

Si la clave primaria está definida en la tabla, se devuelve un objeto ActiveRow que contiene la fila insertada.

Inserción múltiple:

$explorer->table('users')->insert([
	[
		'name' => 'Jim',
		'year' => 1978,
	], [
		'name' => 'Jack',
		'year' => 1987,
	],
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978), ('Jack', 1987)

Se pueden pasar como parámetros ficheros u objetos DateTime:

$explorer->table('users')->insert([
	'name' => $name,
	'created' => new DateTime, // or $explorer::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // inserts the file
]);

Actualización (devuelve el recuento de filas afectadas):

$count = $explorer->table('users')
	->where('id', 10) // must be called before update()
	->update([
		'name' => 'Ned Stark'
	]);
// UPDATE `users` SET `name`='Ned Stark' WHERE (`id` = 10)

Para actualizar podemos utilizar los operadores += a -=:

$explorer->table('users')
	->update([
		'age+=' => 1, // see +=
	]);
// UPDATE users SET `age` = `age` + 1

Borrado (devuelve el recuento de filas borradas):

$count = $explorer->table('users')
	->where('id', 10)
	->delete();
// DELETE FROM `users` WHERE (`id` = 10)

Trabajar con relaciones

Tiene Una Relación

Tiene una relación es un caso de uso común. Libro tiene un autor. Libro tiene un traductor. La obtención de una fila relacionada se realiza principalmente mediante el método ref(). Acepta dos argumentos: nombre de la tabla de destino y columna de unión de origen. Véase el ejemplo:

$book = $explorer->table('book')->get(1);
$book->ref('author', 'author_id');

En el ejemplo anterior obtenemos la entrada de autor relacionada de la tabla author, la clave primaria de autor se busca en la columna book.author_id. El método Ref() devuelve una instancia de ActiveRow o null si no hay ninguna entrada apropiada. La fila devuelta es una instancia de ActiveRow, por lo que podemos trabajar con ella del mismo modo que con la entrada del libro.

$author = $book->ref('author', 'author_id');
$author->name;
$author->born;

// or directly
$book->ref('author', 'author_id')->name;
$book->ref('author', 'author_id')->born;

Book también tiene un traductor, por lo que obtener el nombre del traductor es bastante fácil.

$book->ref('author', 'translator_id')->name

Todo esto está muy bien, pero es algo engorroso, ¿no cree? Database Explorer ya contiene las definiciones de las claves foráneas, así que ¿por qué no utilizarlas automáticamente? Hagámoslo.

Si llamamos a una propiedad que no existe, ActiveRow intenta resolver el nombre de la propiedad llamante como relación ‘tiene una’. Obtener esta propiedad es lo mismo que llamar al método ref() con un solo argumento. Llamaremos clave al único argumento. La clave se resolverá como una relación particular de clave externa. La clave pasada se compara con las columnas de la fila, y si coincide, la clave foránea definida en la columna coincidente se utiliza para obtener datos de la tabla de destino relacionada. Véase el ejemplo:

$book->author->name;
// same as
$book->ref('author')->name;

La instancia ActiveRow no tiene columna de autor. Se buscan todas las columnas de libros que coincidan con key. En este caso, la coincidencia significa que el nombre de la columna debe contener la clave. Así, en el ejemplo anterior, la columna author_id contiene la cadena “author” y, por lo tanto, coincide con la clave “author”. Si desea obtener el traductor del libro, sólo tiene que utilizar, por ejemplo, “traductor” como clave, porque la clave “traductor” coincidirá con la columna translator_id. Encontrará más información sobre la lógica de correspondencia de claves en el capítulo Expresiones de unión.

echo $book->title . ': ';
echo $book->author->name;
if ($book->translator) {
	echo ' (translated by ' . $book->translator->name . ')';
}

Si desea buscar varios libros, utilice el mismo método. Nette Database Explorer obtendrá los autores y traductores de todos los libros obtenidos a la vez.

$books = $explorer->table('book');
foreach ($books as $book) {
	echo $book->title . ': ';
	echo $book->author->name;
	if ($book->translator) {
		echo ' (translated by ' . $book->translator->name . ')';
	}
}

El código sólo ejecutará estas 3 consultas:

SELECT * FROM `book`;
SELECT * FROM `author` WHERE (`id` IN (1, 2, 3)); -- ids of fetched books from author_id column
SELECT * FROM `author` WHERE (`id` IN (2, 3));    -- ids of fetched books from translator_id column

Tiene mucha relación

La relación “tiene muchos” es la relación inversa a “tiene uno”. El autor ha escrito muchos libros. El autor ha traducido muchos libros. Como puedes ver, este tipo de relación es un poco más difícil porque la relación tiene nombre (‘escrito’, ‘traducido’). La instancia ActiveRow tiene el método related(), que devolverá un array de entradas relacionadas. Las entradas también son instancias de ActiveRow. Véase el ejemplo siguiente:

$author = $explorer->table('author')->get(11);
echo $author->name . ' has written:';

foreach ($author->related('book.author_id') as $book) {
	echo $book->title;
}

echo 'and translated:';
foreach ($author->related('book.translator_id') as $book) {
	echo $book->title;
}

Método related() El método acepta la descripción completa de la unión pasada como dos argumentos o como un argumento unido por un punto. El primer argumento es la tabla de destino, el segundo es la columna de destino.

$author->related('book.translator_id');
// same as
$author->related('book', 'translator_id');

Puede utilizar la heurística de Nette Database Explorer basada en claves externas y proporcionar sólo el argumento clave. La clave se comparará con todas las claves externas que apunten a la tabla actual (author table). Si hay una coincidencia, Nette Database Explorer usará esta clave foránea, de lo contrario lanzará una Nette\InvalidArgumentException o AmbiguousReferenceKeyException. Puede encontrar más información sobre la lógica de coincidencia de claves en el capítulo Expresiones de unión.

Por supuesto, puede llamar a métodos relacionados para todos los autores obtenidos, Nette Database Explorer obtendrá de nuevo los libros apropiados a la vez.

$authors = $explorer->table('author');
foreach ($authors as $author) {
	echo $author->name . ' has written:';
	foreach ($author->related('book') as $book) {
		$book->title;
	}
}

El ejemplo anterior sólo ejecutará dos consultas:

SELECT * FROM `author`;
SELECT * FROM `book` WHERE (`author_id` IN (1, 2, 3)); -- ids of fetched authors

Creación manual del explorador

Se puede crear una conexión a la base de datos utilizando la configuración de la aplicación. En estos casos se crea un servicio Nette\Database\Explorer que puede pasarse como dependencia utilizando el contenedor DI.

Sin embargo, si Nette Database Explorer se utiliza como herramienta independiente, es necesario crear manualmente una instancia del objeto Nette\Database\Explorer.

// $storage implements Nette\Caching\Storage:
$storage = new Nette\Caching\Storages\FileStorage($tempDir);
$connection = new Nette\Database\Connection($dsn, $user, $password);
$structure = new Nette\Database\Structure($connection, $storage);
$conventions = new Nette\Database\Conventions\DiscoveredConventions($structure);
$explorer = new Nette\Database\Explorer($connection, $structure, $conventions, $storage);