« truncated dates are iso 8601 compliant | Main | Googlebot probing our AgEcon search engine »

sql for rollup stats in the dspace database

A) Find all the item handles that belong to a community with a community id of 4

SELECT handle.handle FROM community2item, handle WHERE community2item.item_id = handle.resource_id AND handle.resource_type_id = 2 AND community2item.community_id = 4

B) Turning a community handle into a resource id (community id)

SELECT handle.resource_id FROM handle WHERE handle=125 AND resource_type_id=4;

resource_id
-------------
4
Note: resource_type_id=4 means community.

C) Substitute B into A -> Item handles in terms of community handle

SELECT count (handle) FROM community2item, handle WHERE community2item.item_id = handle.resource_id AND handle.resource_type_id = 2 AND community2item.community_id = (SELECT handle.resource_id FROM handle WHERE handle=125 AND resource_type_id=4);

Note: resource_type_id=2 means item.

Posted by Jeffrey Silvis on March 9, 2009 4:50 PM |

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)