Stussa Stussa - 1 month ago 13
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

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).

Comments