Confluence AORECENTLY_VIEWED table troubleshooting

[Matúš Klaudíny]

How come queries based on: 

SELECT *
FROM "AO_92296B_AORECENTLY_VIEWED"
JOIN user_mapping u ON u.user_key = user_key
ORDER BY "LAST_VIEW_DATE" DESC

 are generating the same output for each user_key inserted into the script. 

We have run this script for various different user_keys and we are getting back same recently viewed list of pages (timestamp is on a second exactly the same). Difference in every row is just the "user specific maping" which seems to be not working. Can you please elaborate about how this table works?

Thanks

[Stephen Sifers · Accepted solution]

Hello Matúš,

Your SQL statement is mostly correct except the database is case sensitive. With this said, your statement is attempting to match user_mapping user_key to user_mapping user_key due to it being ran as a low case statement. The proper way to run this SELECT statement will be as follows (note the quotes):

SELECT *
FROM "AO_92296B_AORECENTLY_VIEWED"
JOIN user_mapping u ON u.user_key = "USER_KEY"
ORDER BY "LAST_VIEW_DATE" DESC

Running the above should properly match your user_mapping table to your AO_92296B_AORECENTLY_VIEWED table. You will also notice that your USER_KEY and user_key will match this time (before they were mismatched due to self matching)

The quotes will honor case sensitivity within your database. Without the quotes (even if case is capital) it will be treated as lower case. If I run the same statement without the quotes you will get an errors as follows:

ERROR:  relation "public.ao_92296b_aorecently_viewed" does not exist
LINE 2: FROM public.AO_92296B_AORECENTLY_VIEWED

I hope this clarifies the issue your are having with the select statement.

Regards,
Stephen Sifers

[Pavan Kumar Pinniboyina]

Place a variable for AO table and match with user_key as a.user.key

Working query 

SELECT * FROM AO_92296B_AORECENTLY_VIEWED a JOIN user_mapping u ON u.user_key = a.user_key ORDER BY LAST_VIEW_DATE DESC

Hope it helps

Thanks,

Pavan