Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

My Requirement is to write a sql query to get the sub-region wise (fault)events count that occurred for the managedobjects. My database is postgres 8.4. Let me explain using the table structure.

My tables in django: Managedobject:

    class Managedobject(models.Model):
       name                = models.CharField(max_length=200, unique=True)
       iscontainer         = models.BooleanField(default=False,)
       parentkey           = models.ForeignKey('self', null=True)

Event Table:

    class Event(models.Model):
      Name        = models.CharField(verbose_name=_('Name'))
      foid        = models.ForeignKey(Managedobject)

Managedobject Records:

NOC
   Chennai
      MO_1
      MO_2
      MO_3
   Mumbai
      MO_4
      MO_5
      MO_6
   Delhi
   Bangalore
IP
   Calcutta
   Cochin

Events Records:

event1 MO_1
event2 MO_2
event3 MO_3
event4 MO_5
event5 MO_6

Now i need to get the events count for all the sub-regions. For example, 

for NOC region:
  Chennai - 3
  Mumbai - 2
  Delhi - 0
  Bangalore - 0

So far i am able to get the result in two different queries. 1. Get the subregions.

select id from managedobject where iscontainer = True and parentkey = 3489

  1. For each of the region (using for loop), get the count as follows:

    SELECT count(*) from event ev WHERE ev.foid IN (WITH RECURSIVE q AS (SELECT h FROM managedobject h WHERE parentkey = 3489 UNION ALL SELECT hi FROM q JOIN managedobject hi ON hi.parentkey = (q.h).id ) SELECT (q.h).id FROM q)

Please help to combine the queries to make it a single query and for getting the top 5 regions. Since the query is difficult in django, i am going for raw sql query.

Thanks, Siva

share|improve this question
do you need that iscontainer field? if parentkey = None then the object isn't a container and if the Managedobject has a parentkey, then it is a container? – DTing Apr 14 '11 at 9:25
@krieger. Thanks for the effort. Here container has got a different meaning. The parent/child relationship level goes endless in Managedobject table. If the record is marked as container, it is a logical entity just added for grouping. Even Record MO_1 can have child MO_11 but it is not a container as it represents a physical entity. – Siva Apr 14 '11 at 10:00
ohhh i see, I misread your initial question. I'll give this another look in a bit. sorry about that. I thought the MO_1 were the events, they are just which managedobject the events were related too. – DTing Apr 14 '11 at 11:03

1 Answer

up vote 0 down vote accepted

I Got the query:

WITH RECURSIVE q AS ( 
  SELECT  h, 
          1 AS level, 
          id AS ckey, 
          displayname as dname 
  FROM managedobject h 
 WHERE parentkey = 3489  
   and logicalnode=True 

 UNION ALL 

 SELECT  hi, 
         q.level + 1 AS level, 
         ckey, 
         dname 
 FROM q 
   JOIN managedobject hi ON hi.parentkey = (q.h).id 
) 
SELECT count(ckey) as ccount, 
       ckey, 
       dname 
FROM q 
  JOIN event as ev on ev.foid_id = (q.h).id 
GROUP BY ckey, dname 
ORDER BY ccount DESC 
LIMIT 5
share|improve this answer
What is this expression: ev.foid_id = (q.h).id doing? Is that a typo? – a_horse_with_no_name Apr 16 '11 at 11:13
No, It is a running query. In Django, foreignkey field will be appended with "_id". In postgres recursive query, you have to access the field as "(q.h).id". – Siva Apr 20 '11 at 11:36
I have never seen that syntax with a recursive CTE. I'd say q.id should be enough for the inner query. The outer should probably use q.ckey – a_horse_with_no_name Apr 20 '11 at 11:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.