ActiveRecord::StatementInvalid error

rubyonrails-talk
1

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: Mixing :include and :conditions - Space Vatican

2

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: Mixing :include and :conditions - Space Vatican

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

3

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: Mixing :include and :conditions - Space Vatican

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

4

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%')

5

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