On Monday, December 3, 2018 at 3:35:32 PM UTC-8, Jeff Dyck wrote: > > 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 >
:join_table option should be a table name, not a dataset: :join_table=>:tbl_equipment_tag Likewise for the other use of :join_table. Thanks, Jeremy -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
