On Mar 31, 10:00 am, Simon Arnaud <[email protected]> wrote:
> Hi
>
> I'm almost certain that I already asked, but I can't find it anywhere.
>
> Let's say I have a table for geographic subsets, with country, region,
> department and town in it. All in one table, because depending on the
> country there is no region, but states, or something else, with
> different depth level. And a geo type.
>
> Let's say I have a leaders table, wich tells me who the leader of a
> country/region/town is.
>
> Something like
>
> Geos
> :id
> :type_id
> :parent
> :name
>
> geo_types
> :id
> :name
>
> Leaders
> :id
> :name
>
> geos_leaders
> :id
> :geo_id
> :leader_id
>
> Now, I want to know who is the leader of the 'region' a given town is in.
>
> I first tried the Leader.something route. I then tried the
> Geo.filter(:name => xxx).first.ancestors route, but I can't find
> something that works.
>
> Anyone has an idea ?
First, if each geo only has 1 leader, why not just have a :leader_id
in the geos table?
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
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
Jeremy
--
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.
