ActiveRecord::StatementInvalid error

I updated to Rails 2.1.0 and I'm getting an
ActiveRecord::StatementInvalid error on an sql query that was
working on
the previous version. A simple call with and include getting data
from 2
tables. I'm not sure why this is happening now. Does anyone know what
might cause this? Thanks in advance.

short answer: qualify your conditions on included tables (ie
raiders.foo = 'bar' instead of foo='bar')
long answer: http://www.spacevatican.org/2008/4/29/include-and-conditions

Frederick Cheung wrote:

I updated to Rails 2.1.0 and I'm getting an
ActiveRecord::StatementInvalid error on an sql query that was
working on
the previous version. A simple call with and include getting data
from 2
tables. I'm not sure why this is happening now. Does anyone know what
might cause this? Thanks in advance.

short answer: qualify your conditions on included tables (ie
raiders.foo = 'bar' instead of foo='bar')
long answer:
http://www.spacevatican.org/2008/4/29/include-and-conditions

I'm not sure how to, since I'm using a function that loops through the
columns. How do I include the tables in it? see below.

def conditions_by_like(value, *columns)
    myArray = value.gsub(/[^\A-Za-z0-9\s]+/, "").split(/ /)
    myArray.collect do |itemz|
     columns = self.user_columns if columns.size==0
      columns = columns[0] if columns[0].kind_of?(Array)
      conditions = columns.map { |c|
       c = c.name if c.kind_of? ActiveRecord::ConnectionAdapters::Column
       "`#{c}` LIKE " +
ActiveRecord::Base.connection.quote("%#{itemz}%")
     }
     end.join(" OR ")
   end

Frederick Cheung wrote:

I updated to Rails 2.1.0 and I'm getting an
ActiveRecord::StatementInvalid error on an sql query that was
working on
the previous version. A simple call with and include getting data
from 2
tables. I'm not sure why this is happening now. Does anyone know
what
might cause this? Thanks in advance.

short answer: qualify your conditions on included tables (ie
raiders.foo = 'bar' instead of foo='bar')
long answer:
http://www.spacevatican.org/2008/4/29/include-and-conditions

I'm not sure how to, since I'm using a function that loops through the
columns. How do I include the tables in it? see below.

instead of your query saying
thing LIKE 'bar'
it should say
table_name.thing LIKE 'bar'

You already know what the table name is (self.table_name) so you just
need to do it.

Fred

Frederick Cheung wrote:

tables. I'm not sure why this is happening now. Does anyone know

I'm not sure how to, since I'm using a function that loops through the
columns. How do I include the tables in it? see below.

instead of your query saying
thing LIKE 'bar'
it should say
table_name.thing LIKE 'bar'

You already know what the table name is (self.table_name) so you just
need to do it.

Fred

I simplified the query below limiting it to the furnii and raiders
tables for testing and did exactly what you told me, but I'm getting
an error. It does not make any sense since the column furnii.name does
exist.

Mysql::Error: #42S22Unknown column 'furnii.name' in 'where clause':
SELECT `furnii`.`id` AS t0_r0, `furnii`.`original_raider_id` AS t0_r1,
`furnii`.`name` AS t0_r2, `furnii`.`description` AS t0_r3,
`furnii`.`security_id` AS t0_r4, `furnii`.`published` AS t0_r5,
`furnii`.`created_at` AS t0_r6, `furnii`.`updated_at` AS t0_r7,
`furnii`.`tmp` AS t0_r8, `furnii`.`modulename` AS t0_r9,
`furnii`.`imageasset_id` AS t0_r10, `raiders`.`id` AS t1_r0,
`raiders`.`login` AS t1_r1, `raiders`.`email` AS t1_r2,
`raiders`.`crypted_password` AS t1_r3, `raiders`.`salt` AS t1_r4,
`raiders`.`created_at` AS t1_r5, `raiders`.`updated_at` AS t1_r6,
`raiders`.`remember_token` AS t1_r7,
`raiders`.`remember_token_expires_at` AS t1_r8, `raiders`.`fname` AS
t1_r9, `raiders`.`lname` AS t1_r10, `raiders`.`imageasset_id` AS t1_r11,
`raiders`.`super_user` AS t1_r12, `raiders`.`gender` AS t1_r13,
`raiders`.`birthdate` AS t1_r14, `raiders`.`postalcode` AS t1_r15 FROM
`furnii` LEFT OUTER JOIN `raiders` ON `raiders`.id =
`furnii`.original_raider_id WHERE (`furnii.name` LIKE '%jack%' OR
`furnii.description` LIKE '%jack%' OR `furnii.published` LIKE '%jack%'
OR `furnii.tmp` LIKE '%jack%' OR `furnii.modulename` LIKE '%jack%' OR
`raiders.login` LIKE '%jack%' OR `raiders.email` LIKE '%jack%' OR
`raiders.crypted_password` LIKE '%jack%' OR `raiders.salt` LIKE '%jack%'
OR `raiders.remember_token` LIKE '%jack%' OR `raiders.fname` LIKE
'%jack%' OR `raiders.lname` LIKE '%jack%' OR `raiders.super_user` LIKE
'%jack%' OR `raiders.gender` LIKE '%jack%' OR `raiders.birthdate` LIKE
'%jack%' OR `raiders.postalcode` LIKE '%jack%')

Frederick Cheung wrote:

tables. I'm not sure why this is happening now. Does anyone know

I'm not sure how to, since I'm using a function that loops through
the
columns. How do I include the tables in it? see below.

instead of your query saying
thing LIKE 'bar'
it should say
table_name.thing LIKE 'bar'

You already know what the table name is (self.table_name) so you just
need to do it.

Fred

I simplified the query below limiting it to the furnii and raiders
tables for testing and did exactly what you told me, but I'm getting
an error. It does not make any sense since the column furnii.name does
exist.

You've quoted it wrong: `furnii.name` means a column called
furnii.name, as opposed to `furnii`.`name` which means a name column
on the furnii tqable.

Fred