Has many "finder_sql" Replacements in Rails 4.2

Question

I have an association that requires multiple joins / user queries. When trying to figure out how to implement this, the finder_sql repeated answer. However, in Rails 4.2 (and later):

ArgumentError: Unknown key :: finder_sql

My connection request is as follows:

 '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 with:

 has_many :tags, through: :articles 

However, if the connection power is large (i.e. the user has thousands of articles, but the system has only a few tags), it requires downloading all 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 it can't seem like this:

 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)

It looks like it is trying to automatically insert user_id tags in tags (and this column exists only in articles). Note. I preload several users, so I cannot use user.tags without other corrections (inserted SQL is what I see using just that!). Thoughts?

Answer 1

While this does not fix your problem directly - if you only need a subset of your data, you can preload it using a subquery:

 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 DB is Postgres specific (due to ARRAY_AGG ), but an equivalent solution probably exists for other databases.

An alternative would be to set the view as a fake connection table (again, database support is required):

 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 (not tested - you might want to install on readonly and remove some of the connections and use if you have the appropriate foreign key constraint settings).

Answer 2

So, I assume that you get an error when trying to access @user.tags , since you have this connection inside user.rb

So, I think what happens when we try to access @user.tags , we try to retrieve the user tags and have these rails search for tags whose user_id matches the current user id provided by the user, since rails accepts the association name as default modelname_id , even if you don’t have user_id , it will try to search in this column and will search (or add WHERE "tags"."user_id" ) regardless of whether you want it or not since the final goal is to search for tags belonging to the current user .

Of course, my answer cannot explain it 100%. Feel free to comment on your thought or, if you find something wrong, let me know.

Answer 3

Short answer

Well, if I understand this correctly, I think I have a solution that just uses the basic ActiveRecord utilities and does not use finder_sql.

May potentially use:

 user.tags.all.distinct 

Or, conversely, in the user model, change the has_many tags to

 has_many :tags, -> {distinct}, through: :articles 

You can create a helper method for the user to get this:

 def distinct_tags self.tags.all.distinct end 

Evidence

From your question, I believe that you have the following scenario:

• A user can have many articles.
• The article belongs to one user.
• Tags can belong to many articles.
• Articles can have many tags.
• You want to get all the individual tags that the user has associated with the articles they created.

With this in mind, I created the following migrations:

 class CreateUsers < ActiveRecord::Migration def change create_table :users do |t| t.string :name, limit: 255 t.timestamps null: false end end end class CreateArticles < ActiveRecord::Migration def change create_table :articles do |t| t.string :name, limit: 255 t.references :user, index: true, null: false t.timestamps null: false end add_foreign_key :articles, :users end end class CreateTags < ActiveRecord::Migration def change create_table :tags do |t| t.string :name, limit: 255 t.timestamps null: false end end end class CreateArticlesTagsJoinTable < ActiveRecord::Migration def change create_table :articles_tags do |t| t.references :article, index: true, null:false t.references :tag, index: true, null: false end add_index :articles_tags, [:tag_id, :article_id], unique: true add_foreign_key :articles_tags, :articles add_foreign_key :articles_tags, :tags end end 

And models:

 class User < ActiveRecord::Base has_many :articles has_many :tags, through: :articles def distinct_tags self.tags.all.distinct end end class Article < ActiveRecord::Base belongs_to :user has_and_belongs_to_many :tags end class Tag < ActiveRecord::Base has_and_belongs_to_many :articles end 

Then run the database with a lot of data:

 10.times do |tagcount| Tag.create(name: "tag #{tagcount+1}") end 5.times do |usercount| user = User.create(name: "user #{usercount+1}") 1000.times do |articlecount| article = Article.new(user: user) 5.times do |tagcount| article.tags << Tag.find(tagcount+usercount+1) end article.save end end 

Finally, in the rails console:

 user = User.find(3) user.distinct_tags 

