Must use table name in complex conditions

[josh...@gmail.com]

I posted this a while back on the rails forum but it not get much
attention, and thought some other folks may experience the same issue.
-------ORIGINAL POST--------
I'm working with a legacy MSSQL database with non-traditional table
names and foreign keys. For our purposes tablel 1 is named 'tblFoo"
and table 2 is named "tblBar". I named the models for the before
mentioned tables foo.rb and bar.rb, using set_table and
set_primary_id. Foo has_many :bars and bars
belongs_to :foo :foreign_key => 'fldBarID

In the process of performing a find with conditions on the two tables
I produced the following code:

@Foos = Foo.all(:include => :bars, :conditions => {:id => '25', :bars
=> { :created_on => '11/06/2002'}})
or
@Foos = Foo.all(:joins => :bars, :conditions => {:id => '25', :bars =>
{ :created_on => '11/06/2002'}})

...and the above code produces the following error:

ActiveRecord::StatementInvalid: DBI::DatabaseError: 37000 (4104)
[unixODBC][FreeTDS][SQL Server]The multi-part identifier
"bars.created_on" could not be bound. The error is caused when rails
does not replace the :bars symbol with the correct table name
(:tblBar) in WHERE section the generated sql. (but the table name is
correctly produced in the INNER JOIN section).

However.... if I format my find as follows, its all good (note the
symbol tblBar in the conditions with is the literal table name)

@Foos = Foo.all(:include => :bars, :conditions => {:id =>
'25', :tblBar => { :created_on => '11/06/2002'}})
or
@Foos = Foo.all(:joins => :bars, :conditions => {:id => '25', :tblBar
=> { :created_on => '11/06/2002'}})

Naming the models after the tables like tbl_foo.rb produces the same
error describe above and so does creating the has_many association:

has_many :tblBar, :class => 'bar'
-----END-----

This the above was originally created I have tested this with tables
that conform to rails standards on a Ubuntu production server with
Nginx and on my Mac in development using sqlserver-adapter-2.3 , with
the same results, the tables detailed in the :include or :join must
use represented using the actual table name as the symbol
in :conditions for that table.

[josh...@gmail.com]

Sorry that last paragraph was written on the run it should read:

In the time since I made the original post, I have tested this issue
using tables that conform to rails standards on a Ubuntu production
server running Passenger and Nginx, and on my Mac in development, both
using sqlserver-adapter-2.3 . In both cases the results were the same
as in the previous post, the tables detailed in the :include or :join
must be represented using the actual table name as the symbol

in :conditions for that table.

[Murray Steele]

I could be wrong, but I think this is just how rails does it. I'm
pretty sure that conditions hashes just get turned (without much
cunning) into conditions strings. Nothing fancy going on with the
SQLServer adpater here, it's just Rails.

2010/1/13 josh...@gmail.com <josh...@gmail.com>:

> --
> You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.
> To post to this group, send email to rails-sqlse...@googlegroups.com.
> To unsubscribe from this group, send email to rails-sqlserver-a...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/rails-sqlserver-adapter?hl=en.
>
>
>
>

[Ken Collins]

Agreed,

And this is going to get so so much more exciting when Rails3 comes along and we have to adapt to AREL usage. I have not even peaked at it yet :)

- Ken

[josh...@gmail.com]

You all area correct, it is a rails issue, tested it on MySQL this
morning and received the same error.

On Jan 13, 4:51 am, Ken Collins <k...@metaskills.net> wrote:
> Agreed,
>
> And this is going to get so so much more exciting when Rails3 comes along and we have to adapt to AREL usage. I have not even peaked at it yet :)
>
>  - Ken
>
> On Jan 13, 2010, at 5:24 AM, Murray Steele wrote:
>
> > I could be wrong, but I think this is just how rails does it. I'm
> > pretty sure that conditions hashes just get turned (without much
> > cunning) into conditions strings.  Nothing fancy going on with the
> > SQLServer adpater here, it's just Rails.
>

> > 2010/1/13 joshmc...@gmail.com <joshmc...@gmail.com>:

> >> For more options, visit this group athttp://groups.google.com/group/rails-sqlserver-adapter?hl=en.