Rails - Jul 2006 - help!

Hi,

i''m fairly new to ruby on rails, and i know the question at hand may 
seem simple at first, but i''m getting really caught up in trying to 
understand this and the docs aren''t giving me answer. i actually
thought
i knew how the sql commands work (like :include, :conditions, :etc) but 
obviously, i don''t.

there are three models M1, M2, M3 joined by a habtam relashinship of

M1 has_and_belongs_to_many M2
M2 has_and_belongs_to_many M1
M2 has_and_belongs_to_many M3
M3 has_and_belongs_to_many M2

and a MyController which is the backbone of the site. (although i 
haven''t specified exactly {model :M1} or something like that in the 
controller, but as far as i know, that shouldn''t change anything [?]).

in MyController i have an action that selects rows from M1 with an 
:include => ''M2s'' and that changes the sql output to
"outer left
join...etc" which is great. . . but when i want to add a :conditions 
clause, i find that it doesn''t add the conditon correctly into the
outer
left join sql command, and it ends up giving me an sql syntax error!
If i try to use the :conditions clause alone (without :include) , i get 
no error, which is great.
:)


but how do i get the two together?? isn''t it possible to put a 
:condition on a select[habtam] action? am i missing something entirely, 
or is there something small i am missing? maybe i need to define 
something in the controller regarding the three models, or ...???

i appreciate any help...
thanks.

harper




-- 
Posted via http://www.ruby-forum.com/.

harper wrote:
> Hi,
> in MyController i have an action that selects rows from M1 with an 
> :include => ''M2s'' and that changes the sql output to
"outer left
> join...etc" which is great. . . but when i want to add a :conditions 
> clause, i find that it doesn''t add the conditon correctly into the
outer
> left join sql command, and it ends up giving me an sql syntax error!
> If i try to use the :conditions clause alone (without :include) , i get 
> no error, which is great.
> :)
Hi Harper.  This all sounds incredibly complicated, and perhaps there''s
something I''m missing in your question that requires that complexity, 
but here''s the simple case.

Where foo has_and_belongs_to_many :bars you create the bollowing tables:

foos (id, name, etc)
bars (id, description, etc)
bars_foos (bar_id, foo_id)

Here, bars_foos is the ''join table'' which implements the habtm
relationship.


from a given foo, you can ask for bars.first.description, or iterate 
across it like

bars.each{ |b| b.description }

That all works out of the box.  All you need is the habtm declaration 
and the tables...no magic sql.

Since you have three entity table and two habmt relationships, you need 
to join tables.  I''ll call your models Alpha, Beta and Gamma.

alphas (id, blah, blah )
betas (id, blah, blah )
gammas (id, blah, blah )

alphas_betas(alpha_id, beta_id)
betas_gammas(beta_id, gamma_id)

And declare the habtms in the appropriate places.

Does this help at all ?

Alan

-- 
Posted via http://www.ruby-forum.com/.

Hi Alan,

first of all firsts, thank you for your detailed answer and a piece of 
your time; it''s greatly appreciated.
so, the deal is that i have done all of the above, and the habtam 
relashinship works great between the three models. i can append a  alpha 
into the beta.alphas array, and a beta into the gama.betas array, and 
yatta yatta. everything works like a charm.
the problem i am having is trying to select Alpah.find(:all, :include => 
betas, :conditions => beta_id in (1,3,a,b,5..) AND ##this is the 
important part where i fail### title LIKE ?", #{beta.title})
for some very annoying reason, i can''t use a condition statement, 
becuase the outputted sql command is not in the right clause (a ''where 
blah = blah'' and ''outer left join'' habtam
relashinships).
i found this a sec ago:

"...The problem that eager loading does not honor conditions and 
ordering
is a long known one, but dealing with it seems
extremely convoluted as I see it (to do it properly you really need a
PROPER database that doesn''t die on you when
you do nested SELECTs)." (= i.e, there''s no way to combine a
habtam
relashinship ''find'' statement with a
''conditions''. don''t know why, but
that''s the deal)

do you know any other way to combine a :conditons and an :include 
statemnt under the same ''find'' action(when under a habtam
relashinship)?
maybe i got it all wrong, and there is some stupid mistake. if i was 
told clear-cut, that there shouldn''t be a problem, i''d go and
try to do
it again , however i can''t find anyone who will give me a clear answer;
and if i CAN''T combine the two statements together, well, how do i ?

again, your help is much much much,
much, appreciated.

thanks.
harper




-- 
Posted via http://www.ruby-forum.com/.

harper wrote:

Hi Harper,
> first of all firsts, thank you for your detailed answer and a piece of 
> your time; it''s greatly appreciated.
Absolutely no problem.
> do you know any other way to combine a :conditons and an :include 
> statemnt under the same ''find'' action(when under a habtam
relashinship)?
> maybe i got it all wrong, and there is some stupid mistake. if i was 
> told clear-cut, that there shouldn''t be a problem, i''d go
and try to do
> it again , however i can''t find anyone who will give me a clear
answer;
> and if i CAN''T combine the two statements together, well, how do i
?
I''m afraid I''m out of my depth.  My Rails knowledge is broad
but shallow
:-)  I''ll try and do a bit of digging in parallel.

At the risk of appearing to dodge the question, do you *need* the eager 
loading ?  Have you seen a performance problem ?  Hopefully it doesn''t 
look too much like dodging the question, as I started with an honest "I 
have no idea" :-)

A.

-- 
Posted via http://www.ruby-forum.com/.

harper wrote:

"Also have in mind that since the eager loading is pulling from multiple 
tables, you?ll have to disambiguate any column references in both 
conditions and orders. So :order => "posts.id DESC" will work while
:order => "id DESC" will not. This may require that you alter the
:order
and :conditions on the association definitions themselves."

perhaps you need to specify the tables in the :conditions ?

:conditions => "alphas.beta_id in (1,3,a,b,5..) AND betas.title LIKE
?",
#{beta.title})"

A.
p.s. why restrict beta on both id and title at the same time ?  If you 
know the beta_id, do you need the title ?
p.p.s Any chance you could give us the real model names, it''ll sit 
easier in the brain.


-- 
Posted via http://www.ruby-forum.com/.

I see you resolved this in another thread :-)

Alan

-- 
Posted via http://www.ruby-forum.com/.

yea...
i totaly didn''t follow what was going on in this thread...although now 
that i''ve found out, you should see the smile behind the computer 
screen.
thanks for the concern!

harp

-- 
Posted via http://www.ruby-forum.com/.