What I meant was that your WHERE clause was extraneous, you should get the same results without it. An inner join is like a WHERE clause because it causes the SQL to only return results when the condition matches (which differentiates it from an outer join).
Or I could have just read your SQL query wrong -- I didn't really spend much time parsing it. :) cheers, Bryan On Mon, Dec 19, 2011 at 2:34 PM, Drammy <[email protected]> wrote: > Thanks Bryan, > > There some useful hints there. I don't quite understand why my INNER > JOIN is doing the same work as my WHERE clause though. I could add > the WHERE clause condition to the INNER JOIN but it is a different > condition. > > Did you just mean I could add it to the INNER JOIN? > > > > On Dec 12, 2:22 pm, Bryan Larsen <[email protected]> wrote: >> That works, but here's a few hints. Generally, you specify the >> "where" clause in ":conditions", the select clause in ":select", inner >> joins in ":joins" and outer joins in ":include". (If it doesn't >> matter whether it's inner or outer, use :include, giving Rails more >> flexibility to rearrange the query). 'from' is deduced >> automatically. It's very rare that you'd need to specify the select, >> and Rails has a standard way of doing renaming >> (seehttp://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMet... >> : table aliasing) . The next thing we notice is that your where >> clause is redundant -- your inner join is doing all the work. You >> can't add a :joins condition to the belongs_to, but you can add it to >> the corresponding has_many: >> >> has_many :classifications, :joins => :recognition_types >> >> Should be all you need. >> >> cheers, >> Bryan >> >> >> >> >> >> >> >> On Sun, Dec 11, 2011 at 11:12 AM, Drammy <[email protected]> wrote: >> > I got it working on the belongs_to relationship with... >> >> > belongs_to :classification_value, :conditions => 'classification_id IN >> > ( >> > SELECT >> > c.id >> > FROM >> > classifications c >> > INNER JOIN >> > recognition_types rt ON c.id = rt.classification_id >> > WHERE rt.id >> > = #{self.recognition_type_id} >> > )' >> >> > On Dec 8, 3:02 pm, Bryan Larsen <[email protected]> wrote: >> >> This is the right place. Matt & Kevin must be busy, we haven't heard >> >> from them for a while. They're probably our two most dependable >> >> question answerers. >> >> >> There are a couple of options for you. >> >> >> 1) set the options attribute on your select-one >> >> (http://cookbook.hobocentral.net/api_tag_defs/select-one). You'll >> >> probably have to read the first part of the DRYML guide first to >> >> understand your many options on you to customize the field. If you >> >> get stuck, yell again. >> >> >> 2) use the :conditions option on your belong_to. This is probably >> >> the easier option, but it might require learning a little about SQL >> >> and/or Rails. In other words, what I'm saying is that learning how >> >> to do #1 will probably do you more good going forward. >> >> >> cheers, >> >> Bryan >> >> >> On Thu, Dec 8, 2011 at 9:05 AM, Drammy <[email protected]> wrote: >> >> > Am I asking this question in the wrong place? Should I be asking this >> >> > in the hobo cookbook? >> >> >> > Any help appreciated. even if its RTFM at page... >> >> >> > On Dec 5, 12:35 pm, Drammy <[email protected]> wrote: >> >> >> Hi all, >> >> >> >> Still pretty new to all this so apologies if this is straight forward; >> >> >> I have searched for solutions as it seems quite an easy problem to >> >> >> solve but alas, no joy. >> >> >> >> I have a few models like so... >> >> >> >> Classification -> ClassificationValue... (where Classification >> >> >> has_many ClassificationValues and ClassificationValue belongs_to >> >> >> Classification) >> >> >> RecognitionType -> Recognition... (where RecognitionType has_many >> >> >> Recognitions and Recognition belongs_to RecognitionType) >> >> >> >> Fairly straight forward... There is also another couple of >> >> >> relationships... >> >> >> >> Classification -> RecognitionType (where RecognitionType belongs_to a >> >> >> Classification) >> >> >> >> ClassificationValue -> Recognition (where Recognition belongs_to a >> >> >> ClassificationValue) >> >> >> >> It is the ClassificationValue dropdown box that appears on the >> >> >> Recognition edit page that is causing me grief. >> >> >> >> I want this dropdown box to only have valid values (not all >> >> >> ClassificationValues). By valid values I mean CalssificationValues >> >> >> that belong_to the Classification that belongs_to the Recognition's >> >> >> RecognitionType... >> >> >> >> Any ideas? (hope you followed that!) >> >> >> >> Cheers, >> >> >> Drammy >> >> >> > -- >> >> > You received this message because you are subscribed to the Google >> >> > Groups "Hobo Users" group. >> >> > To post to this group, send email to [email protected]. >> >> > To unsubscribe from this group, send email to >> >> > [email protected]. >> >> > For more options, visit this group >> >> > athttp://groups.google.com/group/hobousers?hl=en. >> >> > -- >> > You received this message because you are subscribed to the Google Groups >> > "Hobo Users" group. >> > To post to this group, send email to [email protected]. >> > To unsubscribe from this group, send email to >> > [email protected]. >> > For more options, visit this group >> > athttp://groups.google.com/group/hobousers?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "Hobo Users" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/hobousers?hl=en. > -- You received this message because you are subscribed to the Google Groups "Hobo Users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/hobousers?hl=en.
