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

On 12/30/05, Pedro F. [email protected] wrote:

    value           text            not null,
            :class_name => 'Language',

( 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 :wink:


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.

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.

On 1/5/06, Solomon W. [email protected] 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