Rails - Dec 2005 - HABTM with finder_sql problem (Rails bug?)

I''m building an app that needs i18n support across the entire database
(i.e. localized attributes). In order to do this I''ve created a
special HABTM join table that can be associated with _any_ other
table:

create table language_strings (
	for_table	varchar(255)	not null,
	foreign_id	int		not null,
	language_id	varchar(5)	not null,
	attr_name	varchar(255)	not null,
	value		text		not null,
	primary key (for_table, foreign_id, language_id)
);

Notice the for_table and foreign_id columns. These two are used to
identify the table and row to which each record belongs.

To make this work I then add something like this to each models that needs it:

class Property < ActiveRecord::Base
	has_and_belongs_to_many :names,
		:class_name => ''Language'',
		:join_table => ''language_strings'',
		:foreign_key => ''foreign_id'',
		:finder_sql =>
			"SELECT language_id AS id, value " +
			"FROM language_strings " +
			"WHERE for_table = ''properties'' AND " +
				''foreign_id = #{id} AND '' +
				"attr_name = ''name''"
end

( With pretty highlighting: http://rafb.net/paste/results/n5aV2A60.html )

Ok, now the problem. The above code does work, however, after fetching
the first Property all succesive ones issue the _exact_ same query
(the one from finder_sql), meaning it doesn''t change the id (in
''foreign_id = #{id} AND '') and keeps bringing the same values.

A script/console example:
>> p1 = Property.find 1
>> p1.names
=> [#<Language:0xa742c4f4 @attributes={"id"=>"en",
"value"=>"Color"}>]
>> p2 = Property.find 2
>> p2.names
=> [#<Language:0xa742828c @attributes={"id"=>"en",
"value"=>"Color"}>]

In the last line "value" should be "Size", not
"Color".

Checking the logs I noticed it keeps using id = 1 ever after the first query.

I really need this to work, but have run out of ideas. Any gurus out
there willing to help? Could this be a bug in Rails or some cache
feature I''m missing?

Thanks in advance,

Pedro Fayolle

On 12/30/05, Pedro Fayolle
<pfayolle-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I''m building an app that needs i18n support across the entire
database
> (i.e. localized attributes). In order to do this I''ve created a
> special HABTM join table that can be associated with _any_ other
> table:
>
> create table language_strings (
>         for_table       varchar(255)    not null,
>         foreign_id      int             not null,
>         language_id     varchar(5)      not null,
>         attr_name       varchar(255)    not null,
>         value           text            not null,
>         primary key (for_table, foreign_id, language_id)
> );
>
> Notice the for_table and foreign_id columns. These two are used to
> identify the table and row to which each record belongs.
>
> To make this work I then add something like this to each models that needs
it:
>
> class Property < ActiveRecord::Base
>         has_and_belongs_to_many :names,
>                 :class_name => ''Language'',
>                 :join_table => ''language_strings'',
>                 :foreign_key => ''foreign_id'',
>                 :finder_sql =>
>                         "SELECT language_id AS id, value " +
>                         "FROM language_strings " +
>                         "WHERE for_table =
''properties'' AND " +
>                                 ''foreign_id = #{id} AND ''
+
>                                 "attr_name =
''name''"
> end
>
> ( With pretty highlighting: http://rafb.net/paste/results/n5aV2A60.html )
My WAG (wild-assed guess) is your use of double quites. Even though
the #{id} section is within single quotes, you''ve chosen to catenate
these strings together with "+" and Ruby may be turning the whole
thing into a string that is resolved early.

I would try it using single quotes and I would also use a multi-line
string literal rather than catenation. That way you know exactly what
you are getting:

               :finder_sql =>
                       ''SELECT language_id AS id, value
                        FROM language_strings
                        WHERE for_table = \''properties\'' AND
                                foreign_id = #{id} AND
                                attr_name = \''name\''''

As a side effect, your code can no longer be confused with Java ;-)

--
Reginald Braithwaite
http://www.braithwaite-lee.com/weblog/

If at first you don''t succeed, skydiving is not for you.

Thanks a lot for your help, but unfortunately that wasn''t the problem.
I did, however, manage to solve it with the use of :conditions instead
of :finder_sql, which also resulted in much cleaner code. I do still
wonder what the problem was.

On 30/12/05, Reginald Braithwaite <raganwald@gmail.com>
wrote:
> On 12/30/05, Pedro Fayolle <pfayolle@gmail.com> wrote:
> > I''m building an app that needs i18n support across the entire
database
> > (i.e. localized attributes). In order to do this I''ve created
a
> > special HABTM join table that can be associated with _any_ other
> > table:
> >
> > create table language_strings (
> >         for_table       varchar(255)    not null,
> >         foreign_id      int             not null,
> >         language_id     varchar(5)      not null,
> >         attr_name       varchar(255)    not null,
> >         value           text            not null,
> >         primary key (for_table, foreign_id, language_id)
> > );
> >
> > Notice the for_table and foreign_id columns. These two are used to
> > identify the table and row to which each record belongs.
> >
> > To make this work I then add something like this to each models that
needs it:
> >
> > class Property < ActiveRecord::Base
> >         has_and_belongs_to_many :names,
> >                 :class_name => ''Language'',
> >                 :join_table =>
''language_strings'',
> >                 :foreign_key => ''foreign_id'',
> >                 :finder_sql =>
> >                         "SELECT language_id AS id, value " +
> >                         "FROM language_strings " +
> >                         "WHERE for_table =
''properties'' AND " +
> >                                 ''foreign_id = #{id} AND
'' +
> >                                 "attr_name =
''name''"
> > end
> >
> > ( With pretty highlighting:
http://rafb.net/paste/results/n5aV2A60.html )
>
> My WAG (wild-assed guess) is your use of double quites. Even though
> the #{id} section is within single quotes, you''ve chosen to
catenate
> these strings together with "+" and Ruby may be turning the whole
> thing into a string that is resolved early.
>
> I would try it using single quotes and I would also use a multi-line
> string literal rather than catenation. That way you know exactly what
> you are getting:
>
>                :finder_sql =>
>                        ''SELECT language_id AS id, value
>                         FROM language_strings
>                         WHERE for_table = \''properties\''
AND
>                                 foreign_id = #{id} AND
>                                 attr_name =
\''name\''''
>
> As a side effect, your code can no longer be confused with Java ;-)
>
> --
> Reginald Braithwaite
> http://www.braithwaite-lee.com/weblog/
>
> If at first you don''t succeed, skydiving is not for you.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Reginald Braithwaite wrote:
> I would try it using single quotes and I would also use a multi-line
> string literal rather than catenation. That way you know exactly what
Nope, I''m having the same issue, with no double-quotes in sight.  I
created some test cases that illustrate this issue last July and
submitted them to the rails team.  I didn''t create a formal defect
ticket at the time, but seeing this post prompted me to.  I''ve
submitted
ticket #3403 to the rails trac.

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

