InnerJoin en Mysql Laravel y Adonis

El tema de JOINSes probablemente una de las características mas poderosas que usan los Gestores de Bases de datos relacionales, pues con ellos podemos extraer información desde 2 hasta n cantidad de tablas según lo necesitemos; siempre y cuando estas mismas estén vinculadas entre si idealmente por llaves foráneas.

Teniendo disponibles las siguientes:

1. Join
2. InnerJoin
3. LeftJoin
4. RightJoin

Para este ejercicio usaremos InnerJoin ya que nos ayuda a encontrar registros que tienen coincidencias en ambas tablas, ya que el objetivo que perseguimos es encontrar todos los posts escritos por el usuario con el id número3

Con base en lo anterior, es importante saber que el paso fundamental es dominar esta habilidad en el lenguaje nativo y de ahí partir a el campo de los frameworks.

Para este ejemplo lo veremos aplicado desde 3 frentes distintos:

1. Laravel Framework 5.6
2. Adonis Framework 4.1
3. MySQL Server 5.7

Nuestro primer paso será crear las tablas que compodrán a la micro base de datos para utilizar de ejemplo; en la cual tendremos almacenados:

1. Los usuarios que pueden publicar 
2. Los posts que son publicados

CREATE TABLE usuarios(
   id INT PRIMARY KEY AUTO_INCREMENT,
   username VARCHAR(100) NOT NULL UNIQUE,
   status_user TINYINT(1) NOT NULL
)ENGINE=INNODB;

Después creamos la tablas de Posts

CREATE TABLE posts(
   id INT PRIMARY KEY AUTO_INCREMENT,
   title VARCHAR(100) NOT NULL UNIQUE,
   body VARCHAR(100) NOT NULL,
   user_id INT NOT NULL,
   CONSTRAINT fk_posts_users FOREIGN KEY(user_id) REFERENCES usuarios(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB;

Una vez hecho lo anterior, la necesidad es mostrar todos los posts o publicaciones que están asociados a un usuario por su id

Caso 1: Eloquent Laravel

Una característica que puedes observar en el siguiente ejemplo es que aunque decido solo usar el método JOIN, Eloquent por defecto lo traducirá a innerJoin como la mejor opción posible (esto lo puedes notar si usas por ejemplo el package Laravel DebugBar, que ayuda a visualizar las sentencias ejecutadas)

$posts = Posts::
    join('usuarios', 'usuarios.id', '=', 'posts.user_id')
    ->select('posts.title', 'usuarios.username')
    ->where('usuarios.id', '=', 3)
    ->get();

Ahora si no quieres usar la estructura propia de Eloquent, bien lo puedes realizar de la siguiente forma

Opción 2. Uso de funciones anónimas y del método on en Eloquent

$posts = Posts::select('posts.title', 'usuarios.username')
    ->from('posts')
    ->join('usuarios', function($query){
    $query->on('usuarios.id', '=', 'posts.user_id')
})->where('usuarios.id', '=', 3)->get();

Caso 2. QueryBuilder Adonis Framework

Adonis Framework esta construido siguiente el patrón de arquitectura de software MVC, siguiendo una estructura y sintáxis muy parecida a Laravel; se encyuentra en su versión 4.1

Como puedes notar en el ejemplo siguiente accedo al constructor de consultas invocando al objeto Database al inicio del Controlador donde lo ocuparemos, después como puedes también observar cuando pasamos el objeto query lo hacemos con la estructura de las arrow functions pues Adonis implementa las últimas características del estándar.

const Database = use('Database')

const posts = await Database
    .table('posts')
    .innerJoin('usuarios', (query) => {
    query.on('usuarios.id', 'posts.user_id')
}).where('usuarios.id', 3)

Ahora bien llegamos a que esas secuencias de código, en su estado mas puro se pueden traducir por el siguiente SQL y significan prácticamente lo mismo

Caso 3. SQL Puro (Sintáxis MySQL)

SELECT title, username FROM posts
   INNER JOIN usuarios
   ON usuarios.id = posts.user_id
WHERE usuarios.id = 3;

Como puedes notar la sintáxis es bastante amigable tanto en SQL puro como así mismo en los dos frameworks que sirven de ejemplo; mostrando todo el poder que se puede llegar a tener usándolos, ya que crear sentencias SQL es realmente simple pues tanto con el ORM de cada uno (Laravel->Eloquent) y (Adonis->Lucid) estas mismas sentencias se construyen con una orientación a objetos

Recuerda EDTeam tiene un curso genial para partir desde cero en el campo de las bases de datos y llegar aún mas a fondo de este breve ejercicio

Curso EDTeam Bases de Datos desde Cero

Saludos