page and comment information

Question

Dear partners,

How do we know this page is belonged which space ? Because we can not find it's spacdid field in the content table.

And also how do we this comment is belonged which page? Because we can not find it's pageid field in the content table.

Thank you.

Hsu Yao Chang · Answer

Dear Jonathan,

We use confluence 5.8.X version, and now we can use query as shown as below:

select * from likes order by creationdate desc

and then use the sql query as shown as below :select * from content where contentid in (the contentid from likes table ) It can collect likes as listed as below:

tab2.jpg

But some comment and page just only has prevver and pageid information, how can we use one query sentence to list like or comment by space?  (It mean list like or comment list by space just only use one query sentence)

Thank you.

Jonathan Soo · Answer

Hey Hsu,

For sure, the current page version is the only page that doesn't have a value in it's prevver column. That's how Confluence determine if that's the current page version, the rest would be available in Page History page. For example, you wish to query all the current page in your database :

SELECT * FROM CONTENT WHERE PREVVER IS NULL AND CONTENTTYPE='PAGE';

Cheers,
Jonathan

Hsu Yao Chang · Answer

Dear Jonathan,

Thank you for your reply. If the command or page has many prever version, would it be possible to find out it's original pageid or spaceid ? Thank you.