DSpace-Simplestats queries

simplestats-queries.sql

-- Select all parents
SELECT DISTINCT c.community_id id, c.name FROM community c, community2community r WHERE c.community_id = r.parent_comm_id;

-- Select all (first-level) children of parent X (here 53)
SELECT DISTINCT c.community_id id, c.name FROM community c, community2community r WHERE r.parent_comm_id = 53 AND c.community_id = r.child_comm_id;

-- Select all collections in community X (here 54)
SELECT DISTINCT c.collection_id id, c.name FROM collection c, community2collection r WHERE r.collection_id = c.collection_id AND r.community_id = 54;

-- Select all child communities, recursively from community X (here 53)
WITH RECURSIVE all_communities AS (
  SELECT parent_comm_id, child_comm_id
  FROM community2community
  WHERE parent_comm_id = 7
  UNION
  SELECT c.parent_comm_id, c.child_comm_id
  FROM community2community c
  JOIN all_communities a
  ON (c.parent_comm_id = a.child_comm_id)
)
SELECT c.name, c.community_id, a.parent_comm_id  
FROM all_communities a, community c
WHERE c.community_id = a.child_comm_id;

-- Select all collections, recursively from community X (here 53) ! MISSING ONE, PROBABLY DUE TO FACT THAT "SOURCE" COLLECTION IS SOMEHOW IGNORED
WITH RECURSIVE all_communities AS (
  SELECT parent_comm_id, child_comm_id
  FROM community2community
  WHERE parent_comm_id = 53
  UNION
  SELECT c.parent_comm_id, c.child_comm_id
  FROM community2community c
  JOIN all_communities a
  ON (c.parent_comm_id = a.child_comm_id)
)
SELECT DISTINCT c.collection_id, c.name
FROM all_communities a, collection c, community2collection r
WHERE r.collection_id = c.collection_id AND a.child_comm_id = r.community_id;

--- CREATE TEMP TABLE FOR ALL SUBCOMMUNITIES
CREATE TEMPORARY TABLE retained_communities AS
WITH RECURSIVE all_communities AS (
  SELECT parent_comm_id, child_comm_id
  FROM community2community
  WHERE parent_comm_id = 53
  UNION
  SELECT c.parent_comm_id, c.child_comm_id
  FROM community2community c
  JOIN all_communities a
  ON (c.parent_comm_id = a.child_comm_id)
)
SELECT c.name, c.community_id, a.parent_comm_id  
FROM all_communities a, community c
WHERE c.community_id = a.child_comm_id;

--- CREATE TEMP TABLE FOR ALL COLLECTIONS IN THAT ARE CHILDREN OF PREVIOUS COMMUNITIES
CREATE TEMPORARY TABLE retained_collections AS
SELECT DISTINCT c.collection_id, c.name
FROM retained_communities a, collection c, community2collection r
WHERE r.collection_id = c.collection_id AND a.community_id = r.community_id;

--- CREATE TEMP TABLE FOR ALL ITEMS THAT BELONG TO PREVIOUS COLLECTIONS
CREATE TEMPORARY TABLE retained_items AS
SELECT DISTINCT i.item_id, i.name
FROM retained_collections a, item i, collection2item r
WHERE r.item_id = i.item_id AND r.collection_id = a.collection_id;