Feeling like I''m spamming this list today. :( Sorry for all the questions. So, I''m making a search form where people can search for Houses. On the form, they can check all Neighborhoods that they want to search Houses in. Each House belongs_to a Neighborhood. What''s the best way to search for all houses that are in the selected Neighborhoods? I tried to start by using find_by_sql, but that got ugly fast. Anyone got a better suggestion? Thanks, Joe
On Wed, 15 Dec 2004 08:06:43 -0800, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Each House belongs_to a Neighborhood. What''s the best way to search > for all houses that are in the selected Neighborhoods?Assuming that you use a ''has_many :houses'' inside Neighborhood, you could just instantiate each selected neighborhood and combine all of their ''houses'' attributes into a single array: @houses = [] @params[''neghborhood_ids''].each do |nid| @houses << Neighborhood.find(nid).houses end @houses.flatten! You may be tempted to point out that this could have an impact on performance due to the number of SQL commands generated---but it''s probably the simplest solution, so I wouldn''t worry about performance until you determine that this will really cause a problem. Just my 2-cents. -- Regards, John Wilger ----------- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don''t know," Alice answered. "Then," said the cat, "it doesn''t matter." - Lewis Carrol, Alice in Wonderland
On Wed, 15 Dec 2004 12:33:54 -0500, John Wilger <johnwilger-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Wed, 15 Dec 2004 08:06:43 -0800, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Each House belongs_to a Neighborhood. What''s the best way to search > > for all houses that are in the selected Neighborhoods? > > Assuming that you use a ''has_many :houses'' inside Neighborhood, you > could just instantiate each selected neighborhood and combine all of > their ''houses'' attributes into a single array: > > @houses = [] > @params[''neghborhood_ids''].each do |nid| > @houses << Neighborhood.find(nid).houses > end > @houses.flatten! > > You may be tempted to point out that this could have an impact on > performance due to the number of SQL commands generated---but it''s > probably the simplest solution, so I wouldn''t worry about performance > until you determine that this will really cause a problem. Just my > 2-cents. > > -- > Regards, > John WilgerNot a bad idea. In my case, the user can also search for price ranges, various amenities (amenity = hardwood floor, for example), mininum bedrooms/bathrooms, etc. Is there a good "Rails" way of combining all the search criterion? In the past, I just generated a big old SQL statement from the @params, but I''d love to hear of a better way.
On Wed, 15 Dec 2004, Joe Van Dyk wrote:> On Wed, 15 Dec 2004 12:33:54 -0500, John Wilger <johnwilger-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > On Wed, 15 Dec 2004 08:06:43 -0800, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > Each House belongs_to a Neighborhood. What''s the best way to search > > > for all houses that are in the selected Neighborhoods? > > In my case, the user can also search for price ranges, various > amenities (amenity = hardwood floor, for example), mininum > bedrooms/bathrooms, etc. > > Is there a good "Rails" way of combining all the search criterion?"Criteria." (Sorry. :-)) I know I''m nagging by this point, but this is a beautiful example of where stretching the relational model out of the database and into Ruby, rather than limiting yourself to the typical hierarchial model for the OO representation, would make life easier. The suggested solution was:> > ...you could just instantiate each selected neighborhood and combine all > > of their ''houses'' attributes into a single array: > > > > @houses = [] > > @params[''neghborhood_ids''].each do |nid| > > @houses << Neighborhood.find(nid).houses > > end > > @houses.flatten!But we already know we have a natural join on houses and neighborhoods, and through either the foreign key relationships or, if it comes to that, manually typing in ''has_many :houses'' in Neighborhood, Ruby can figure this out too. So we should be able to ask Ruby directly to give us all of the houses in a set of neighborhoods and it should be able to figure out how to join things up and, ideally, do an optimal join that would be just one SQL query. Instead of the above code, what about something like this: neighborhood_id_restriction DB::Restriction::In(:neighborhood_id, @params[''neighborhood_ids'']) houses_and_neighborhoods Neighborhood.restrict(neighborhood_id_restriction).join(House) Now houses_and_neighborhoods is a relation representing this query: SELECT * FROM neighborhood NATURAL JOIN house WHERE neighborhood_id IN (@params[''neighborhood_ids'']) You have two choices at this point. You can just iterate through it: houses_and_neighborhods.each { |house_combined_with_neighbourhood_object| ... } getting objects which respond to the methods on both house and neighborhood, or you could restrict further: hardwood_floor = DB::Restriction::Equal(''hardwood floor'') houses_and_neighbourhoods.join(Ammenity.restrict(hardwood_floor)).each { |neighbourhood_house_ammenity| ... } Oh, did someone mention efficiency? In a well optimized system, the SQL query will not happen until you hit the ''each'' method. So there''d be no more of this, "oops, that web page just did eight hundred separate queries to the DB" thing. If I can ever get out of PHP/MySQL legacy system hell, I will write the code to do this myself. cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA
On 16.12.2004, at 18:30, Curt Sampson wrote:> > Instead of the above code, what about something like this: > > neighborhood_id_restriction > DB::Restriction::In(:neighborhood_id, @params[''neighborhood_ids'']) > houses_and_neighborhoods > Neighborhood.restrict(neighborhood_id_restriction).join(House) >Curt, This sounds truly awesome! If you find a way to say Neighborhood.restrict(neighborhood_id_restriction).join(House).join(Amme nity.restrict(hardwood_floor)) and get that executed as a single sql multiple join statement, I will put your picture in a frame on my wall (just there next to David''s). If not, I will just continue writing custom sql queries for such joins... //jarkko -- Jarkko Laine http://jlaine.net _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On Thu, 16 Dec 2004 20:23:31 +0200, Jarkko Laine <jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org> wrote:> > On 16.12.2004, at 18:30, Curt Sampson wrote: > > > > Instead of the above code, what about something like this: > > > > neighborhood_id_restriction > > DB::Restriction::In(:neighborhood_id, @params[''neighborhood_ids'']) > > houses_and_neighborhoods > > Neighborhood.restrict(neighborhood_id_restriction).join(House) > > > > Curt, > > This sounds truly awesome! If you find a way to say > Neighborhood.restrict(neighborhood_id_restriction).join(House).join(Amme > nity.restrict(hardwood_floor)) and get that executed as a single sql > multiple join statement, I will put your picture in a frame on my wall > (just there next to David''s). If not, I will just continue writing > custom sql queries for such joins... > > //jarkko >Thanks for the feedback everyone. So, in my case, I should probably just create a big ol'' SQL statement? Thanks, Joe
On Thu, 16 Dec 2004, Joe Van Dyk wrote:> On Thu, 16 Dec 2004 20:23:31 +0200, Jarkko Laine <jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org> wrote: > > > This sounds truly awesome! If you find a way.... I will put your > > picture in a frame on my wall (just there next to David''s).Ooo! Incentive!> Thanks for the feedback everyone. > So, in my case, I should probably just create a big ol'' SQL statement?Yes, that''s typically my method at the moment. Back when I was doing Java, my main strategy was this: 1. Create domain objects that represented the business model as it should look. Most of these were pretty light-weight, often close to or actually data holders. One of the big reasons for doing this was that I started with a legacy database that was a complete mess, and had to find some way of dealing with it while refactoring it. 2. Create "builders" whose job was to provide an SQL query and a method to create a collection of objects from the results of that query. This was probably the most successful part of that system; you could create a new builder in a matter of minutes becuase it had just the SQL statement itself and a bit of code to read a result set row and return an object. The rest of the stuff was taken care of by the framework. 3. Create transaction scripts to do the database updates. Given that stuff was coming in on web forms, there was not any need to update just individual fields (you just passed in everything from the form to the DBMS; what changed, changed, what didn''t, didn''t) so there weren''t so many of these. This also helped to deal with really complex things such as creating a new customer and doing fulfillment for his initial product. (That one was so complex I actually had a separate assembler class which you would create and then invoke various methods on to tell it what you wanted it to create, and then you''d say "go" and it would deal.) This is sort of the antithesis of the Rails approach in that it''s oriented toward making the DBMS do most of the work. It also works best with sort of a "single transaction" approach, where you retrieve a bunch of stuff from the DB, operate on it, do some sort of update, and then throw everything away. But that fits in quite nicely with the way the web works. And when you''ve got a bunch of folks updating stuff on a site simultaneously, it''s important that you not cache stuff so that you don''t wipe out other changes. (I reckoned that a DBMS already has excellent transaction management, so why rewrite any of it in my application code?) cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA