md.md

I am aware that they are slightly different, but I am too lazy to make the one in ActiveRecord do the same thing as Sequel. In general it seems Sequel would take the advantage of subqueries, but ActiveRecord might not.

In this case, it seems Sequel's SQL is faster than the one generated by ActiveRecord.

Sequel model:

class User < Sequel::Model(Sequel.connect(DATABASE_CONFIG))
  one_to_many :issues, :class => class_name(:Question),
    :dataset => lambda{
      # note that eager_graph would mean a join, but
      # eager would only load it via another SQL with in ([id])
      Question.eager(:user).eager_graph(:categories).
        where(:categories => categories_dataset).
        where(:state => 'created').
        where{questions__created_at > Time.now - 86400*2}.
        reverse_order(:questions__created_at).distinct
    }
end

User.first.issues

Generated:

SELECT DISTINCT
  "questions"."id", "questions"."created_at", "questions"."updated_at", "questions"."user_id", "questions"."mentor_id", "questions"."state", "questions"."title", "questions"."body",
  "categories"."id" AS "categories_id", "categories"."created_at" AS "categories_created_at", "categories"."updated_at" AS "categories_updated_at", "categories"."name"

FROM "questions"

LEFT OUTER JOIN "categories_questions"
  ON ("categories_questions"."question_id" = "questions"."id")
LEFT OUTER JOIN "categories"
  ON ("categories"."id" = "categories_questions"."category_id")

WHERE (

  ("questions"."id" IN (
    SELECT "categories_questions"."question_id"
    FROM "categories_questions"
    WHERE (
      ("categories_questions"."category_id" IN
        (
        SELECT "categories"."id"
        FROM "categories"
        INNER JOIN "expertises"
          ON (
            ("expertises"."category_id" = "categories"."id")
          AND
            ("expertises"."user_id" = 1)
          )
        WHERE
          ("categories"."id" IS NOT NULL)
        )
      )
    AND
      ("categories_questions"."question_id" IS NOT NULL)
    )
  )
)
AND
  ("state" = 'created')
AND
  ("questions"."created_at" > '2013-11-11 11:19:20.130656+0000')
)
ORDER BY
  "questions"."created_at" DESC

ActiveRecord model:

user = User.first
Question.includes(:categories, :user).
  where('categories.id' => user.categories).
  where("questions.state = 'created' AND questions.created_at > ?", 2.days.ago).
  order('questions.created_at DESC').uniq

Generated:

user.categories:

SELECT "categories".* FROM "categories" INNER JOIN "expertises" ON "categories"."id" = "expertises"."category_id" WHERE "expertises"."user_id" = 1

And the rest:

SELECT DISTINCT "questions"."id" AS t0_r0, "questions"."title" AS t0_r1, "questions"."body" AS t0_r2, "questions"."user_id" AS t0_r3, "questions"."created_at" AS t0_r4, "questions"."updated_at" AS t0_r5, "questions"."state" AS t0_r6, "questions"."mentor_id" AS t0_r7, "categories"."id" AS t1_r0, "categories"."name" AS t1_r1, "categories"."created_at" AS t1_r2, "categories"."updated_at" AS t1_r3, "users"."id" AS t2_r0, "users"."email" AS t2_r1 FROM "questions" LEFT OUTER JOIN "categories_questions" ON "categories_questions"."question_id" = "questions"."id" LEFT OUTER JOIN "categories" ON "categories"."id" = "categories_questions"."category_id" LEFT OUTER JOIN "users" ON "users"."id" = "questions"."user_id" WHERE "categories"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29) AND (questions.state = 'created' AND questions.created_at > '2013-11-11 11:27:21.725317') ORDER BY questions.created_at DESC