Pull data from another table in a lookup

Looking at the association more... if what you're trying to say is that a Venue should only have one BackBar, and a BackBar has many Products, then your models would look like this:

class BackBar < ApplicationRecord
  has_many :products
  belongs_to :venue
end

class Product < ApplicationRecord
  #nothing needed here...
end

class Venue < ApplicationRecord
  has_one :back_bar
end

I believe this would now allow:

@products = @venue.backbar.products

I haven't actually tested this so it may completely blow up the universe as we know it... but I think that's possible :)

Cheers Jacob the second one makes more sense as each venue can only have one backbar.

That's interesting that products doesn't need anything to associate it. Maybe I need to read up more of a few of these concepts.

It would seem thats not quite right, I am getting this error...

PG::UndefinedColumn: ERROR:  column products.back_bar_id does not exist
LINE 1: SELECT  1 AS one FROM "products" WHERE "products"."back_bar_...
                                               ^
: SELECT  1 AS one FROM "products" WHERE "products"."back_bar_id" = $1 LIMIT $2

This is the table for back bar. Basically I am adding a venue_id and product_id when a user adds a product to the back bar.

create_table "back_bars", force: :cascade do |t|
    t.integer  "venue_id"
    t.integer  "product_id"
    t.decimal  "price",               default: "0.0"
    t.integer  "stock_level"
    t.integer  "last_ordered_amount"
    t.datetime "last_ordered_at"
    t.boolean  "active",              default: false
    t.datetime "created_at",                          null: false
    t.datetime "updated_at",                          null: false
    t.index ["product_id"], name: "index_back_bars_on_product_id", using: :btree
    t.index ["venue_id"], name: "index_back_bars_on_venue_id", using: :btree
  end

Look at error message, it tells you the problem:

PG::UndefinedColumn: ERROR: column products.back_bar_id does not exist

When you have a has_many association, the associated table has to have a column that will hold the parents ID. So in this case, you need to add a back_bar_id column to your products table. When you call @venue.backbar.products, it will look at the Product table and try to find any products that has an back_bar_id that matches the BackBar object you're currently working with.

Yeah I got that there was a missing column in the DB but having the 'back_bar_id' on the products table makes no sense.

A backbar can have many products, but belongs to a venue. Thus the backbar references the venue and product as per my previous posts.

I tried to do the same as we did for articles
But it just doesn't want to work and I can't see why

If I'm following correctly, I think your query @venue.backbar.products is what is causing that. If you have a has_many :products on BackBar, then you also need the back_bar_id on the Product table to associate them.

It makes perfect sense if you're saying that a BackBar has_many :products - the back_bar belonging to a venue has nothing to do with the association between the back_bar and the products table.

#get the first BackBar object
@backbar = Backbar.first

#get the products for @backbar
@backbar_products = Product.where(:back_bar_id => @backbar.id)

#this is the same as saying:
@backbar.products
# if you have the has_many :products association as stated above

Maybe I am not explaining it right then.

Basically, this is the journey.

• A user finds a product, they first select the venue and then add it to the BackBar for that venue.
• This gets stored in the back_bars table as detailed above. It stores the venue_id and product_id

What i am trying to do, is similar to the Articles issue i had originally. Where I am getting back a list of items that are associated to a venue_id from the back_bar table. This provides me with a product_id with this I can get back the details of that product to display in a list in on the venue's back bar page.

This is the code i am now using and is working... but as with the articles method i am sure it can be simplified.

(im not sure how to do comments on here)

def show
    //Look up Venue
    @venue = Venue.find(params[:venue_id])

    if !@venue.nil?
      //Venue found look up back bar items
      @products_in_backbar = BackBar.select(:product_id).where(:venue_id => @venue.id)
      @products = Product.where(id: @products_in_backbar)
    else
      //Redirect to Venues page
      redirect_to venues_path
    end
    return
  end

I think I understand why you're both saying I need a back_bar_id on the product table. And i think this is where the confusion is occurring, on my part.

The association I have is theproduct_id on the BackBar table, goes with the id on the Product table. so I guess the Product class 'has_many :back_bars' as thats where the association goes?

