Forum: Ruby on Rails sql server & linux: left join problem

Announcement (2017-05-07): is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see and for other Rails- und Ruby-related community platforms.
7a966bb1534f0ea863bc9ffe65ae449f?d=identicon&s=25 Jamie Orchard-hays (jamieorc)
on 2006-01-10 05:45
(Received via mailing list)
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 => [" = ?", params[:workorder][:name]]
     render :template => 'recipe/list'

(paginate_with_sort at bottom of email.)

Here's the top of the error:

Processing RecipeController#find_recipes_for_workorder (for 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'
sqlserver_adapter.rb:354:in `add_limit_offset!'
     /vendor/rails/activerecord/lib/active_record/base.rb:395:in `find'
174:in `find_collection_for_pagination'
192:in `paginator_and_collection_for'
124:in `paginate'
     /lib/sorting.rb:7:in `paginate_with_sort'

Any ideas appreciated.


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
7a966bb1534f0ea863bc9ffe65ae449f?d=identicon&s=25 Jamie Orchard-hays (jamieorc)
on 2006-01-10 18:08
(Received via mailing list)
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

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 ( = '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.

This topic is locked and can not be replied to.