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.

  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.

  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.

  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.

  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>;