leads to the following result:

  Tag Load (0.4ms) SELECT DISTINCT `tags`.* FROM `tags` INNER JOIN `articles_tags` ON `tags`.`id` = `articles_tags`.`tag_id` INNER JOIN `articles` ON `articles_tags`.`article_id` = `articles`.`id` WHERE `articles`.`user_id` = 3 => #<ActiveRecord::AssociationRelation [#<Tag id: 3, name: "tag 3", created_at: "2016-10-18 22:00:52", updated_at: "2016-10-18 22:00:52">, #<Tag id: 4, name: "tag 4", created_at: "2016-10-18 22:00:52", updated_at: "2016-10-18 22:00:52">, #<Tag id: 5, name: "tag 5", created_at: "2016-10-18 22:00:52", updated_at: "2016-10-18 22:00:52">, #<Tag id: 6, name: "tag 6", created_at: "2016-10-18 22:00:52", updated_at: "2016-10-18 22:00:52">, #<Tag id: 7, name: "tag 7", created_at: "2016-10-18 22:00:52", updated_at: "2016-10-18 22:00:52">]> 

Answer 4

It may be useful to use eager_load to force ActiveRecord joins. It works like includes(:tags).references(:tags)

Here is the code snippet:

 users.eager_load(:tags).map { |user| user.tag.inspect } # equal to users.includes(:tags).references(:tags).map { |user| user.tag.inspect } 

Where users is an ActiveRecord relationship.

This code will get into the database at least two times:

• Select only user IDs (hopefully not too many)
• Select users with join tags via article_tags avoiding

SELECT * FROM article_tags WHERE article_id IN (1,2,3 ...) - many

Answer 5

You are on the right track with has_many :tags, through: :articles (or even better has_many :tags, -> {distinct}, through: :articles , as Kevin suggests). But you should read a little about includes vs preload vs eager_load . You do it:

 User.preload(:tags).each {|u| ... } 

But you have to do this:

 User.eager_load(:tags).each {|u| ... } 

or that:

 User.includes(:tags).references(:tags).each {|u| ... } 

When I do this, I get this request:

 SELECT "users"."id" AS t0_r0, "tags"."id" AS t1_r0, "tags"."name" AS t1_r1 FROM "users" LEFT OUTER JOIN "articles" ON "articles"."user_id" = "users"."id" LEFT OUTER JOIN "articles_tags" ON "articles_tags"."article_id" = "articles"."id" LEFT OUTER JOIN "tags" ON "tags"."id" = "articles_tags"."tag_id" 

But it's still going to send a lot of redundant material from the database to your application. It will be faster:

 User.eager_load(:tags).distinct.each {|u| ... } 

Donation:

 SELECT DISTINCT "users"."id" AS t0_r0, "tags"."id" AS t1_r0, "tags"."name" AS t1_r1 FROM "users" LEFT OUTER JOIN "articles" ON "articles"."user_id" = "users"."id" LEFT OUTER JOIN "articles_tags" ON "articles_tags"."article_id" = "articles"."id" LEFT OUTER JOIN "tags" ON "tags"."id" = "articles_tags"."tag_id" 

By doing only User.first.tags.map &:name , I join too:

 SELECT DISTINCT "tags".* FROM "tags" INNER JOIN "articles_tags" ON "tags"."id" = "articles_tags"."tag_id" INNER JOIN "articles" ON "articles_tags"."article_id" = "articles"."id" WHERE "articles"."user_id" = ? 

For more information, see this github repo with the rspec test to find out which SQL Rails are using.

Answer 6

There are three possible solutions:

1) Continue to use has_many associations

Fake user_id column by adding it to selected columns.

  class User < ActiveRecord::Base has_many :tags, -> (user) { select(%Q{DISTINCT "tags".*, #{user_id} AS user_id }) .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" end 

2) Add an instance method to the User class

If you use tags only for queries, and you have not used it in connections, you can use this approach:

 class User def tags select(%Q{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" = ?', id) end end 

user.tags now behaves like an association for all practical purposes.

3) OTOH, using EXISTS , can be executed than use different

  class User < ActiveRecord::Base def tags exists_sql = %Q{ SELECT 1 FROM articles, articles_tags WHERE "articles"."user_id" = #{id} AND "articles_tags"."article_id" = "article"."id" AND "articles_tags"."tag_id" = "tags.id" } Tag.where(%Q{ EXISTS ( #{exists_sql} ) }) end end