Querying for Ratings

In Querying rating you will be working on the "REG_RATING" and "REG_RESOURCE_RATING" tables. Here are the table structure related to ratings.

REG_RATING
REG_ID	REG_RATING	REG_USER_ID	REG_RATED_TIME

REG_RESOURCE_RATING
REG_RATING_ID	REG_VERSION	REG_PATH_ID	REG_RESOURCE_NAME

Here is an example of query to return ratings of a resource (non-collection) which is authored by a given name:

SELECT RT.REG_RATING_ID FROM REG_RESOURCE_RATING RT, REG_RESOURCE R

WHERE (R.REG_VERSION=RT.REG_VERSION OR

(R.REG_PATH_ID=RT.REG_PATH_ID AND R.REG_NAME=RT.REG_RESOURCE_NAME))

AND R.REG_CREATOR=?

The following example shows a code snippet that execute this query and print the result rating values:

String sql1 = "SELECT RT.REG_RATING_ID FROM REG_RESOURCE_RATING RT, REG_RESOURCE R " +

"WHERE (R.REG_VERSION=RT.REG_VERSION OR " +

"(R.REG_PATH_ID=RT.REG_PATH_ID AND R.REG_NAME=RT.REG_RESOURCE_NAME)) " +

"AND R.REG_AUTHOR=?";

Resource q1 = registry.newResource();

q1.setContent(sql1);

q1.setMediaType(RegistryConstants.SQL_QUERY_MEDIA_TYPE);

q1.addProperty(RegistryConstants.RESULT_TYPE_PROPERTY_NAME,

RegistryConstants.RATINGS_RESULT_TYPE);

registry.put(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", q1);



Map parameters = new HashMap();

parameters.put("1", "admin");

Collection result = registry.executeQuery(RegistryConstants.CONFIG_REGISTRY_BASE_PATH + RegistryConstants.QUERIES_COLLECTION_PATH + "/custom-queries", parameters);



for (String ratingPath: result.getChildren()) {

        String ratingResource = registry.get(ratingPath);

        System.out.println(ratingResource.getContent());

    }

Note

When you put the query as a resource, you should set the property "RESULT_TYPE_PROPERTY_NAME" to "RATINGS_RESULT_TYPE".

For more information about Queries see Custom Query.