I'm trying to setup a many-to-many relationship on a legacy database (which hopefully explains the ugly and non-Sequel standard table and field names), which I *think* I've done ok, unfortunately it's triggering a TinyTDS / MSSQL error whenever I try to query the association. Hoping you can point me to a quick fix that I'm just not seeing, as if feels like this should be pretty simple...
Everything is wrapped in a Module as I deal with some other databases in this project, but the models look like so, with the problem associations (Device.tags, and Tag.devices) bolded... class Device < Sequel::Model(InvDB[:tbl_equipment]) set_primary_key :id_equipment def_column_alias(:model_id, :model) def_column_alias(:status_id, :status) one_to_one :jssmobileinfo, :key=>:serial_number, :primary_key=>: serial_number, :class=>'JAMF::MobileNormalized' one_to_many :history, :key=>:equipment_id, :primary_key=>:id_equipment, : class=>'Inventory::Device_History' many_to_one :user, :key=>:owner, :primary_key=>:id_no, :class=> 'Inventory::User', :select=>[:firstname, :lastname, :nom_utilisateur, : mot_de_passe, :type, :school, :grade] many_to_one :device_model, :key=>:model_id, :key_column => :model, : primary_key=>:id_model, :class=>'Inventory::Device_Model', :select=>[: id_model, :model_description, :apple_model_number, :type] many_to_one :device_status, :key=>:status_id, :primary_key=>:id_status, : class=>'Inventory::Device_Status', :select=>[:status_description, :assigned] many_to_one :site, :key => :school, :primary_key=>:id_school, :class => 'Inventory::Site', :select => [:school_short_name] * many_to_many * *:tags, :join_table => InvDB[:tbl_equipment_tag], :left_key => :equipment_id, :left_primary_key => :id_equipment, :right_key => :tag_id, :right_primary_key => :id, :class=>'Inventory::Tag'*end class Tag < Sequel::Model(InvDB[:tbl_tag]) set_primary_key :id *many_to_many *:devices, :join_table=>InvDB[:tbl_equipment_tag], :left_key=>:tag_id, :left_primary_key => :id, :right_key=>:equipment_id, :right_primary_key => :id_equipment, :class=>'Inventory::Device' end There is an associated table "tbl_equipment_tag" that contains an :id primary key (just for that record), an :equipment_id and a :tag_id. When I find a device and then try to get the tags associated, I'm getting a TinyTDS / MSSQL error like below: "/Library/Ruby/Gems/2.3.0/gems/sequel-5.13.0/lib/sequel/adapters/tinytds.rb:205:in `fields': TinyTds::Error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. (Sequel::DatabaseError)" I get the same error if find a Tag and try to see the associated Devices. The sql code that is being generated is below: SELECT [TBL_EQUIPMENT].* FROM [TBL_EQUIPMENT] INNER JOIN (SELECT * FROM [ TBL_EQUIPMENT_TAG]) AS [T1] ON ([T1].[EQUIPMENT_ID] = [TBL_EQUIPMENT].[ ID_EQUIPMENT]) WHERE ((SELECT * FROM [TBL_EQUIPMENT_TAG]).[TAG_ID] = 5) If I run that query against the DB directly (using Navicat FWIW), I get nearly the exact error: "[FreeTDS][SQL Server]Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." I suspect this is something funky about how MS SQL works, but wondering about a workaround? Thanks, Jeff -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To post to this group, send email to sequel-talk@googlegroups.com. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
