How to List Favourites for a Specific User with SQL Queries

Objective

You'd like to check a user's favourites in your Confluence database. The following queries will help you find favourites for a specific user.

Instructions for Confluence 5.2+

• Execute the query below to get the ID for the "favourite" label of the user in question. Be sure to replace <name_of_user> with the actual username.

  1 2 3 4 SELECT l.labelid, l.name, u.username FROM label l join user_mapping u on l.owner = u.user_key WHERE name like 'favourite' and u.username = '<name_of_user>';

• Using the label ID returned by the query above, execute the next query. Be sure to replace <labelid_from_first_query> with the actual label ID.

  1 2 3 4 5 6 SELECT u.username, l.name, c.contenttype, c.title FROM label l, content_label t, content c, user_mapping u WHERE l.labelid=t.labelid AND t.contentid=c.contentid AND t.owner = u.user_key AND t.labelid=<labelid_from_first_query>;

Instructions for Confluence up to version 5.1

• Execute the query below to get the ID for the "favourite" label of the user in question. Be sure to replace <name of the user> with the actual username.

  1 2 3 4 SELECT labelid, name, owner FROM label WHERE name like 'favourite' AND owner like '<name of the user>';

• Using the label ID returned by the query above, execute the next query. Be sure to replace <labelid of the first query> with the actual label ID.

  1 2 3 4 5 SELECT t.owner, l.name, c.contenttype, c.title FROM label l, content_label t, content c WHERE l.labelid=t.labelid AND t.contentid=c.contentid AND t.labelid=<labelid of the first query>;