0

I have books and tags.

def Book < ApplicationRecord
  has_and_belongs_to_many :tags
end

def Tag < ApplicationRecord
  has_and_belongs_to_many :books
end

I want to find all books that do not have the tag with the id 1 associated. (They may have no tags.) I tried this:

Book.includes(:tags).where.not(tags: { id: 1 })

This query finds all books without tags, books that have other tags and books that have the unwanted tag and at least one other tag associated to them.

How can I filter all books with the certain tag? Thanks for any ideas!

Railsana
  • 1,813
  • 2
  • 21
  • 30
  • Do you mean, that you need to find books that have at least one tag from array of tags with id 1/2/3? Or do you want to obtain books that have all tags at once 1/2/3? Please describe in more details – AntonTkachov May 03 '18 at 17:09
  • Thanks for your quick answer! I want to get all books that don't have a certain tag. – Railsana May 03 '18 at 17:18

2 Answers2

2

app/models/books_tag.rb

class BooksTag < ApplicationRecord
  belongs_to :book
  belongs_to :tag
end

Solution:

Book.where.not(id:
  BooksTag.where(tag_id: 1).select(:book_id)
)
  • Similar to Anton's answer, I couldn't think of just a one query solution.
  • I added the books_tag model above so that we won't need to do a JOIN SQL query.
Jay-Ar Polidario
  • 6,463
  • 14
  • 28
  • `BookTags` :), you can get the Book ids with a pluck: `BooksTag.where.not(tag_id: 1).pluck(:book_id).uniq` – iGian May 03 '18 at 18:20
  • @Gian using `pluck()` will fetch the values from the DB immediately, and return the values as an `array of values` in ruby. However, using `.select()` above doesn't immediately fetch the values from the DB, and that that `.select()` only just still builds up the "query string". This means only one SQL string gets executed (although that string has a subquery), as opposed to using `pluck` which would need two SQL executions (This is assuming your solution is still missing the last step which is `Book.where(id: thebookidsofyourcode)` because I assume the OP wants to receive `Book` records :) – Jay-Ar Polidario May 03 '18 at 18:27
  • Yes I agree, you got my up! – iGian May 03 '18 at 18:31
  • Haha just my two cents. But yours should work too! \\(^-^)/ – Jay-Ar Polidario May 03 '18 at 18:32
  • 1
    Thank you! This is my final code: ```Book.where.not(id: Book.joins(:tags).where(tags: {id: 1}))``` – Railsana May 03 '18 at 21:20
0

Quick solution is to go with 2 requests. Firstly you get all book ids that have associated tag, then you select all other books.

unwanted_book_ids = Books.joins(:tags).where(tags: { id: 1 }).pluck('books.id').uniq
@books = Book.where.not(id: unwanted_book_ids)

I am not sure that it's the best solution, but, to be honest, I can't find any trivial one without subqueries/unions/etc

AntonTkachov
  • 1,784
  • 1
  • 10
  • 29
  • @Railsana Have this helped you? You've been provided with several answers, that should work in 95% of cases, but there is no feedback from you at all :( – AntonTkachov May 03 '18 at 20:23
  • Thank you! I marked the best answer as solution. It works within one call to the database. – Railsana May 04 '18 at 11:21
  • Yes, Jay-Ar Polidario solution is better. I like it, too. But, just you should know... it doesn't work with a one query. In his comment he mentioned, that it's 2 queries – AntonTkachov May 04 '18 at 12:37
  • I can see the query in the terminal, it's really just one. ;) – Railsana May 05 '18 at 17:13