Forum: Ruby on Rails HABTM with finder_sql problem (Rails bug?)

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Pedro F. (Guest)
on 2005-12-30 17:56
(Received via mailing list)
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.
Reginald Braithwaite (Guest)
on 2005-12-30 20:10
(Received via mailing list)
On 12/30/05, Pedro F. <removed_email_address@domain.invalid> 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 ;-)

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

If at first you don't succeed, skydiving is not for you.
Pedro F. (Guest)
on 2006-01-01 07:49
(Received via mailing list)
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.
Solomon W. (Guest)
on 2006-01-06 03:28
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.
Reginald Braithwaite (Guest)
on 2006-01-06 17:42
(Received via mailing list)
On 1/5/06, Solomon W. <removed_email_address@domain.invalid> 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
Jan P. (Guest)
on 2006-01-06 19:24
(Received via mailing list)
This topic is locked and can not be replied to.