1

I have a product model with a has_and_belongs_to_many association with tags. I am trying to create a custom query to exclude certain products with a particular tag. With the code below I get the error PG::UndefinedColumn: ERROR: column products.product_id does not exist... If I remove .where.not(products_tags: 'Mouldings') from the @stones query, products will list based on the @products model.

product.rb

class Product < ApplicationRecord
  include PgSearch
  pg_search_scope :search_for,
                  against: %i(name body),
                  associated_against: { variations: :name, categories: :name, tags: :name },
                  using: {
                      tsearch: {
                          any_word: true
                      }
                  }

  has_and_belongs_to_many :categories
  has_and_belongs_to_many :tags
  ...
end

tag.rb

class Tag < ApplicationRecord
  has_and_belongs_to_many :products
end

products_controller.rb

  ...
  def index
        if params[:query]
          @products = Product.search_for(params[:query])
          @stones = @products.where.not(products_tags: 'Mouldings')
        else
          ...
        end
  end
  ...
Charles Smith
  • 3,201
  • 4
  • 36
  • 80

2 Answers2

1

Try include or joins.

For example, if include the tag table, and then query:

      @products = Product.search_for(params[:query]).includes(:tag)
      @stones = @products.where('tag.name != ?', 'Mouldings')
gwalshington
  • 1,418
  • 2
  • 30
  • 60
  • I tried both and I get this error `PG::UndefinedColumn: ERROR: column products.tag does not exist` - being noobish, I think I should of mentioned that the association between products and tags is done through a join table `products_tags` – Charles Smith Mar 19 '18 at 00:25
  • When you call a join table, such as products_tags - it is looking for an `id` unless you specificy another attribute. The issue here is that youre passing in a string, instead of an id. What is the name of the attribute on the tags table, that holds the value `Mouldings`. That is what you need to query for. – gwalshington Mar 19 '18 at 13:53
  • the attribute on the tags table is `name` - I have been trying everything and running into brick walls... – Charles Smith Mar 19 '18 at 17:19
  • I updated the answer - you need to call the attribute you're trying to query. In this case, `tags.name` not just `tags` – gwalshington Mar 19 '18 at 17:45
  • I am getting close. Currently getting error `PG::UndefinedTable: ERROR: missing FROM-clause entry for table "tags"` then I found this post https://stackoverflow.com/questions/25099668/pgundefinedtable-error-missing-from-clause-entry-for-table-when-using-joins so I updated my code to `@stones = Product.includes(:tags).where('tags.name = ?', 'Mouldings').references(:tags)` which displays only products with `Mouldings` tag but errors out when I use `where.not` – Charles Smith Mar 19 '18 at 18:14
  • Great! Just do `.where('tags.name != ?', 'Mouldings')` – gwalshington Mar 19 '18 at 19:22
0

I could not find a solution to my problem (which is similar). Here is what I did:

My Models

class Allergy < ApplicationRecord
   has_and_belongs_to_many :items
end

class Item < ApplicationRecord
   has_and_belongs_to_many :allergies
end

Given a list of allergies, I want to find the items that do not have those allergies. Example:

allergies = ['dairy', 'eggs']

So I did was create two scopes, it makes the code more understandable (and useful):

class Item < ApplicationRecord
  has_and_belongs_to_many :allergies

  scope :with_allergy, ->(n) { includes(:allergies).where(allergies: {name: n}) }

  scope :free_of, ->(a) { self.all.where.not(id: self.with_allergy(a)) }
end

# I can pass one allergy
Item.free_of('dairy')

# I can pass multiple allergies
Item.free_of(['dairy', 'eggs'])

You can change 'name' to any attribute you want (including id).

I had to create the two scopes because the following:

Item.includes(:allergies).where.not(allergies: {name: n})

Did not return items that are not associated with any allergies. Instead, it returns records that have at least one association.

Possible solution for Rails 6.1>

Rails 6.1 added a .missing method that returns all records that do not have any association.

Item.missing(:allergies) # returns items that do not have any associated allergies.

The latter can be combined with where clauses.

Source: https://boringrails.com/tips/activerecord-where-missing-associations

David
  • 97
  • 9