Selección con subconsultas de SQL en Eloquent ORM a partir de Laravel 6, parte 2

En la lección anterior intentamos obtener los últimos logins de los usuarios usando para ello relaciones de Eloquent ORM, sin embargo el SQL utilizado no fue el más óptimo. En esta lección utilizaremos el método addSelect y su nueva funcionalidad a partir de Laravel 6 para seleccionar valores de otras consultas (lo que se conoce como subconsultas en SQL) y así obtener datos de la manera más eficiente.

Repositorio

Ver el código de esta lección en GitHub

Aprende más sobre bases de datos y SQL con nuestro libro digital disponible en Leanpub con 40% de descuento por prelanzamiento.

Material Relacionado

Nuestra meta en esta lección es obtener la última fecha de inicio de sesión del usuario, como puedes ver en la URL /usuarios ya hemos logrado nuestro cometido, pero la consulta SQL ejecuta no es óptima puesto que trae todos los logins y no uno solo:

#
select * from `logins` where `logins`.`user_id` in (1, 23, 53, 55, 271, 325, 338, 427, 455, 462, 641, 769, 824, 839, 964) order by `created_at` desc

Esta consulta se produce con la relación hasOne que definí en User:

<?php

public function last_login()
{
    return $this->hasOne(Login::class)->orderByDesc('created_at');
}

Pero un usuario no tiene un solo login sino muchos, por ende obtenemos un SQL no óptimo.

Una forma sencilla de solucionar el problema es con el uso de una subconsulta de SQL.

En la prueba tests/Unit/UserTest.php voy a hacer el siguiente cambio:

<?php

// Antes:
$users = User::all();

// Despues:
$users = User::withLastLogin()->get();

El método withLastLogin será un scope de Eloquent que vamos a definir en el modelo de usuarios:

<?php

public function scopeWithLastLogin($query)
{
        
}

Ahora vamos a utilizar un subselect en conjunto con una relación dinámica:

La subconsulta o subselect nos permitirá obtener el ID del último login, el cual usaremos como una llave foránea para crear una nueva relación «dinámica» en lastLogin:

<?php

//...

public function lastLogin()
{
    return $this->belongsTo(Login::class);
}

public function scopeWithLastLogin(Builder $query) 
{ 
    $subquery = Login::select('logins.id') 
        ->whereColumn('logins.user_id', 'users.id') 
        ->latest() 
        ->limit(1); 

    $query->addSelect(['last_login_id' => $subquery]);

    $query->with('last_login'); 
}

Con estos cambios nuestra «prueba unitaria» o «prueba de integración» debería volver a funcionar como antes: ejecuta vendor/bin/phpunit --filter UserTest

Invierte las barras diagonales si estás en Windows: vendor\bin\phpunit --filter UserTest

Lo que quiere decir que solo nos falta hacer el cambio en el controlador app/Http/Controllers/UserController.php y recargar la página:

<?php

//...
$users = User::query()
    ->with('team', 'skills', 'profile.profession')
    ->withLastLogin()

Ahora podemos obtener la información del último inicio de sesión como si se tratara de una relación belongsTo puesto que hemos agregado el atributo last_login_id a la consulta, el cual contendrá el ID del último login de cada uno de los usuarios seleccionados.

Nota por supuesto que la columna last_login_id no existe en la tabla users y que obtenemos esta información a través de una subconsulta SQL dentro de la consulta que trae la información de los usuarios en la lista:

#
select `users`.*, (select `logins`.`id` from `logins` where `logins`.`user_id` = `users`.`id` order by `created_at` desc limit 1) as `last_login_id` from `users` where `users`.`deleted_at` is null order by `created_at` desc limit 15 offset 0

#
select * from `logins` where `logins`.`id` in (104, 273, 286, 1420, 1722, 1797, 2304, 2462, 2489, 3561, 4266, 4601, 4692, 5394)

Si quieres ir aún más lejos podemos obtener la fecha del último login como si tratase de un atributo del modelo User lo cual nos ahorrará la segunda consulta.

Vamos paso a paso. Comencemos encapsulando la forma de obtener la fecha del último login dentro de un atributo dinámico:

Empezamos cambiando estas dos aserciones de la prueba en tests/Unit/UserTest a lo siguiente:

<?php

$this->assertEquals(Carbon::parse('2019-09-18 16:00:00'), $users->firstWhere('name', 'Joel')->last_login_at);

$this->assertEquals(Carbon::parse('2019-09-15 12:01:00'), $users->firstWhere('name', 'Ellie')->last_login_at);

Nota que cambié ->last_login->created_at por ->last_login_at. Esperemos que falle:

null does not match expected type "object"

Agreguemos el atributo dinámico al modelo de usuario:

<?php

public function getLastLoginAtAttribute()
{
    return optional($this->lastLogin)->created_at;
}

¡Nuestra prueba pasa!

Usemos el nuevo atributo dinámico en la vista:

<!-- users/_row.blade.php -->
<span class="note">Último login: {{ optional($user->last_login_at)->format('d/m/Y H:ia') ?: 'N/A' }}</span>

Nota que he cambiado el código ligeramente porque planeo no usar la relación lastLogin.

Ahora cambiemos la lógica del modelo de usuario:

<?php

    public function scopeWithLastLogin(Builder $query)
    {
        $subquery = Login::select('logins.created_at')
            ->whereColumn('logins.user_id', 'users.id')
            ->orderByDesc('logins.created_at')
            ->limit(1);

        $query->addSelect(['last_login_at' => $subquery]);
    }

    // Elimina el método getLastLoginAtAttribute

Ahora la prueba pasa, sin embargo hay un bug sutil, vamos a revelarlo en UserTest:

<?php
//...
$this->assertInstanceOf(Carbon::class, $users->firstWhere('name', 'Joel')->last_login_at);
$this->assertInstanceOf(Carbon::class, $users->firstWhere('name', 'Ellie')->last_login_at);

¡last_login_at no es una instancia de Carbon! Efectivamente, aunque optional nos protege en la vista hemos perdido las fechas de login de los usuarios. Arreglar esto es muy sencillo, agrega un cast a al modelo de usuario:

<?php

//...

class User extends Authenticatable
{
    //...

    protected $casts = [
        'active' => 'bool',
        'last_login_at' => 'datetime',
    ];

    //...
}

Ahora tanto la prueba unitaria como las pruebas que realices en el navegador deberían tener éxito :)

Recuerda que puedes aprender más sobre SQL con nuestro libro Aprende SQL: con MySQL y MariaDB. Con las bases adecuadas de SQL es mucho más sencillo lograr soluciones como ésta.