Rails - Jan 2006 - sql server & linux: left join problem

I''ve got a left join problem of some sort connecting from linux  
FreeTDS/ODBC to SQL Server. It''s similar to the false post I had  
earlier, but this is a real problem. Here''s my code:

   def find_recipes_for_workorder
     @recipe_pages, @recipes = paginate_with_sort :recipes, :per_page  
=> 10, \
		:joins => ''left join workorder on workorder.workorder_id =  
recipe.workorder_id'', \
		:conditions => ["workorder.name = ?", params[:workorder][:name]]
     render :template => ''recipe/list''
   end

(paginate_with_sort at bottom of email.)

Here''s the top of the error:

Processing RecipeController#find_recipes_for_workorder (for  
172.17.1.102 at 2006-01-09 23:26:30) [POST]
   Parameters:
{"workorder"=>{"name"=>"RGL004"},
"action"=>"find_recipes_for_workorder",
"controller"=>"recipe"}


DBI::DatabaseError ( (8156) [unixODBC][FreeTDS][SQL Server]The column  
''name'' was specified multiple times for
''tally''.):
     /usr/lib/site_ruby/1.8/DBD/ODBC/ODBC.rb:168:in `execute''
     /usr/lib/site_ruby/1.8/dbi/dbi.rb:640:in `execute''
     /usr/lib/site_ruby/1.8/dbi/dbi.rb:671:in `select_all''
     /vendor/rails/activerecord/lib/active_record/connection_adapters/ 
sqlserver_adapter.rb:354:in `add_limit_offset!''
     /vendor/rails/activerecord/lib/active_record/base.rb:931:in  
`add_limit!''
     /vendor/rails/activerecord/lib/active_record/base.rb:924:in  
`construct_finder_sql''
     /vendor/rails/activerecord/lib/active_record/base.rb:395:in `find''
     /vendor/rails/actionpack/lib/action_controller/pagination.rb: 
174:in `find_collection_for_pagination''
     /vendor/rails/actionpack/lib/action_controller/pagination.rb: 
192:in `paginator_and_collection_for''
     /vendor/rails/actionpack/lib/action_controller/pagination.rb: 
124:in `paginate''
     /lib/sorting.rb:7:in `paginate_with_sort''

Any ideas appreciated.

Thanks,
Jamie

-------------
class ActionController::Base
   def paginate_with_sort(collection_id, options={})
     @params[:sort_direction] ||= ''-1''
     asc_desc = {''1'' => "asc",
''-1'' => "desc"}
     options[:order] = "#{@params[:sort_by]} #{asc_desc[@params 
[:sort_direction]]}" if @params[:sort_by]
     @params[:sort_direction] = @params[:sort_direction].to_i * (-1)
     paginate collection_id, options
   end
end

It turns out that SQLServerAdaptor''s add_limit_offset has a bug in  
it. If there is a left join on two tables that have columns with the  
*same name*, the error below is created. I''ve been able to verify  
this by taking the generated sql statement and placing it into Query  
Analyzer.

This is the sort of statement generated:
SELECT count(*) as TotalRows from
    (SELECT TOP 1000000000 * FROM recipe
     left join workorder on workorder.workorder_id = recipe.workorder_id
     WHERE (workorder.name = ''VMD001'') ) tally

recipe and workorder both have "name" as a column, so the query  
throws the error
"DBI::DatabaseError ( (8156) [unixODBC][FreeTDS][SQL Server]The  
column ''name'' was specified multiple times for
''tally''.):"

I''m wondering if anyone has already patched this, but not submitted  
it to trac.

I''ll be digging in and see if I can create a patch for this today. If  
anyone has anything done already, that would be great.

Jamie


On Jan 9, 2006, at 11:44 PM, Jamie Orchard-Hays wrote:
> I''ve got a left join problem of some sort connecting from linux  
> FreeTDS/ODBC to SQL Server. It''s similar to the false post I had  
> earlier, but this is a real problem. Here''s my code:
>
>   def find_recipes_for_workorder
>     @recipe_pages, @recipes =  
> paginate_with_sort :recipes, :per_page => 10, \
> 		:joins => ''left join workorder on workorder.workorder_id =  
> recipe.workorder_id'', \
> 		:conditions => ["workorder.name = ?",
params[:workorder][:name]]
>     render :template => ''recipe/list''
>   end
>
> (paginate_with_sort at bottom of email.)
>
> Here''s the top of the error:
>
> Processing RecipeController#find_recipes_for_workorder (for  
> 172.17.1.102 at 2006-01-09 23:26:30) [POST]
>   Parameters:
{"workorder"=>{"name"=>"RGL004"},
> "action"=>"find_recipes_for_workorder",
"controller"=>"recipe"}
>
>
> DBI::DatabaseError ( (8156) [unixODBC][FreeTDS][SQL Server]The  
> column ''name'' was specified multiple times for
''tally''.):
>     /usr/lib/site_ruby/1.8/DBD/ODBC/ODBC.rb:168:in `execute''
>     /usr/lib/site_ruby/1.8/dbi/dbi.rb:640:in `execute''
>     /usr/lib/site_ruby/1.8/dbi/dbi.rb:671:in `select_all''
>     /vendor/rails/activerecord/lib/active_record/ 
> connection_adapters/sqlserver_adapter.rb:354:in
`add_limit_offset!''
>     /vendor/rails/activerecord/lib/active_record/base.rb:931:in  
> `add_limit!''
>     /vendor/rails/activerecord/lib/active_record/base.rb:924:in  
> `construct_finder_sql''
>     /vendor/rails/activerecord/lib/active_record/base.rb:395:in
`find''
>     /vendor/rails/actionpack/lib/action_controller/pagination.rb: 
> 174:in `find_collection_for_pagination''
>     /vendor/rails/actionpack/lib/action_controller/pagination.rb: 
> 192:in `paginator_and_collection_for''
>     /vendor/rails/actionpack/lib/action_controller/pagination.rb: 
> 124:in `paginate''
>     /lib/sorting.rb:7:in `paginate_with_sort''
>
> Any ideas appreciated.
>
> Thanks,
> Jamie
>
> -------------
> class ActionController::Base
>   def paginate_with_sort(collection_id, options={})
>     @params[:sort_direction] ||= ''-1''
>     asc_desc = {''1'' => "asc",
''-1'' => "desc"}
>     options[:order] = "#{@params[:sort_by]} #{asc_desc[@params 
> [:sort_direction]]}" if @params[:sort_by]
>     @params[:sort_direction] = @params[:sort_direction].to_i * (-1)
>     paginate collection_id, options
>   end
> end
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails