2010/3/31 Jeremy Evans <[email protected]>: > First, if each geo only has 1 leader, why not just have a :leader_id > in the geos table?
I oversimplified, without noticing, my exact schema. It's because leaders have a 'type' in a leader_type table, for example : state representative, catholic, veterinary, etc. So you can have multiple leaders for a geo. > Anyway, the simple answer with the current schema (which allows > multiple leaders per geo) would be: > > Geo.many_to_many :leaders > Geo[town_id].leaders # Leader of town > Geo.many_to_one :par, :key=>:parent > Geo[town_id].par.leaders # Leader of region town is in, assuming > region is town's parent Yeah, for this, it's quite easy. > To get arbitrary depth in a single query you'd need to use a recursive > common table expression. If you don't mind using multiple queries: > > class Geo > def region_leaders > cur = self > while(cur) do > return cur.leaders if cur.type_id == REGION_TYPE > cur = cur.par > end > nil > end > end I already have a mostly working solution with multiple queries, but yours seems much simpler. You seem to have the same conclusion I had : I need rcte. Which is out, as I use SQLite, so I guess I will go the multiple queries route. I was also wondering how to do this : I have the town code (not id), which is unique, for example "FR43286" I have this hierarchy for geos : town > department > region > country I want all the veterinary leaders (department, region, country), which might be 0, 1 or many. I tried Geo.filter(:code => "FR43286").first.ancestors.leaders.type.filter(:name => "veterinary") but it does not work, and I would be very surprised if it would. After some thinking, I was wondering if I can express an intersection with sequel to solve this problem. Something like : Leader.filter(:type => "veterinary).all.intersect(Geo.filter(:code => "FR43286").first.leaders) Sounds possible ? Simon -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
