同じ商品にLIKEしたユーザーを1名マッチングする

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE items (
    item_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    category VARCHAR(100) NOT NULL,
    deleted_at DATETIME NULL
);

CREATE TABLE user_actions (
    action_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    item_id BIGINT NOT NULL,
    action_type VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL
);

INSERT INTO users (name) VALUES
('山田'),
('佐藤'),
('鈴木'),
('田中');

INSERT INTO items (title, category, deleted_at) VALUES
('Java入門書', 'BOOK', NULL),
('Spring Boot入門書', 'BOOK', NULL),
('MySQL入門書', 'BOOK', NULL),
('Docker入門書', 'BOOK', NULL),
('古い教材', 'BOOK', NOW());

INSERT INTO user_actions (user_id, item_id, action_type, created_at) VALUES
(1, 1, 'LIKE', NOW()),
(1, 3, 'LIKE', NOW()),

(2, 1, 'LIKE', NOW()),
(2, 2, 'LIKE', NOW()),
(2, 3, 'LIKE', NOW()),

(3, 4, 'LIKE', NOW()),

(4, 1, 'LIKE', NOW());

package com.example.demo.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * SuperDao - データベース接続の取得を担当する基底クラス
 */
public class SuperDao {

    private static final String DB_URI =
            "jdbc:mysql://localhost:3306/sip_a?"
            + "characterEncoding=utf8&"
            + "useSSL=false&serverTimezone=GMT%2B9&"
            + "rewriteBatchedStatements=true";

    private static final String DB_USER = "newuser";
    private static final String DB_PASS = "0";

    protected Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URI, DB_USER, DB_PASS);
    }
}

package com.example.demo.model.dto;

public class MatchedUserDto {

    private Long matchedUserId;
    private String matchedUserName;
    private int commonLikeCount;
    private String commonItemTitles;

    public MatchedUserDto() {
    }

    public MatchedUserDto(Long matchedUserId, String matchedUserName, int commonLikeCount, String commonItemTitles) {
        this.matchedUserId = matchedUserId;
        this.matchedUserName = matchedUserName;
        this.commonLikeCount = commonLikeCount;
        this.commonItemTitles = commonItemTitles;
    }

    public Long getMatchedUserId() {
        return matchedUserId;
    }

    public void setMatchedUserId(Long matchedUserId) {
        this.matchedUserId = matchedUserId;
    }

    public String getMatchedUserName() {
        return matchedUserName;
    }

    public void setMatchedUserName(String matchedUserName) {
        this.matchedUserName = matchedUserName;
    }

    public int getCommonLikeCount() {
        return commonLikeCount;
    }

    public void setCommonLikeCount(int commonLikeCount) {
        this.commonLikeCount = commonLikeCount;
    }

    public String getCommonItemTitles() {
        return commonItemTitles;
    }

    public void setCommonItemTitles(String commonItemTitles) {
        this.commonItemTitles = commonItemTitles;
    }

    @Override
    public String toString() {
        return "MatchedUserDto{" +
                "matchedUserId=" + matchedUserId +
                ", matchedUserName='" + matchedUserName + '\'' +
                ", commonLikeCount=" + commonLikeCount +
                ", commonItemTitles='" + commonItemTitles + '\'' +
                '}';
    }
}

package com.example.demo.model.dao;

import com.example.demo.model.dto.MatchedUserDto;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserMatchingDao extends SuperDao {

    public MatchedUserDto findOneMatchedUserByLike(Long targetUserId) throws SQLException {
        String sql =
                "SELECT " +
                "    u.user_id AS matched_user_id, " +
                "    u.name AS matched_user_name, " +
                "    COUNT(DISTINCT my_action.item_id) AS common_like_count, " +
                "    GROUP_CONCAT(DISTINCT i.title ORDER BY i.title SEPARATOR ', ') AS common_item_titles " +
                "FROM user_actions my_action " +
                "JOIN user_actions other_action " +
                "    ON my_action.item_id = other_action.item_id " +
                "    AND other_action.action_type = 'LIKE' " +
                "    AND other_action.user_id <> my_action.user_id " +
                "JOIN users u " +
                "    ON other_action.user_id = u.user_id " +
                "JOIN items i " +
                "    ON my_action.item_id = i.item_id " +
                "WHERE my_action.user_id = ? " +
                "AND my_action.action_type = 'LIKE' " +
                "AND i.deleted_at IS NULL " +
                "GROUP BY " +
                "    u.user_id, " +
                "    u.name " +
                "ORDER BY " +
                "    common_like_count DESC, " +
                "    u.user_id ASC " +
                "LIMIT 1";

        try (
                Connection connection = getConnection();
                PreparedStatement statement = connection.prepareStatement(sql)
        ) {
            statement.setLong(1, targetUserId);

            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    MatchedUserDto dto = new MatchedUserDto();
                    dto.setMatchedUserId(resultSet.getLong("matched_user_id"));
                    dto.setMatchedUserName(resultSet.getString("matched_user_name"));
                    dto.setCommonLikeCount(resultSet.getInt("common_like_count"));
                    dto.setCommonItemTitles(resultSet.getString("common_item_titles"));

                    return dto;
                }
            }
        }

        return null;
    }

    public static void main(String[] args) {
        UserMatchingDao dao = new UserMatchingDao();

        Long targetUserId = 1L;

        System.out.println("LIKEベースのユーザーマッチングを開始します。");
        System.out.println("対象ユーザーID: " + targetUserId);

        try {
            MatchedUserDto matchedUser = dao.findOneMatchedUserByLike(targetUserId);

            if (matchedUser == null) {
                System.out.println("マッチング対象のユーザーは見つかりませんでした。");
                return;
            }

            System.out.println("マッチング対象ユーザー:");
            System.out.println(matchedUser);

        } catch (SQLException e) {
            System.err.println("ユーザーマッチング中にエラーが発生しました。");
            System.err.println("原因: " + e.getMessage());
            e.printStackTrace();
        }

        System.out.println("処理を終了します。");
    }
}

SELECT
    u.user_id AS matched_user_id,
    u.name AS matched_user_name,
    COUNT(DISTINCT my_action.item_id) AS common_like_count,
    GROUP_CONCAT(DISTINCT i.title ORDER BY i.title SEPARATOR ', ') AS common_item_titles
FROM user_actions my_action
JOIN user_actions other_action
    ON my_action.item_id = other_action.item_id
    AND other_action.action_type = 'LIKE'
    AND other_action.user_id <> my_action.user_id
JOIN users u
    ON other_action.user_id = u.user_id
JOIN items i
    ON my_action.item_id = i.item_id
WHERE my_action.user_id = 1
AND my_action.action_type = 'LIKE'
AND i.deleted_at IS NULL
GROUP BY
    u.user_id,
    u.name
ORDER BY
    common_like_count DESC,
    u.user_id ASC
LIMIT 1;

JOIN user_actions other_action
    ON my_action.item_id = other_action.item_id
    AND other_action.action_type = 'LIKE'
    AND other_action.user_id <> my_action.user_id

ORDER BY
    common_like_count DESC,
    u.user_id ASC
LIMIT 1

LIKEベースのユーザーマッチングを開始します。
対象ユーザーID: 1
マッチング対象ユーザー:
MatchedUserDto{matchedUserId=2, matchedUserName='佐藤', commonLikeCount=2, commonItemTitles='Java入門書, MySQL入門書'}
処理を終了します。