Pull data from another table in a lookup
Hi all,
I am looking up a list of articles, depending on the brand ID's - @articles = Article.where(brand_id: brand_ids) what i would like to know is there a way to retrieve the brand name in this look up too so i don't have to do it later.
Maybe so it returns the article details + the brand name rather than the brand ID
Many thanks
This may not be the best way, but usually when I do this kind of stuff I use the controller to build an array that has all my data in it that I need for the view. Is there an association between articles and brands? If so you could use includes(:brands)
@articles = Article.where(Brand_id: brand_ids).includes(:brands).map { |article| [article.details, article.brands] }
I'm assuming with the plural 'brands' that an article could have many brands. So in the above example, @articles.first[1] would list all the associated brands with the first article.
I can't seem to get that to work.
Basically @articles = Article.where(brand_id: brand_ids) returns a list of articles which have a brand_id.
What i want to do is use that brand_id to get the name of the brand from the brands table and return it as part of @articles at present i then need to look up the brand id within the article, using
<%= Brand.find(article.brand_id).name %>
and obviously as we get more articles this could have loads of DB calls which is not ideal haha :)
I believe you want @articles = Article.where(brand_id: brand_ids).includes(:brand) which will load that association. Pretty close to what Jacob said, but you need to reference the association in the includes.
Then when you print out the articles, you can say
<% @articles.each do |article| %>
<div>
<%= article.brand.name %>
</div>
<% end %>
This should be efficient because it will make two queries and preload the brand association on each article.
Whoops, looks like I had a few typos in there, sorry about that! I was however thinking the includes needed to be the plural form of the association but looking back at my own project it's indeed singular!
Do what Chris said and you'll be set! :)
haha Cheers guys :) Not to worry Jacob, thanks for your help mate, you made it easier for Chris really ;)
Jacob's the one that did the hard work though. I just had to tweak his answer a little bit. ;) He's the one that deserves cake 🍰.
To clean this up, you may want to create a scope on article which performs all the joins and association loading. Would allow you to clean up the controller a bit and just says Article.method (where method is your AR query. Good practice to try to keep as much code out of the controller as possible, but Chris' answer will definitely get you what you need. I'd just suggest moving this to the model.
I don't agree with that. Code in your controller isn't magically a bad thing. It's only a bad thing when it becomes overly complex. Having one query in your controller is actually a requirement for this method. If you extract it out, you still have to save the query in the controller but now you've hidden away the actual query which actually makes this more complicated with no benefit. Making a method arbitrarily to "clean up the controller" isn't actually a good reason to do this.
A good reason to refactor is if if you are using this query multiple places in the app and want to keep them consistent with each other. In that case, then yes, you do get value from abstracting this because you can change the functionality of each use of it all in one place.
class Article < ActiveRecord::Base
def self.with_brands(brand_ids)
scoped.where(brand_id: brand_ids).includes(:brand)
end
end
# controller
@articles = Article.with_brands(brand_ids)
Alan, check:
http://tomdallimore.com/blog/includes-vs-joins-in-rails-when-and-where
https://gorails.com/blog/activerecord-merge
http://guides.rubyonrails.org/active_record_querying.html#scopes
I believe this should get you down the right rabbit hole. I'd have to dink around with your models to get something more concrete, I can't recall if I've ever done an includes in a scope. If you come up with a working scope please share if you can!
Chris is right, I just have a bad habit of hiding away complex queries into models. I think I wrongly assumed that this query was being used in multiple places as you suggested. Then yes, it's definitely of value to abstract it. But I do have a nasty habit of hiding things in models when I shouldn't. Got to work on that.
So that's how it would be done - thank you for the example Chris!
Here lately I've been refactoring things down like James had suggested in an attempt to better understand how each entity in the MVC framework really works together. Even if it's overkill or unnecessary, I think it's been an incredible learning experience! The hard part is once you know how to abstract/refactor things out, when to do it and when not to do it...
Guys thank you all so much for the extended discussion on this topic. Its really useful, and nice again more learning for me :D.
I have started to use it a lot else where in the site so it could be useful to have some of the lookups like this. I can also use this elsewhere in the app for other lookups.
Chris's example would work really well for me as I am using a similar lookup for users who don't manage the brands, but only follow them. So i could in theory retrieve articles for both there :)
Chris, we really need a like or kudos button you solved my issue, but others provide some great input too so they should get rewarded as well ;)
Chris,
I am trying to get this to include product details too, like we did above but for some reason i can't get it to work :/ could you see what i am missing please?
I am Getting a list the items in a backbar, which works fine, until i add in the includes() bit to get the extra details. I really can't get my head around includes(). lol
@products = BackBar.where(:venue_id => @venue.id).includes(:product)
This @products = BackBar.where(:venue_id => @venue.id) returns a list of items which are in the BackBar table, in there are 2 fields which are referenced venue_id and product_id -- Returns id: 1, venue_id: 1, product_id: 5
class BackBar < ApplicationRecord
belongs_to :product
has_many :venues
end
class Product < ApplicationRecord
has_many :back_bars
end
class Venue < ApplicationRecord
belongs_to :back_bar
end
If I'm not mistaken, your association is wrong. class BackBar would need has_many :products in order to use .includes() like this.
Check out http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations and http://apidock.com/rails/ActiveRecord/QueryMethods/includes for some extra details on includes.
I think you need to fix your association to something more like this:
class BackBar < ApplicationRecord
has_many :products
has_many :venues
end
class Product < ApplicationRecord
end
This would allow you to do something like:
@products = Backbar.where(:venue_id => @venue.id).includes(:product)
or if you just need the products that go with that backbar, then
@products = Backbar.where(:venue_id => @venue.id).products
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_barstable as detailed above. It stores thevenue_idandproduct_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
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! :)
Agreed Jacob!
And yeah you're right about the HABTM table name. The thing about HABTM is that you don't get to ever interact with that table in the middle...so imagine you needed some meta data, like a quantity of the product in each venue...well you're SOL with HABTM. If you have an explicit model as a join table, and do has_many through, then you can add extra columns in like a quantity and do a whole lot more with it. In most cases I find it's better to be explicit about that join table than using HABTM for that reason. Guess I should do an episode on this!
I will go stand in the corner for an hour and hang my head in shame due to my poor naming conventions,
Seriously though cheers guys, and yeah please do some more episodes on Data and this stuff haha
Lol naming can be a major PITA... just last night I spent a few hours just renaming things to be a little more future proof.
If you're able to, take some time to build prototypes and do proof of concepts. This will help clear up some of the naming issues that happen when first starting a project. Often you don't know what really makes sense until much further along in the project but by that time it's too late or a major undertaking to rename.
An episode that digs into the associations would be great! Just last week I finally figured out some of the magic in associations, the moment I realized you can designate your own primary / foreign keys and even name it however you'd like was one of the happiest moments in my life!
class Agent < ActiveRecord::Base
has_many :ar_listed, class_name: 'Abor::AborRetsResi', primary_key: 'agent_id', foreign_key: 'list_agent_mlsid'
end
#this lets me do:
@agent_listed_properties = current_agent.ar_listed
#which returns all the listed properties that agent has! Before, I would have had to do something like:
@agent_listed_properties = Abor::AborRetsResi.where(list_agent_mlsid: current_agent.agent_id)
Of course this may not be the best way to approach this, but seeing how this all works was really eye opening!
Hmm, markdown isn't parsing for me even though it's showing up in the preview just fine... oh well, this is what I was trying to post as my example:
https://gist.github.com/nanosplit/b50241d66947fbcae3a1020d1f94ab8a
Sorry to spam! :)
Yeah naming always is a pain, to me it made sense but I understand your points. ;) will fix in v2 of the app haha what I'm building ATM is still very my a proof of concept to show the idea will work and get people on board
@Jacob, the markdown preview is different than the server side renderer so things are slightly different sometimes.
@Alan, I added a reactions feature so you can give kudos to other people just like on Github issues. :D
might need to look at the subscriber times in the diamond, mine says 3 months but i done 4 payments?
Woah, very clean!
Think you'll be sharing some of the techniques you used? I'm digging this WYSIWYG editor!
Thanks Chris! :)
@Alan, they're actually badges, so you get a badge at 1, 3, 6, 12, and 24 months but only after you cross that threshold. Going to be doing similar things for answering questions and whatnot. :D
@Jacob, yeah for sure! The editor is https://github.com/NextStepWebs/simplemde-markdown-editor which I like quite a bit. It was easy to setup, but unfortunately doesn't provide any way of doing @mentions unfortunately. Guess I may have to build my own editor at some point instead.
@chrris sorry to hijack but the site looks great. love the editor, reactions, series, and more.
Sweet, I'll have to play around with it, I love a good editor! :)
Did you ever mess with Summernote? http://summernote.org/examples/#hint-for-mention
They support mentions out the box it looks like. I use it on a site and have had no real issues so far.
Anywho - thanks again, and great job!!