Yeah, sounds like your relationship is backwards in the database where a BackBar belongs_to :product, when it should actually be a Product belongs_to :back_bar.

It kind of depends on products in this case. Will a BackBar ever have more than 1 product? And will a product ever belong to multiple back_bars?

If your BackBar will only ever have 1 product associated with it then you can store the product_id in the BackBar table. However, if a single BackBar can have multiple products, then you will have to let the products table store the back_bar_id so that Rails will know what products belong to what back_bars. The whole "Rails Magic" thing really is just letting Rails know which foreign_keys it has to look for in order to retrieve the records.

Just a thought, but would a :through association work better here? Something like:

http://guides.rubyonrails.org/association_basics.html#the-has-many-through-association

class Venue < ActiveRecord::Base
  has_many :back_offices
  has_many :products, through: :back_offices
end

class BackOffice < ActiveRecord::Base
  belongs_to :venue
  belongs_to :product
end

class Product < ActiveRecord::Base
  has_many :back_offices
  has_many :venues, through: :back_offices
end

I have no idea if this would work correctly for you but may be worth tinkering with unless Chris can say definitively one way or another?

Yeah, a product can be part of multiple bar bars, and a back bar can have more than 1 product.

I thought that having a back bar table would make things easier haha. The reason being is each product could have a different price per venue - yeah it's a crazy system i am working on, but fun lol

You may want to check out the has_and_belongs_to_many association

http://guides.rubyonrails.org/association_basics.html#choosing-between-has-many-through-and-has-and-belongs-to-many

class BackOffice < ActiveRecord::Base
  has_and_belongs_to_many :products
end

class Product < ActiveRecord::Base
  has_and_belongs_to_many :back_offices
end

Again, Chris may be able to clear this up more... I haven't had much personal experience with HABTM so there could be some catches I'm unaware of.

Yeah, for a many-to-many relationship, you'll actually need a join table and has_and_belongs_to_many will use that to connect everything. Thats the only way for your backbar to have many products and your products to belong to many backbars.

Hoping this displays right, this is what my table is like (minus the date fields) basically this shows that have 3 products in Venue ID 1 and 1 product with Venue ID 2

id  venue_id    product_id  price   stock_level     
1    1         5             0      NULL    
2    1         1             0      NULL    
3    2         1             0      NULL
4    1         4             0     NULL 

Using this code below i can return a list of items in from the DB and display a list of products that are in a venue's back bar. This works. But i am sure there is a better way to do it.

def show
    # Look up Venue
    @venue = Venue.find(params[:venue_id])

    if !@venue.nil?
      # Venue found look up back bar items
      @products_in_backbar = BackBar.select(:product_id).where(:venue_id => @venue.id)
      @products = Product.where(id: @products_in_backbar)
    else
      #Redirect to Venue page
      redirect_to venues_path
    end
    return
  end

The join table you mention Chris is the back_bar. Maybe I should have called it something else like venue_products i was just trying to keep it with the terminology used in the app :)

Oh I gotcha now! Yeah that makes sense. So then you are correct having both the venue_id and product_id on that table. The naming I believe is what threw you off because you actually need a bunch of records of the BackBar per venue.

class BackBar < ApplicationRecord
  belongs_to :product
  belongs_to :venue
end

class Product < ApplicationRecord
  has_many :back_bars
  has_many :venues, through: :back_bars
end

class Venue < ApplicationRecord
  has_many :back_bars
  has_many :products, through: :back_bars
end

# controller
@products = @venue.products

That worked!! mmm ok i really need to consider my naming of tables in the future i think. Or make it clearer that a table is a join table haha

Thanks again Chris and Jacob for all your help!!

Hah, the devil is in the details... naming can be incredibly important. Your code is telling a story to other developers, so using descriptive names can go a really long way.

I was just reading more into the HABTM associations and they show to use the names of the two associated tables to make the name of the join table... so in your case this would be :venues_products. It's a little weird to say out loud but at least the name instantly tells you that it has something to do with venues and products.

This was an interesting problem, thanks for the challenge Alan, and thanks Chris for the nice solution! :)