Rails 2.0.2 - quoted_table_name problem

Rails
1

Hello,

I am trying to upgrade existing Rails codebase from Rails 1.2.5 to
Rails 2.0.2. My db is mysql.

I ran into a “table not found” error after upgrading in one of the test
cases which works perfectly under 1.2.5.

So this assert statement gives the following error :

assert_equal(43, QciQuestion.find(:all,
:joins => "as qq inner join qcis as qc on qq.qci_id = qc.id ",
:conditions => [ “qc.unit_id = ?”, 1]).size, “Wrong number of
resident interview questions loaded.”)

Error

ActiveRecord::StatementInvalid: Mysql::Error: #42S02Unknown table
‘qci_questions’: SELECT qci_questions.* FROM qci_questions as qq
inner join qcis as qc on qq.qci_id = qc.id WHERE (qc.unit_id = 1)

So, I was debugging and ran into the “construct_finder_sql” method in
ActiveRecord. This method adds a quoted table name in front of the “.*”
if there a “join” option is specified. Apparently this is what causing
the Unknown table error above.

Rails 2.0.2 ActiveRecord::Base

def construct_finder_sql(options)
scope = scope(:find)
sql = "SELECT #{(scope && scope[:select]) || options[:select]
|| (options[:joins] && quoted_table_name + ‘.') || '’} "
sql << "FROM #{(scope && scope[:from]) || options[:from] ||
quoted_table_name} "

…
…
end

Rails 1.2.5 ActiveRecord::Base

def construct_finder_sql(options)
scope = scope(:find)
sql = "SELECT #{(scope && scope[:select]) || options[:select]
|| ‘*’} "
sql << "FROM #{(scope && scope[:from]) || options[:from] ||
table_name} "
…
…
end

Fix

To fix this, I had to provide a “select => " option to the finder so
the code doesn’t add the quoted_table_name to the ".”.

Questions:

  1. Is there any option to suppress adding of this quoted_table_name to
    the query
    ?

  2. I have pored over the release notes (kind of) on rubyonrails.org,
    this forum and other references to Rails 2.0.2 and could never find any
    mention about this. Did anyone see any documentation related to this
    change ?

  3. Has anyone run into this problem and solved it differently ?

Thanks,
Kannan

2

Sorry, but could you explain what you’re trying to do with the join
statement there? all I see is qq and qcs… I need it written in english
for
me to understand it.

On Jan 25, 2008 9:19 AM, Krishnan Kannan
[email protected]
wrote:

‘qci_questions’: SELECT qci_questions.* FROM qci_questions as qq

…
Questions:
3. Has anyone run into this problem and solved it differently ?

Thanks,
Kannan

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

–
Ryan B.

Feel free to add me to MSN and/or GTalk as this email.

3

On 24 Jan 2008, at 22:49, Krishnan Kannan wrote:

So this assert statement gives the following error :

assert_equal(43, QciQuestion.find(:all,
:joins => "as qq inner join qcis as qc on qq.qci_id = qc.id ",
:conditions => [ “qc.unit_id = ?”, 1]).size, “Wrong number of
resident interview questions loaded.”)

basically AR isn’t expecting you to alias the main table you’re
querying (and short of being able to parse the joins statement, how
could it?).
Wild speculation, but i suspect the reason it does this is because
previously you’d very commonly do

Foo.find :all, :joins => ‘…’
and then you spend 5 minutes scratching your head because weird stuff
is happening because the id column from foos is getting squashed by
the id column for this join tables. so you add :select => ‘foos.’ to
the thing. The change means that in the 95% of cases where you where
just systematically adding on :select => 'foos.’ you don’t have to.

Fred

4

Hello Ryan,

I can explain what’s the qq and qcs but my feeling is that’s not what is
causing the problem.

Basically there are 2 tables, Qcis (table qcis) and QciQuestion (table
qci_questions). qci_questions is a child of qcis table using qci_id as
the foreign key. qcis table has an id column as it’s primary key and
also have the Unit column.

In plain english this query translates to
“Get me all the qci_questions where the unit id is 1 in qcis”

The qq,qc are alias for the table qci_question and table qcis,
respectively.

Hope this helps.

Kannan

Ryan B. wrote:

Sorry, but could you explain what you’re trying to do with the join
statement there? all I see is qq and qcs… I need it written in english
for
me to understand it.

On Jan 25, 2008 9:19 AM, Krishnan Kannan
[email protected]
wrote:

‘qci_questions’: SELECT qci_questions.* FROM qci_questions as qq

…
Questions:
3. Has anyone run into this problem and solved it differently ?

Thanks,
Kannan

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

–
Ryan B.
http://www.frozenplague.net
Feel free to add me to MSN and/or GTalk as this email.

5

Frederick,

If your assumption is correct (alias the main table), is that a change
in Rails 2.0 (AR in Rails 2.0 specifically) ? Because this query worked
as such in Rails 1.2.5. As I have shown the AR code in my posting, the
quoted_table_name is something new in AR for Rails 2.0.2 and I am trying
to understand why it was added and if there is an option to not apply
it.

Thanks,
Kannan

Frederick C. wrote:

On 24 Jan 2008, at 22:49, Krishnan Kannan wrote:

So this assert statement gives the following error :

assert_equal(43, QciQuestion.find(:all,
:joins => "as qq inner join qcis as qc on qq.qci_id = qc.id ",
:conditions => [ “qc.unit_id = ?”, 1]).size, “Wrong number of
resident interview questions loaded.”)

basically AR isn’t expecting you to alias the main table you’re
querying (and short of being able to parse the joins statement, how
could it?).
Wild speculation, but i suspect the reason it does this is because
previously you’d very commonly do

Foo.find :all, :joins => ‘…’
and then you spend 5 minutes scratching your head because weird stuff
is happening because the id column from foos is getting squashed by
the id column for this join tables. so you add :select => ‘foos.’ to
the thing. The change means that in the 95% of cases where you where
just systematically adding on :select => 'foos.’ you don’t have to.

Fred

6

No change of which I am aware.

A couple of alternatives, though:

  1. Why not try a find_by_sql here? It looks like you are trying too
    hard to use the generic #find and force feed it a very specific join.
  2. Is there a reason that you cannot use find with the :include
    option, since it appears that you’re trying to check the size of the
    associated array?
  3. Is the test in the right place? It seems that you are really
    testing an attribute of Qcis (the number of questions associated to
    it) rather than an attribute/state of the QciQuestion itself.

Regards,
AndyV

On Jan 25, 11:20 am, Krishnan Kannan <rails-mailing-l…@andreas-