suggestions_info_by_country_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

code

text

2147483647

√

null

name

text

2147483647

√

null

n_duplicates

int8

√

null

n_conflicts

int8

√

null

n_relations

int8

√

null

n_pending_orgs

int8

√

null

View Definition

SELECT c.val AS code,
    c.name,
    COALESCE(t1.n_duplicates, (0)::bigint) AS n_duplicates,
    COALESCE(t2.n_conflicts, (0)::bigint) AS n_conflicts,
    COALESCE(t3.n_relations, (0)::bigint) AS n_relations,
    COALESCE(t4.n_pending_orgs, (0)::bigint) AS n_pending_orgs
   FROM ((((countries c
     LEFT JOIN ( SELECT o.country,
            count(DISTINCT d.local_id) AS n_duplicates
           FROM (oa_duplicates d
             LEFT JOIN organizations o ON ((d.local_id = o.id)))
          WHERE ((d.status = 'suggested'::text) AND (o.status = 'approved'::text))
          GROUP BY o.country) t1 ON ((t1.country = c.val)))
     LEFT JOIN ( SELECT o.country,
            count(DISTINCT c_1.idgroup) AS n_conflicts
           FROM (oa_conflicts c_1
             LEFT JOIN organizations o ON ((c_1.id1 = o.id)))
          WHERE ((c_1.status = 'suggested'::text) AND (o.status = 'approved'::text))
          GROUP BY o.country) t2 ON ((t2.country = c.val)))
     LEFT JOIN ( SELECT o.country,
            count(DISTINCT r.id1) AS n_relations
           FROM (relationships r
             LEFT JOIN organizations o ON ((r.id1 = o.id)))
          WHERE ((r.status = 'suggested'::text) AND (o.status = 'approved'::text) AND ((r.reltype = 'IsChildOf'::text) OR (r.reltype = 'IsParentOf'::text)))
          GROUP BY o.country) t3 ON ((t3.country = c.val)))
     LEFT JOIN ( SELECT o.country,
            count(DISTINCT o.id) AS n_pending_orgs
           FROM organizations o
          WHERE (o.status = 'suggested'::text)
          GROUP BY o.country) t4 ON ((t4.country = c.val)))
  ORDER BY c.name;

Columns

View Definition

Possibly Referenced Tables/Views