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:inexecute’
/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:inadd_limit_offset!’
/vendor/rails/activerecord/lib/active_record/base.rb:931:in
add_limit!' /vendor/rails/activerecord/lib/active_record/base.rb:924:inconstruct_finder_sql’
/vendor/rails/activerecord/lib/active_record/base.rb:395:in find' /vendor/rails/actionpack/lib/action_controller/pagination.rb: 174:infind_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:inpaginate’
/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

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