On Thursday, March 27, 2014 9:30:55 PM UTC-7, Snarke wrote: > > I’m pretty sure this is not the best way to go about this. :/ > > I’m trying to deal with processing incoming orders, generating the > invoices, and shipping them out. > > class Order < Sequel::Model > end > > If the order originated from Etsy, then there’s an Etsy customer ID, an > Etsy order ID, and other Etsy-specific data. If it came from Amazon, then > there’s different things. If it’s from Kickstarter, then I need to track > which campaign, and which reward level, are associated with it. In short, > lots of sparse data. Thus: > > class Orderproperty< Sequel::Model > end > > And the associated table: > > TABLE orderproperties ( > order_id int4 NOT NULL, > key varchar NOT NULL, > value varchar NOT NULL, > PRIMARY KEY (order_id,key) , > FOREIGN KEY (order_id) REFERENCES orders (id) > ); > > > If I want to find out what reward level an order had, assuming the key is > “Reward Level,” my code would look like: > Order[4325].orderproperties_dataset.filter(:key=>’Reward > Level').first[:value] > > Pretty unwieldy. What I (think I?) want to do is have a pseudo-method > ‘property’ so that I can do > Order[4325].property[‘Reward Level’] > and > Order[4325].property[‘Reward Level’] = “5" > > > This is what I did (don’t laugh, at least not too hard): > > class Order < Sequel::Model > > class PropertyProxy > def initialize(orderid) > @orderid = orderid > end > def [](key) > Orderproperty[:order_id => @orderid, :key > => key.to_s] > end > def []=(key, value) > Orderproperty.new(:order_id => @orderid, > :key => key.to_s, :value=>value).save > end > def keys > > Orderproperty.dataset.filter(:order_id=>@orderid).select_map(:key) > > end > end > > def property > PropertyProxy.new(self.id) > end > end > > This gives me Order.property[“key”] and …[“key”]=value, as well as > Order.property.keys to find out what properties are present. > > > It does do what I wanted it to, but I can’t help but suspect there’s a > better way to do it. One notable shortfall is that this doesn’t seem to > offer an elegant way of finding all the Reward Level 5 orders. It would be > nice if I could do something like > Order.filter(‘Reward Level’ => ‘5’).all > or at least > Order.filter(:key => ‘Reward Level’, :value=>’5’).all >
There's the correlated subquery approach: Order.where(:id=>DB[:order_properties].select(:order_id).where(:order_id=>:orders__id, :key=>'Reward Level', :value=>'5')) or the regular subquery approach: Order.where(:id=>DB[:order_properties].select(:order_id).where(:key=>'Reward Level', :value=>'5')) or the join approach: Order.join(:order_properties, :order_id=>:id, :key=>'Reward Level', :value=>'5').select_all(:orders) 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 http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
