How do I dynamically chain where clauses in rails?

Question

I have a Videos model and a Tags model. Tags HABTM Videos and Videos HABTM Tags.

class Tag < ApplicationRecord
  has_and_belongs_to_many :videos
  scope :with_tag, -> (id) { where(id: id) }
end

class Video < ApplicationRecord
  has_and_belongs_to_many :tags
end

I want to know how to create a query where an unknown number of Tags would come in from the user. So I can say, "show me all the videos that are tagged with (123 AND 124 AND 125)". The ultimate goal being that I don't know how many tags the user is going to pass in, so I need to know how to create a chain of scopes and ultimately be able to pass the entire thing into ransack to do a text search on some fields of the resulting videos. How can I accomplish this?

UPDATE: I want to know how to do this with Rails and ActiveRecord. I know how I can accomplish this with SQL. But accomplishing it only with SQL will not allow me to pass the resulting relation onto ransack.

Are you gonna store the tags that come from the user, right? — Pavan, Sep 12 '18 at 15:16
@EricLubow This is the best approach.. https://stackoverflow.com/a/11512925/2767755 — Arup Rakshit, Sep 12 '18 at 15:19
@Pavan it has a join table, stored there as video_id and tag_id. — Arup Rakshit, Sep 12 '18 at 15:19
@ArupRakshit I'm asking how to accomplish this in Active Record. I will update the question. I know how to do it in SQL. — Eric Lubow, Sep 12 '18 at 15:22
@ArupRakshit I meant OP can save the collection of ids of tags that come from a user to a variable and can use it construct a query. — Pavan, Sep 12 '18 at 15:25

score 2 · Accepted Answer · answered Sep 12 '18 at 16:29

I have created some related tables to write this answer, and test the query against the data the table has. So, I am here fetching the posts, which has tag 1 and 2 both.

IN SQL:

rails-test_development=# select id from posts;
 id
----
  1
  2
  3
(3 rows)

rails-test_development=# select id from tags;
 id
----
  2
  3
  4
  5
  6
(5 rows)

rails-test_development=# select post_id, tag_id from posts_tags;
 post_id | tag_id
---------+--------
       1 |      2
       2 |      3
       2 |      2
       2 |      1
       3 |      1
       3 |      2
       1 |      4
(7 rows)

rails-test_development=# WITH posts_tags_cte AS (
rails-test_development(#         SELECT post_id, array_agg(tag_id) as tags
rails-test_development(#         FROM posts_tags
rails-test_development(#         WHERE tag_id in (1, 2)
rails-test_development(#         GROUP BY post_id
rails-test_development(# )
rails-test_development-# SELECT posts.id FROM posts_tags_cte JOIN posts ON posts.id = posts_tags_cte.post_id
rails-test_development-# WHERE posts_tags_cte.tags @> array[1, 2]::int8[];
 id
----
  3
  2
(2 rows)

Now in Rails:

rails-test$ rails c
Running via Spring preloader in process 7361
Loading development environment (Rails 5.2.1)
2.5.1 :001 > Post.by_tag([1,2]).first.attributes
  Post Load (1.7ms)  SELECT  "posts".* FROM     (WITH posts_tags_cte AS (
      SELECT post_id, array_agg(tag_id) as tags
      FROM posts_tags
      WHERE tag_id in (1,2)
      GROUP BY post_id
    )
    SELECT posts.* FROM posts_tags_cte JOIN posts ON posts.id = posts_tags_cte.post_id
    WHERE posts_tags_cte.tags @> array[1,2]::int8[]) as posts
 ORDER BY "posts"."id" ASC LIMIT $1  [["LIMIT", 1]]
 => {"id"=>2, "name"=>"Postppp", "title"=>"Post", "content"=>"I don't know", "created_at"=>Sun, 09 Sep 2018 09:48:33 UTC +00:00, "updated_at"=>Sun, 09 Sep 2018 09:48:33 UTC +00:00, "month_and_year"=>Sun, 09 Sep 2018}
2.5.1 :002 > Post.by_tag([1,2]).last.attributes
  Post Load (1.3ms)  SELECT  "posts".* FROM     (WITH posts_tags_cte AS (
      SELECT post_id, array_agg(tag_id) as tags
      FROM posts_tags
      WHERE tag_id in (1,2)
      GROUP BY post_id
    )
    SELECT posts.* FROM posts_tags_cte JOIN posts ON posts.id = posts_tags_cte.post_id
    WHERE posts_tags_cte.tags @> array[1,2]::int8[]) as posts
 ORDER BY "posts"."id" DESC LIMIT $1  [["LIMIT", 1]]
 => {"id"=>3, "name"=>"Post A", "title"=>"Post title", "content"=>"Lorem Ipsum is simply dummy text of the printing and typesetting industry.", "created_at"=>Sun, 09 Sep 2018 09:52:57 UTC +00:00, "updated_at"=>Sun, 09 Sep 2018 09:52:57 UTC +00:00, "month_and_year"=>Sun, 09 Sep 2018}
2.5.1 :003 >

And I defined the scope as:

class Post < ApplicationRecord
  has_and_belongs_to_many :tags

  scope :by_tag, -> (tag_ids) {

    sql = sanitize_sql_array [<<-SQL, tag_ids, tag_ids]
    (WITH posts_tags_cte AS (
      SELECT post_id, array_agg(tag_id) as tags
      FROM posts_tags
      WHERE tag_id in (?)
      GROUP BY post_id
    )
    SELECT posts.* FROM posts_tags_cte JOIN posts ON posts.id = posts_tags_cte.post_id
    WHERE posts_tags_cte.tags @> array[?]::int8[]) as posts
    SQL

    from(sql)
  }
end

I took help from this answer.

Thank you for including the scoping here. Without the scoping, the following `.ransack()` call wouldn't work the way I needed it to. By doing this via a scope, even with the SQL, it made the `.ransack()` call work. — Eric Lubow, Sep 12 '18 at 17:31

How do I dynamically chain where clauses in rails?

1 Answers1

IN SQL:

Now in Rails:

Linked