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
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > 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 > -- > Posted via http://www.ruby-forum.com/. > > > >-- Ryan Bigg http://www.frozenplague.net Feel free to add me to MSN and/or GTalk as this email. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 24 Jan 2008, at 22:49, Krishnan Kannan wrote:> > 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.") >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> > 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 > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Bigg 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 > <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> > 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 Bigg > http://www.frozenplague.net > Feel free to add me to MSN and/or GTalk as this email.-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Cheung 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-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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- s.net> wrote:> 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 Cheung 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 > > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---