page and comment information

[Hsu Yao Chang]

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.

[Jonathan Soo Wai Hou]

Hi Hsu,

Whenever you encounter a page with no spaceid, it's most likely a older version of your current page. Only the latest page have a spaceid. The older page will have prever as a references to the newer page. Same goes to comment, older version of comment doesn't have a pageid, but a prevver referring to the updated comment.

That being said, make sure you include prevver is null in your SQL statement to exclude all the older version of record.

Hope this helps.

Best Regards,
Jonathan 

[Hsu Yao Chang]

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.

[Jonathan Soo Wai Hou]

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]

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.