0

I have two Tables:

Locations, which is self-refential:

int id,
string name,
int location_id

and Nodes:

int id,
string name,
int location_id

The Relations are:

class Node
  belongs_to :location
end

class Location
  has_many :nodes
end

That works, but i want not only the direct associated Nodes for an Location but also that Nodes, wich are associated to any Child of the Location. I have an Select Statement with some CTE, which archives exactly this:

with sublocations (name, id, lvl) as 
(
  select 
    l.name,
    l.id,
    1 as lvl
  from locations l
  where l.id = 10003
  union all
  select 
    sl.name,
    sl.id,
    lvl + 1 as lvl
  from sublocations inner join locations sl
  on (sublocations.id = sl.location_id)
)
select 
  sl.name as location, 
  sl.id as location_code,
  n.name
from sublocations sl join nodes n on n.LOCATION_ID = sl.ID;

But how can i bring this in the has_many Relation?

Thanks, Jan

zishe
  • 10,665
  • 12
  • 64
  • 103
Ladeburger
  • 1
  • By location child you mean Node, right? You may add Location <=> Location association. – zishe Jun 22 '14 at 03:36
  • No, an Location child is an sublocation. Think of an Datacenter, I have two datacenter, in every Center there are Rows of Chassi an within every Chassi there are some Servers(Nodes). Rows and Chassi are location childs. So an Location could be Datacenter one. The direct Childs may be Row1 and Row2, every of them contains some Chassi and every Chassi contains some Nodes. In Location.where(:name => 'DC1').first.nodes i want to see ALL Nodes from DC1. – Ladeburger Jun 22 '14 at 15:24

0 Answers0