Stussa Stussa - 11 months ago 65
Ruby Question

Has Many 'finder_sql' Replacement in Rails 4.2

I've got an association that needs a few joins / custom queries. When trying to figure out how to implement this the repeated response is

finder_sql
. However in Rails 4.2 (and above):


ArgumentError: Unknown key: :finder_sql


My query to do the join looks like this:

'SELECT DISTINCT "tags".*' \
' FROM "tags"' \
' JOIN "articles_tags" ON "articles_tags"."tag_id" = "tags"."id"' \
' JOIN "articles" ON "article_tags"."article_id" = "articles"."id"' \
' WHERE articles"."user_id" = #{id}'


I understand that this can be achieved via:

has_many :tags, through: :articles


However if the cardinality of the join is large (i.e. a user has thousands of articles - but the system only has a few tags) it requires loading all the articles / tags:

SELECT * FROM articles WHERE user_id IN (1,2,...)
SELECT * FROM article_tags WHERE article_id IN (1,2,3...) -- a lot
SELECT * FROM tags WHERE id IN (1,2,3) -- a few


And of course also curious about the general case.

Note: also tried using the proc syntax but can't seem to figure that out:

has_many :tags, -> (user) {
select('DISTINCT "tags".*')
.joins('JOIN "articles_tags" ON "articles_tags"."tag_id" = "tags"."id"')
.joins('JOIN "articles" ON "article_tags"."article_id" = "articles"."id"')
.where('"articles"."user_id" = ?', user.id)
}, class_name: "Tag"



ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column tags.user_id does not exist

SELECT DISTINCT "tags".* FROM "tags" JOIN "articles_tags" ON "articles_tags"."tag_id" = "tags"."id" JOIN "articles" ON "article_tags"."article_id" = "articles"."id" WHERE "tags"."user_id" = $1 AND ("articles"."user_id" = 1)


That is it looks like it is trying to inject the
user_id
onto tags automatically (and that column only exists on articles). Note: I'm preloading for multiple users so can't use
user.tags
without other fixes (the SQL pasted is what I'm seeing using exactly that!). Thoughts?

Answer Source

While this doesn't fix your problem directly - if you only need a subset of your data you can potentially preload it via a subselect:

users = User.select('"users".*"').select('COALESCE((SELECT ARRAY_AGG(DISTINCT "tags"."name") ... WHERE "articles"."user_id" = "users"."id"), '{}') AS tag_names')
users.each do |user|
  puts user[:tag_names].join(' ')
end

The above is DB specific for Postgres (due to ARRAY_AGG) but an equivalent solution probably exists for other databases.

An alternative option might be to setup a view as a fake join table (again requires database support):

CREATE OR REPLACE VIEW tags_users AS (
  SELECT 
    "users"."id" AS "user_id", 
    "tags"."id" AS "tag_id"
  FROM "users"
    JOIN "articles" ON "users"."id" = "articles"."user_id"
    JOIN "articles_tags" ON "articles"."id" = "articles_tags"."article_id"
    JOIN "tags" ON "articles_tags"."tag_id" = "tags"."id"
  GROUP BY "user_id", "tag_id"
)

Then you can use has_and_belongs_to_many :tags (haven't tested - may want to set to readonly and can remove some of the joins and use if you have proper foreign key constraints setup).