On 1/5/06, Solomon White <solo@gatelys.com> wrote:
> Reginald Braithwaite wrote:
> > I would try it using single quotes and I would also use a multi-line
> > string literal rather than catenation. That way you know exactly what
>
> Nope, I''m having the same issue, with no double-quotes in sight. 
I
> created some test cases that illustrate this issue last July and
> submitted them to the rails team.  I didn''t create a formal defect
> ticket at the time, but seeing this post prompted me to.  I''ve
submitted
> ticket #3403 to the rails trac.
Interesting. Can you share your test cases? Is there a way to view the
ticket on line?

--
Reginald Braithwaite
http://www.braithwaite-lee.com/weblog/

"Real power today belongs to people who have knowledge, who know how
to use it to do things. The rest are hiding behind an illusion. We
were willing to do whatever we needed to do in order to learn what we
wanted to learn. That''s the kind of passion the world needs and the
world respects." ~Se7en

Hi,

http://dev.rubyonrails.org/ticket/3403

Regards
Jan

Reginald Braithwaite wrote:
>On 1/5/06, Solomon White <solo@gatelys.com> wrote:
>  
>
>>Reginald Braithwaite wrote:
>>    
>>
>>>I would try it using single quotes and I would also use a multi-line
>>>string literal rather than catenation. That way you know exactly
what
>>>      
>>>
>>Nope, I''m having the same issue, with no double-quotes in
sight.  I
>>created some test cases that illustrate this issue last July and
>>submitted them to the rails team.  I didn''t create a formal
defect
>>ticket at the time, but seeing this post prompted me to.  I''ve
submitted
>>ticket #3403 to the rails trac.
>>    
>>
>
>Interesting. Can you share your test cases? Is there a way to view the
>ticket on line?
>
>--
>Reginald Braithwaite
>http://www.braithwaite-lee.com/weblog/
>
>"Real power today belongs to people who have knowledge, who know how
>to use it to do things. The rest are hiding behind an illusion. We
>were willing to do whatever we needed to do in order to learn what we
>wanted to learn. That''s the kind of passion the world needs and the
>world respects." ~Se7en
>_______________________________________________
>Rails mailing list
>Rails@lists.rubyonrails.org
>http://lists.rubyonrails.org/mailman/listinfo/rails
>
>  
>