Select column as true / false if id is exists in another table

Related Projects GRDB
1

Hello Everyone!

I need a little help in solving this problem using GRDB tools

I want to do something like that:

Member.annotated(with: Member.service.exists().forKey("service")).fetchAll(db)

Although there is no problem using raw sql, I want to use GRDB Query Interface.

Sorry If I duplicate another such question, but I can't find a solution in forums.

I tried to use TableAlias and CTE, but I am unable to do it :frowning:

Thanks for any help!

2

Hello @rezuanb,

You can write do it this way:

extension Member {
    static let services = hasMany(Service.self)
}
struct MemberInfo: Decodable, FetchableRecord {
    var member: Member
    var services: Bool
}

// << Solution 1 >>
// SELECT member.*, COUNT(DISTINCT service.id) > 0 AS services
// FROM member
// LEFT JOIN service ON service.memberId = member.id
// GROUP BY member.id
let memberInfos = try Member
    .annotated(with: (Member.services.isEmpty == false).forKey("services"))
    .asRequest(of: MemberInfo.self)
    .fetchAll(db)

You may prefer a more straightforward SQL solution:

// << Solution 2 >>
// SELECT *, EXISTS (
//     SELECT * FROM service
//     WHERE service.memberId = member.id
// ) AS services
// FROM "member"
let memberInfos = try Member
    .annotated(with: [SQL("""
        EXISTS (
          SELECT * FROM service
          WHERE service.memberId = member.id
        ) AS services
        """)])
    .asRequest(of: MemberInfo.self)
    .fetchAll(db)

The SQL version above can also be generated with Swift apis, as below:

// << Solution 3 >>
let memberAlias = TableAlias()
let services = Service.filter(Column("memberId") == memberAlias[Column("id")])
let memberInfos = try Member
    .aliased(memberAlias)
    .annotated(with: services.exists().forKey("services"))
    .asRequest(of: MemberInfo.self)
    .fetchAll(db)

You'll pick your favorite solution. If your database is big, maybe make benchmarks and choose the most efficient one. Otherwise, pick the one that looks the most clear and easy to maintain for you. My personal choice would be the raw SQL one.