How to use "-" on database relations in rails

Question

I have three relations, user, shopping_list and item. Users can have multiple shopping lists and shopping lists hold multiple items. The rule is that a shopping list can only hold one kind of item, like one "tv" for example.

On my webpage i have a spot where the user can select which shopping_list he/she would like to add the item too. The shopping lists appear in a drop down and the user selects it. It works fine but it does not follow the rule mentioned above. I would like to not show the shopping list if it already holds that item but continue to show the rest.

Here are my relations

create_table "shopping_lists", force: true do |t|
    t.integer "user_id"
    t.integer "list_id"
    t.string  "name"
  end 


create_table "item", force: true do |t|
    t.integer "list_id"
    t.integer "item_id"
  end

How do i build my query so that when i am on a page i only get shopping lists that do not already hold that item?

(I thought about adding a user_id to the 'item' relation and then i could use the '-' operator, i'm not sure how that works in rails. do you think that would be the way to go?)

Do you have a field that holds the "type" of item? – Ruby Racer Jun 30 '14 at 05:36 — Ruby Racer, Jun 30 '14 at 05:36

score 1 · Accepted Answer · edited May 23 '17 at 12:11

*Validation**

There are two things to consider:

You could validate your way to unique fields

You could use indexes in your db

The first step is to ensure you're not populating your collection more than you want. You can do this either by validating in the association:

#app/models/list.rb
class List < ActiveRecord::Base
   has_and_belongs_to_many :items, uniq: true
end

#app/models/item.rb
class Item < ActiveRecord::Base
   has_and_belongs_to_many :lists, uniq: true
end

#lists_items
list_id | item_id

or by using indexes in you join table:

#migration
add_index(:lists_items, [:list_id, :item_id], :unique => true)

--

Dropdown

If you have a select box, you'll need to do something like the following (using .not):

collection_select(:item_ids, :item_id, Item.where.not("id IN (?)", current_user.list.map(&:item_id)), :id, :name_with_initial, prompt: true)

Not sure how you'll define the list id's, but this is the foundation of what you need to do

How to use "-" on database relations in rails

1 Answers1