Project

General

Profile

« Previous | Next » 

Revision 57674

Added by Michele Artini over 4 years ago

org info in all tabs

View differences:

schema.sql
1 1 
DROP VIEW organizations_view;
2 
DROP VIEW organizations_info_view;
2 3 
DROP VIEW organizations_simple_view;
3 4 
DROP VIEW users_view;
4 5 
DROP VIEW conflicts_view;
......
140 141 
	org.lng,
141 142 
	org.city,
142 143 
	org.country,
143 
	org.created_by,
144 
	org.creation_date,
145 
	org.modified_by,
146 
	org.modification_date,
147 144 
	COALESCE(jsonb_agg(DISTINCT jsonb_build_object('id', oid.otherid, 'type', oid.type)) FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,
148 145 
	COALESCE(jsonb_agg(DISTINCT jsonb_build_object('name', n.name, 'lang', n.lang))      FILTER (WHERE n.name      IS NOT NULL), '[]') AS other_names,
149 146 
	COALESCE(jsonb_agg(DISTINCT a.acronym)                                               FILTER (WHERE a.acronym   IS NOT NULL), '[]') AS acronyms,
......
161 158 
    org.lat,
162 159 
	org.lng,
163 160 
	org.city,
164 
	org.country,
161 
	org.country;
162
163 
CREATE VIEW organizations_info_view AS SELECT
164 
	org.id,
165 165 
	org.created_by,
166 166 
	org.creation_date,
167 167 
	org.modified_by,
168 
	org.modification_date;
168 
	org.modification_date,
169 
	count(r.id1)      as n_rels,
170 
	count(e.local_id) as n_enrichments,
171 
	count(c.local_id) as n_conflicts
172 
FROM organizations org
173 
	LEFT OUTER JOIN relationships r ON (org.id = r.id1)
174 
	LEFT OUTER JOIN openaire_simrels e ON (org.id = e.local_id AND e.oa_original_id NOT LIKE 'openorgs____::%')
175 
	LEFT OUTER JOIN openaire_simrels c ON (org.id = c.local_id AND c.oa_original_id LIKE 'openorgs____::%')
176 
GROUP BY org.id;
169 177
170 178 
CREATE VIEW organizations_simple_view AS SELECT
171 179 
    org.id,

Also available in: Unified diff