Quick question about joins, etc

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

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 .

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.

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

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.

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
http://www.greencheese.us/ZeekLand <-- NOT a blog!!!

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?

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.)

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs