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.

@furnii_from_furnii = Furni.find :all, :include => “raider”,
:conditions => “”+Furni.conditions_by_like(@search)+" OR "
+Raider.conditions_by_like(@search) + “”

On 11 Aug 2008, at 22:16, Sam G. 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

Frederick C. wrote:

On 11 Aug 2008, at 22:16, Sam G. 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

On 12 Aug 2008, at 21:08, Sam G. 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

On 12 Aug 2008, at 22:20, Sam G. wrote:

thing LIKE ‘bar’
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

Frederick C. wrote:

On 12 Aug 2008, at 21:08, Sam G. 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 C. wrote:

On 12 Aug 2008, at 22:20, Sam G. wrote:

thing LIKE ‘bar’
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

got it. thanks again

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs