Vistas SQL
Introducción
- La gestión de bases de datos es una tarea fundamental en la mayoría de los proyectos de software. Ya sea que estés desarrollando una aplicación web, un servicio de back-end o un sistema de análisis de datos, es probable que necesites interactuar con una o varias bases de datos. La gestión de estas bases de datos puede incluir tareas como crear tablas, insertar y actualizar datos, realizar consultas complejas y automatizar tareas a través de procedimientos almacenados.
- En este contexto, las vistas y triggers son herramientas muy útiles que nos permiten simplificar y optimizar nuestras consultas y operaciones. Una vista es una tabla virtual que se crea a partir de una consulta compleja y que se puede utilizar en consultas posteriores como si fuera una tabla real. Un trigger es un procedimiento almacenado que se ejecuta automáticamente cuando se produce una acción en una tabla, como insertar, actualizar o eliminar registros. Los triggers pueden ser muy útiles para automatizar tareas como la actualización de datos en otras tablas o el envío de notificaciones por correo electrónico.
- En este tutorial, vamos a aprender cómo trabajar con vistas y triggers en Eloquent, el ORM de Laravel. Eloquent es una de las herramientas más populares para la gestión de bases de datos en Laravel y es conocido por su facilidad de uso y su potencia. A lo largo del tutorial, te mostraremos cómo crear vistas y triggers en Eloquent y cómo utilizarlos para automatizar tareas y mejorar el rendimiento de tus consultas. Esperamos que, al final del tutorial, tengas un conocimiento sólido sobre estas herramientas y puedas aplicarlas en tus propios proyectos.
Vista v_productos
A veces Eloquent presenta dificultades para relalizar ciertas operaciones SQL.
En este ejemplo utilizaremos una vista creada por una migración que podras encontrar dentro de proyecto.
DB::statement es un método de la clase Illuminate\Support\Facades\DB que se utiliza para ejecutar consultas SQL directamente en la base de datos. Este método toma una cadena de consulta SQL como argumento y la ejecuta en la base de datos subyacente.
Este método es útil para ejecutar comandos de SQL que no tienen una representación directa en la API de Eloquent, como la creación o eliminación de vistas, la creación de índices de base de datos, la modificación de columnas, entre otros.
Es importante tener en cuenta que DB::statement no devuelve ningún resultado. Por lo tanto, no se puede utilizar para recuperar datos de la base de datos. En su lugar, se utiliza para ejecutar consultas que modifican la estructura de la base de datos o realizan operaciones que no requieren un conjunto de resultados.
Aquí hay un ejemplo de cómo se utiliza DB::statement para crear una vista en la base de datos:
DB::statement('CREATE VIEW v_clientes AS SELECT * FROM clientes WHERE activo = 1');
En este ejemplo, se utiliza DB::statement para ejecutar una consulta SQL que crea una vista llamada v_clientes. La vista se crea a partir de la tabla clientes, seleccionando solo las filas donde el campo activo es igual a 1.
En conclusión, DB::statement es una herramienta útil para ejecutar consultas SQL directamente en la base de datos, lo que permite realizar operaciones avanzadas que no están disponibles a través de la API de Eloquent.
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
DB::statement("CREATE OR REPLACE
VIEW `v_productos` AS
SELECT
`p`.`id` AS `id`,
`p`.`nombre` AS `nombre`,
`p`.`descripcion` AS `descripcion`,
`p`.`iva_id` AS `iva_id`,
`p`.`precio` AS `precio`,
`s`.`nombre` AS `subcategoria`,
`c`.`nombre` AS `categoria`,
`m`.`nombre` AS `marca`,
`p`.`imagen` AS `imagen`
FROM
(((`productos` `p`
LEFT JOIN `subcategorias` `s` ON ((`p`.`subcategoria_id` = `s`.`id`)))
JOIN `categorias` `c` ON ((`c`.`id` = `s`.`categoria_id`)))
JOIN `marcas` `m` ON ((`m`.`id` = `p`.`marca_id`))) ORDER BY CATEGORIA,SUBCATEGORIA;");
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
DB::statement('DROP VIEW v_productos;');
}
};
Este código crea una vista llamada v_productos que muestra una lista de productos con su respectiva subcategoría, categoría y marca. Para ello, se utiliza una consulta SQL con múltiples join y se ordena por categoría y subcategoría.
La primera línea de la consulta, CREATE OR REPLACE VIEW 'v_productos' AS, indica que se está creando una vista llamada v_productos.
Dentro del SELECT statement, se seleccionan los campos que se desean mostrar en la vista. Se usan alias para que los nombres sean más descriptivos.
Luego, se utiliza el operador LEFT JOIN para hacer una unión izquierda entre la tabla productos y subcategorias, con el fin de obtener la subcategoría correspondiente de cada producto. Como algunos productos pueden no tener una subcategoría asignada, se utiliza LEFT JOIN en lugar de JOIN para incluir todos los productos, incluso aquellos que no tienen una subcategoría correspondiente.
Posteriormente, se utiliza JOIN para unir la tabla categorias a la tabla subcategorias, de manera que se pueda obtener la categoría correspondiente de cada producto.
Por último, se utiliza otro JOIN para unir la tabla marcas a la tabla productos, con el fin de obtener la marca correspondiente de cada producto.
La vista resultante tiene un conjunto de campos que muestran la información relevante de cada producto, ç junto con su subcategoría, categoría y marca correspondientes. La vista se ordena por categoría y subcategoría para facilitar la visualización de los productos.
Como sería la consulta en Elocuent
Para ejecutar consultas SQL en Laravel utilizando Eloquent, se puede utilizar el método select de la clase DB de Laravel. Por ejemplo, para obtener los campos id, nombre, descripcion, precio, subcategoria, categoria, marca e imagen de la tabla productos y de sus tablas relacionadas subcategorias, categorias y marcas, se puede utilizar el siguiente código en Eloquent:
use Illuminate\Support\Facades\DB;
$productos = DB::table('productos')
->leftJoin('subcategorias', 'productos.subcategoria_id', '=', 'subcategorias.id')
->join('categorias', 'subcategorias.categoria_id', '=', 'categorias.id')
->join('marcas', 'productos.marca_id', '=', 'marcas.id')
->select('productos.id', 'productos.nombre', 'productos.descripcion', 'productos.precio',
'subcategorias.nombre as subcategoria', 'categorias.nombre as categoria',
'marcas.nombre as marca', 'productos.imagen')
->orderBy('categorias.nombre', 'asc')
->orderBy('subcategorias.nombre', 'asc')
->get();
Este código utiliza el método join para unir las tablas productos, subcategorias, categorias y marcas según las claves foráneas. Luego, utiliza el método select para seleccionar los campos necesarios y renombrar algunos campos de las tablas relacionadas utilizando la cláusula as. Finalmente, utiliza el método orderBy para ordenar los resultados por categorias.nombre y subcategorias.nombre, y el método get para obtener los resultados.
Diferencias entre un método y otro
La principal diferencia entre hacerlo con Eloquent y con una vista de base de datos es que en Eloquent, no se crea una vista en la base de datos. En su lugar, se define una función en un modelo que devuelve los datos de la vista. Esto tiene algunas implicaciones:
- No se crea una vista en la base de datos, lo que significa que no se puede acceder directamente a la vista desde otra aplicación que no use Eloquent.
- Al no ser una vista en la base de datos, no se pueden utilizar las funciones específicas de cada motor de base de datos para optimizar el rendimiento de la vista. En cambio, todo el trabajo de filtrado y selección de datos se realiza en la aplicación utilizando Eloquent.
- Sin embargo, Eloquent ofrece una gran cantidad de características que permiten personalizar la consulta de los datos, lo que puede resultar más flexible que trabajar con una vista de base de datos predefinida.
En conclusión, la elección entre utilizar una vista de base de datos o definir una función en Eloquent para obtener los datos depende del caso de uso específico y de las necesidades de la aplicación. Si se necesita una vista específica para ser utilizada por varias aplicaciones o herramientas, entonces una vista de base de datos podría ser la mejor opción. Si se necesita más flexibilidad para personalizar la consulta de datos, o si se quiere evitar el mantenimiento adicional de la vista en la base de datos, entonces definir una función en Eloquent podría ser la mejor opción.
v_ofertas
Este código es una migración de Laravel que crea una vista de base de datos llamada v_ofertas. La vista utiliza la cláusula SELECT para seleccionar los campos que se mostrarán en la vista, incluyendo campos de las tablas v_productos y ofertas. La cláusula JOIN se utiliza para unir las dos tablas, de forma que sólo se muestren los productos que tienen una oferta asociada.
El código utiliza la sintaxis de Laravel para definir la vista. En el método up(), se utiliza la función DB::statement() para ejecutar la consulta SQL que crea la vista. En el método down(), se utiliza la función DB::statement() de nuevo, pero esta vez para eliminar la vista.
Aquí te dejo el código completo:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
DB::statement("CREATE OR REPLACE
VIEW `v_ofertas` AS
SELECT
`v`.`id` AS `id`,
`v`.`nombre` AS `nombre`,
`v`.`descripcion` AS `descripcion`,
`v`.`iva_id` AS `iva_id`,
`v`.`subcategoria` AS `subcategoria`,
`v`.`categoria` AS `categoria`,
`v`.`marca` AS `marca`,
`v`.`imAGEN` AS `imAGEN`,
`o`.`descripcion` AS `descripcio_oferta`,
`o`.`id` AS `id_oferta`
FROM
(`v_productos` `v`
JOIN `ofertas` `o` ON ((`v`.`id` = `o`.`producto_id`)));");
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
DB::statement("DROP VIEW v_ofertas");
}
};
v_proveedores
¡Por supuesto! En este caso, se trata de una migración de Laravel que crea una vista de base de datos llamada v_proveedores. La vista utiliza la cláusula SELECT para seleccionar los campos que se mostrarán en la vista, incluyendo campos de la tabla proveedores y de las tablas poblaciones y provincias. La cláusula JOIN se utiliza para unir las tres tablas y obtener los datos necesarios para la vista.
El código utiliza la sintaxis de Laravel para definir la vista. En el método up(), se utiliza la función DB::statement() para ejecutar la consulta SQL que crea la vista. La consulta utiliza la cláusula INNER JOIN para unir las tablas proveedores, poblaciones y provincias, y la cláusula SELECT para seleccionar los campos que se mostrarán en la vista. En el método down(), se utiliza la función Schema::dropIfExists() para eliminar la vista.
Es importante tener en cuenta que la consulta SQL utilizada en esta migración hace referencia a tablas específicas de una base de datos. Si se desea utilizar este código en otro entorno, puede ser necesario cambiar los nombres de las tablas y campos para que coincidan con la estructura de la base de datos. Aquí está el código completo:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
DB::statement("CREATE OR REPLACE VIEW v_proveedores AS SELECT pr.*,pob.nombre as poblacion,pro.nombre as provincia
FROM comercio.proveedores pr INNER JOIN poblaciones pob ON pr.cod_postal=pob.codigo
INNER JOIN provincias pro ON pro.codigo=pob.provincia_cod;");
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('v_proveedores');
}
};