boolean subquery in sequel

Question

I am using the ruby sequel gem as my ORM on a project and I want to use the following query to load a sequel model:

SELECT "subject", "addresses", "personal", "read_by", "folder", "created_at", "updated_at", "sent_at", "draft",
--HOW DO I DO THIS IN Sequel?
EXISTS(SELECT COUNT(id) FROM "todos" WHERE reference_email_id = "emails"."id") as "todos" 
FROM "emails" 
INNER JOIN "email_participants" ON ("email_participants"."email_id" = "emails"."id") WHERE (("user_id" = 124))

In the above query is this subquery that returns a boolean result:

EXISTS(SELECT COUNT(id) FROM "todos" WHERE reference_email_id = "emails"."id") as "todos" 
FROM "emails"

At the moment my ruby code looks like this for selecting everything but the boolean expression outlined above:

Email.inner_join(:email_participants, {email_id: :id})
                          .where(user_id: query.user_id)
                          .select_all(:emails)

How can I get sequel to include this EXISTS subquery expression?

score 1 · Answer 1 · answered Nov 13 '13 at 16:15

I am writing this from my memory so forgive me if it does not work directly as it is. The idea is however to use select_append to add to the selected columns.

Email.inner_join(:email_participants, {email_id: :id})
  .where(user_id: query.user_id)
  .select_all(:emails)
  .select_append {
    Todo.where(reference_email_id: {emails: :id})
      .count(:id)
      .exists
      .as(:todos)
  }

boolean subquery in sequel

1 Answers1