# Quick question about joins, etc **URL:** \ **Category:** Rails\ **Created:** [November 24, 2006, 8:40am UTC](https://www.ruby-forum.com/t/quick-question-about-joins-etc/77104 "2006-11-24T08:40:47Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![smc7000](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@smc7000](https://www.ruby-forum.com/u/smc7000)\ **Post date:** [November 24, 2006, 8:40am UTC](https://www.ruby-forum.com/t/quick-question-about-joins-etc/77104/1 "2006-11-24T08:40:47Z")
I’ve got something I’ve never tried before - I have three tables set up for a many-to-many data relationship in my rails project and I want to get a bunch of data from one table based on the table farthest removed from it. Example: Three tables: places, venueclasses, and places\_venueclasses. “Places” holds venue data. Name of the joint, phone number, hours, etc. “Venueclasses” holds types of venues. “Bar”, “restaurant”, “club”, etc. “Places\_venueclasses” is the bridge between them and only consists of two columns, “place\_id” and “venueclass\_id”. My controller will receive two pieces of information from the view: the venueclass id and a letter of the alphabet. For now all I want to figure out is how to get all the “places” that are affiliated with that venueclass id, but eventually I’m going to have to find a way to return only those that start with the supplied letter. So if anybody knows how to do both, you rock the figurative house. But if you only know how to do the first, that’s cool too and probably enough for now anyway since I’m slow at this. I tried doing it with a join, but I can’t get the syntax right, even after going to the API. So then I tried doing it in baby steps, but it turns out I can’t do a “find(:all, :conditions =\> “something = #{x}”)” when “x” is an array and not a single value (since I would get an array from the second table with which to search the third). So now I’m REALLY stuck and have absolutely no clue where to go from here. TIA sean ---
**Author:** ![smc7000](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@smc7000](https://www.ruby-forum.com/u/smc7000)\ **Post date:** [November 24, 2006, 9:19am UTC](https://www.ruby-forum.com/t/quick-question-about-joins-etc/77104/2 "2006-11-24T09:19:04Z")
uhm whats that array containing? try sth. like venue = VenueClasses.find(params[:id]) places = venue.places.find :all, :conditions =\> [“name = ?”,params[:letter]] though i’m just guessing as your explanation about what to find and what parameters you exactly get was not that precise . ---
**Author:** ![smc7000](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@smc7000](https://www.ruby-forum.com/u/smc7000)\ **Post date:** [November 24, 2006, 9:37am UTC](https://www.ruby-forum.com/t/quick-question-about-joins-etc/77104/3 "2006-11-24T09:37:59Z")
> though i’m just guessing as your explanation about what to find and > what parameters you exactly get was not that precise . Hmm. Guess you’re right. I’ll try again: Received parameters: the type of venue (whoops - made a mistake on this one in the first post - sorry!) and a letter of the alphabet. What I need to find: All of the places that are associated with that venue type. For example, “Place A” can be both a bar and a restaurant, so it is associated with both. “Place B” is just a restaurant. So if I’m looking for restaurants, both will be returned, but if I’m looking for bars, only Place A will come up. After I get that info, I’ll need to select only the hits that begin with the chosen letter. Where I need to get it from: The tables go: Venueclasses -\> Places\_venueclasses -\> Places. So I need to start by getting the type of venue (bar, restaurant, whatever), then find the id for that type. Then I need to use that id in the second table to collect the id’s of every place that is associated with that id (the type of venue id). Then, after I’ve collected all those ids, I need to go into the last table and match those ids (the place ids) to the rows that have the same id, so I can access all the information for each place, like name, telephone number, etc. So it’s like I’m Mario hopping across two data toadstools to get to the third. The letter thing is after. ---
**Author:** ![smc7000](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@smc7000](https://www.ruby-forum.com/u/smc7000)\ **Post date:** [November 25, 2006, 1:46pm UTC](https://www.ruby-forum.com/t/quick-question-about-joins-etc/77104/4 "2006-11-25T13:46:00Z")
OK. Almost got it figured out. Just have a couple of questions: In this: venueid = VenueClasses.find( :first, :conditions =\> [type = ?,params[:type]]).id What does “type = ?,params[:type]” do? The question mark, especially, I’ve never seen before and I can’t find an explanation in the API. Is it some special operator that means that Rails looks for something similar to, but not exactly, “params[:type]” ? Maybe? Also, the use of :include - from the API i got the impression that you needed to use :joins first. So i guess I don’t understand it yet. Actually, this is my first time making a join in Rails. Is this the right way to do it? Thanks! sean ---
**Author:** ![smc7000](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@smc7000](https://www.ruby-forum.com/u/smc7000)\ **Post date:** [November 24, 2006, 10:51am UTC](https://www.ruby-forum.com/t/quick-question-about-joins-etc/77104/5 "2006-11-24T10:51:16Z")
Assumptions: 1. parameter holding the Venue Type is params[:type] 2. cloumn in venue\_classes tabel holding the type i named “type” 3. Letter for searching the Place names is in params[:place] 4. Relations: VenueClasses has\_and\_belongs\_to\_many :places Places has\_and\_belongs\_to\_many :venue\_classes #prepare the Place letter for wildcard-matching in a LIKE statement placename = params[:place] + ‘%’ #get the venue id for the given type venueid = VenueClasses.find( :first, :conditions =\> [type = ?,params[:type]]).id #get the venue with all the places in a collection @venue = VenuesClasses.find :first, :conditions =\> [“venue\_classes.id = ? AND places.name LIKE”,venueid,placename], :include =\> :places then you should be able to read all atrributes of venue, and all matching places through @venue.places Note: i’m wondering if one could also directly put the search for the type in the second find, without first finding the ID, like this: @venue = VenuesClasses.find :first, :conditions =\> [“venue\_classes.type = ? AND places.name LIKE”,params[:type],placename], :include =\> :places though i’m not so sure about it because of the special habtm stuff with the join table oh and yeah, my code could very possibly contain typos or other errors, maybe it points in the right direction at least. ---
**Author:** ![smc7000](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@smc7000](https://www.ruby-forum.com/u/smc7000)\ **Post date:** [November 25, 2006, 5:05pm UTC](https://www.ruby-forum.com/t/quick-question-about-joins-etc/77104/6 "2006-11-25T17:05:18Z")
Sean C. wrote: > In this: > venueid = VenueClasses.find( :first, :conditions =\> [type = > ?,params[:type]]).id > > What does “type = ?,params[:type]” do? That’s a typo for this: [" type =\> ? ", params[:type] ] The ? just means to substitute in the next thing in the array. (And _nobody_ here should be writing a Rails app without /Agile Web Development with Rails/ by the Daves. Don’t let the “Agile” term spook you; they really mean “the easy way” there.) This also works: [" type =\> :type ", params] Because then ActiveRecord treats the next item in the array as a Hash, and keys it with :type. > Also, the use of :include - from the API i got the impression that you > needed to use :joins first. So i guess I don’t understand it yet. If you mean a method takes two symbols, like this: foo( :include =\> 42, :joins =\> ‘bar’ ) The method foo() might not use those arguments in that order. It might fetch the :joins key first. > Actually, this is my first time making a join in Rails. Is this the > right way to do it? No, you should use the has\_many directives, so they will take care of the verbose details for you. – Phlip [Redirecting...](http://www.greencheese.us/ZeekLand) ← NOT a blog!!! ---
**Author:** ![smc7000](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@smc7000](https://www.ruby-forum.com/u/smc7000)\ **Post date:** [November 28, 2006, 2:26am UTC](https://www.ruby-forum.com/t/quick-question-about-joins-etc/77104/7 "2006-11-28T02:26:29Z")
GOT IT! Here’s what I ended up with: letter = params[:letter] + “%” searchtype = Venueclass.find\_by\_name(params[:searchtype]) @step1results = searchtype.places.find(:all, :conditions =\> [“places.nameen LIKE ?”, letter]) And that was it! I’m almost wary because it looks too simple, but it works. Does anybody see anything wrong with this? ---
**Author:** ![smc7000](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@smc7000](https://www.ruby-forum.com/u/smc7000)\ **Post date:** [November 27, 2006, 8:07am UTC](https://www.ruby-forum.com/t/quick-question-about-joins-etc/77104/8 "2006-11-27T08:07:22Z")
Thanks! I went through the AWDWR book, and this is what I came up with (and worked!): searchtype = Venueclass.find\_by\_name(params[:searchtype]) @step1results = searchtype.places Awesome. I can’t believe it was that easy. Now I have to screen out all of the results that don’t start with a certain letter, say “B”. Would it be easier to do this with an if clause on the view, something like: for result in @step1results if result.placename.firstletter = @letter \<%= stuff %\> end end or would it be better not to have to load up every record first, by only loading the ones that match the letter in the controller, like: @step1results = searchtype.places.firstletter? @letter (note: I’m guessing at some of these function names. Still more pseudocode than real code.)