jOOQ中实现One-to-One关联查询与对象映射的正确姿势

record User(
    UUID id,
    String username,
    String ipAddress,
    long lastJoinAt,
    long createdAt,
    long updatedAt,
    GuildMembersRecord guildMember // 期望关联的GuildMembersRecord对象
) {}

record GuildMembersRecord(
    UUID id,
    UUID guildId,
    UUID userId,
    String role,
    OffsetDateTime updatedAt,
    OffsetDateTime createdAt
) {}

UUID key = UUID.randomUUID(); // 假设这是查询的用户ID
this.context.select(
    USERS.ID,
    USERS.USERNAME,
    USERS.IP_ADDRESS,
    USERS.LAST_JOIN_AT,
    USERS.CREATED_AT,
    USERS.UPDATED_AT,
    // 尝试使用row(Select)映射GuildMembersRecord
    row(
        DSL.select(GUILD_MEMBERS)
        .from(GUILD_MEMBERS)
        .where(GUILD_MEMBERS.USER_ID.eq(key)) // 注意：这里的key应该与外层查询关联
    ).convertFrom(r -> r.into(GUILD_MEMBERS).into(GuildMembersRecord.class))
)
.from(USERS)
.where(USERS.ID.eq(key))
.fetchOne(Records.mapping(User::new));

field(
    select(GUILD_MEMBERS) // 选择整个GUILD_MEMBERS表引用
    .from(GUILD_MEMBERS)
    .where(GUILD_MEMBERS.USER_ID.eq(USERS.ID)) // 关键：关联到外层查询的USERS.ID
)

import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.select;
import static your.package.jooq.tables.Users.USERS; // 假设这是生成的jOOQ表
import static your.package.jooq.tables.GuildMembers.GUILD_MEMBERS; // 假设这是生成的jOOQ表

import java.time.OffsetDateTime;
import java.util.UUID;

// 假设这些是你的Java record定义
record User(
    UUID id,
    String username,
    String ipAddress,
    long lastJoinAt,
    long createdAt,
    long updatedAt,
    GuildMembersRecord guildMember
) {}

record GuildMembersRecord(
    UUID id,
    UUID guildId,
    UUID userId,
    String role,
    OffsetDateTime updatedAt,
    OffsetDateTime createdAt
) {}

public class UserGuildService {

    private final DSLContext context;

    public UserGuildService(DSLContext context) {
        this.context = context;
    }

    public User fetchUserWithGuildMember(UUID userId) {
        return this.context.select(
                USERS.ID,
                USERS.USERNAME,
                USERS.IP_ADDRESS,
                USERS.LAST_JOIN_AT,
                USERS.CREATED_AT,
                USERS.UPDATED_AT,
                // 正确的关联映射方式：使用field(Select)
                field(
                    select(GUILD_MEMBERS) // 投影整个GUILD_MEMBERS表，jOOQ将自动映射为GuildMembersRecord
                    .from(GUILD_MEMBERS)
                    .where(GUILD_MEMBERS.USER_ID.eq(USERS.ID)) // 关联子查询条件
                ).as("guildMember") // 为投影的字段指定别名，与User record的属性名匹配
            )
            .from(USERS)
            .where(USERS.ID.eq(userId))
            .fetchOne(org.jooq.Records.mapping(User::new)); // 使用Records.mapping进行整体映射
    }

    public static void main(String[] args) {
        // 示例用法（需要配置DSLContext）
        // DSLContext dslContext = ...; // 初始化你的jOOQ DSLContext
        // UserGuildService service = new UserGuildService(dslContext);
        // UUID testUserId = UUID.fromString("..."); // 你的测试用户ID
        // User user = service.fetchUserWithGuildMember(testUserId);
        // if (user != null) {
        //     System.out.println("User: " + user.username());
        //     if (user.guildMember() != null) {
        //         System.out.println("Guild Member Role: " + user.guildMember().role());
        //     } else {
        //         System.out.println("No Guild Member found for this user.");
        //     }
        // }
    }
}