LaravelのSQL副問合せの覚書

概要

前段としてユーザーテーブルとユーザーログインテーブルが存在する

論理名:ユーザー

CREATE TABLE USERS (
    ID INT PRIMARY KEY,
    NAME VARCHAR(100),
    EMAIL VARCHAR(50),
    ROLE INT,
    CREATED DATETIME
);

論理名:ログイン履歴

CREATE TABLE USER_LOGIN (
    ID INT PRIMARY KEY,
    USER_ID INT,
    IP_ADDRESS VARCHAR(50),
    LOGIN_DATE DATETIME
);

ユーザーがログインをすると都度ログイン履歴にカラムが作成される。結果は1行ではなくログインした分だけ生成される。
USERSテーブルのデータに最終ログインだけ加えたデータを取得したい。となった場合に生SQLで書くと

生SQL

SELECT
    USERS.ID,
    USERS.NAME,
    USERS.EMAIL,
    USERS.ROLE
    (SELECT
        USER_LOGIN.LOGIN_DATE
    FROM
        USER_LOGIN
    WHERE
        USER_LOGIN.USER_ID = USERS.ID
    ORDER BY
        USER_LOGIN.LOGIN_DATE DESC) as LOGIN_DATE
    Limit 0, 1
FROM
    USERS

と言う形で、副問合せとしてUSER_LOGINテーブルのUSER_IDで結合した最新のログイン情報を取得することで達成できる。
しかし生SQLおじさんではなく、これをLaravelで実現する場合にどうするか考えた。

Models/User.php

取得ロジック

public function getUserWithLastestLogin(){
    return $this->select(
                'USERS.ID',
                'USERS.NAME',
                'USERS.EMAIL',
                'USERS.ROLE',
                DB::raw('(
                    SELECT
                        LOGIN_DATE
                    FROM
                        USER_LOGIN 
                    WHERE
                        USER_LOGIN.USER_ID = USERS.ID 
                    ORDER BY
                        LOGIN_DATE DESC 
                    LIMIT 0, 1
                ) AS LOGIN_DATE')
            )->get();
}

と言う形でまとめる事ができた。
https://readouble.com/laravel/11.x/ja/queries.html
リファレンスを確認すると
先にログイン情報をGroupByで取得してからユーザーでjoinSubを使う事もできるが変数として先にログイン履歴を取得した後にユーザーとJoinすると書くと結構長くなるなと感じた。
この辺はお作法次第になるが、副問合せ自体はrawで単一問い合わせを組み合わせられる事が分かったので収穫となった。
もっと良い書き方を探して明日からも探索。