On Fri, Aug 30, 2013 at 4:04 PM, Jeremy Evans <[email protected]>wrote:
> On Friday, August 30, 2013 6:53:13 AM UTC-7, Christian MICHON wrote: > >> Hi Jeremy, >> >> I'm trying to use Sequel models to tap into a MSSQL database view (not >> under my control) and figure out the hierarchy of products I have to >> extract. I'm not performing any SQL write, pure read out. My next plans >> include eager extraction, regardless of the depth (this will be in another >> thread after I fix this issue). >> >> This particular product view contains 79 columns, and I only care for 2 ( >> product_id and name). When declaring the model, I've restricted the dataset >> to only these 2 attributes. Working fine. >> >> The parent-child relation is stored in another table, I've modified my >> model with a many_to_many association in order to grab subproducts. Working >> fine. >> >> Yet, each element I get in the result contains the original 79 columns, >> and not the 2 columns I defined in the model. How to go around this? >> >> Here's the code: please keep in mind tbl_products and tbl_sub_products >> are actually views, not pure tables. >> >> class Product < Sequel::Model >> set_dataset DB[:tbl_products].select(:**product_id, :name) >> set_primary_key :product_id >> many_to_many :subproducts, :class=>self, :join_table=>:tbl_sub_**products, >> :left_key=>:product_id, :right_key=>:reference_product >> end >> >> o = Product[82188] # => 2 attributes, all is good >> o.subproducts.size #=> 3 subproducts, all is good >> o.subproducts.map(&:product_**id) #=> visual inspection of subproducts >> id, many_to_many association is working well >> o.subproducts # => kaboom: array of 3 Product objects, but each has a >> @values of 79 attributes >> >> a = o.subproducts.first >> a.class # => Product >> a.columns # => [:product_id, :name] >> a.instance_variable_get(:@**values).size # => 79 >> >> Did I miss something obvious in the Sequel documentation? >> >> Thanks in advance for the help you can provide. >> > > many_to_many defaults to SELECTing associated_table.*. Use the :select > association option to change which columns are selected. > > Thanks, > Jeremy > Thanks Jeremy: I focused so much on the advanced association that I indeed missed this option in the basic association documentation. If I follow exactly your clue, I should code the following: many_to_many :subproducts, :class=>self, :select => [:product_id, :name], :join_table=>:tbl_sub_products, :left_key=>:product_id, :right_key=>:reference_product Yet, the first call to 'subproducts' give this error message: Sequel::DatabaseError: NativeException: java.sql.SQLException: Ambiguous column name 'PRODUCT_ID'. So instead I coded it this way, to remove the ambiguity on product_id (and point to tbl_products): many_to_many :subproducts, :class=>self, :select => [:tbl_products__product_id, :name], :join_table=>:tbl_sub_products, :left_key=>:product_id, :right_key=>:reference_product This works fine now: thanks! BR Christian -- 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 http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/groups/opt_out.
