Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Sql query to find the below list

Kotakonda, Bhargavi March 29, 2024

Im looking a sequel query in mysql to fetch the below listed feilds in confluence spaces and pages .

created by

last modified /last updated date

last viewed

number of views

incoming and outgoing links 

 

Thanks in Advance

2 answers

0 votes
Humashankar VJ
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 1, 2024

Also,

A few notes for anyone who needs or wants to dive deeper into SQL territory:

  1. Always be extremely careful with any query you want to send to your database
  2. If you are learning, do it in a testing environment. Never try new things on your production
  3. Understand the database relationship
  4. Check for similar queries
  5. If you plan on changing something on your database, create a backup before
  6. Updating the database requires Confluence to be down. Touching the database while Confluence runs will most likely break your instance

Some threads here in the community prove to be useful while learning:

0 votes
Humashankar VJ
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 1, 2024

Hi @Kotakonda, Bhargavi 

 

This query gives list of all spaces and associated users in every space

 

SELECT distinct s.SPACENAME, cu.lower_email_address
FROM SPACES AS s
JOIN SPACEPERMISSIONS AS sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping AS u ON sp.permusername = u.user_key
LEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_id
WHERE cu.lower_email_address is not null
ORDER BY s.SPACENAME, cu.lower_email_address;

 

Kotakonda, Bhargavi April 2, 2024

@Humashankar VJ this is not what i am looking for 

SELECT
COUNT(CONTENTID) AS "number of pages",
SPACES.SPACENAME,
MAX(CONTENT.LASTMODDATE) AS "LastModificationDate"
FROM CONTENT
JOIN SPACES ON CONTENT.SPACEID = SPACES.SPACEID
WHERE CONTENT.SPACEID IS NOT NULL
AND CONTENT.PREVVER IS NULL
AND CONTENT.CONTENTTYPE = 'PAGE'
AND CONTENT.CONTENT_STATUS = 'current'
GROUP BY SPACES.SPACENAME
ORDER BY "number of pages" DESC;

 

this is the query im using for now to fetch the space name number of pages and the last mod date but i want a  query which help me to fetch the child pages and the last mode 

Kotakonda, Bhargavi April 2, 2024

And also add the creator and last modifier, also created date to it .

 

Humashankar VJ
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 2, 2024

Hi @Kotakonda, Bhargavi - Thanks for the clear level set on this - let me research and get you some outcome..

Humashankar VJ
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 2, 2024

Hi @Kotakonda, Bhargavi 

 

To fetch the child pages and their last modification dates, you can modify your query to use a recursive Common Table Expression (CTE)

Can you try the below one:
This joins the results with the spaces table to get the space name.
The outer query groups the results and fetches the count of pages, max last modification date, and a list of child page titles

 

WITH RECURSIVE page_cte AS (
SELECT CONTENTID, CONTENT.LASTMODDATE, SPACES.SPACENAME, CONTENT.TITLE
FROM CONTENT
JOIN SPACES ON CONTENT.SPACEID = SPACES.SPACEID
WHERE CONTENT.SPACEID IS NOT NULL AND CONTENT.PREVVER IS NULL AND CONTENT.CONTENTTYPE = 'PAGE'
UNION ALL
SELECT c.CONTENTID, c.LASTMODDATE, s.SPACENAME, c.TITLE
FROM CONTENT c
JOIN page_cte p ON c.PARENTID = p.CONTENTID
JOIN SPACES s ON c.SPACEID = s.SPACEID
)
SELECT SPACENAME, COUNT(CONTENTID) AS "number of pages", MAX(LASTMODDATE) AS "LastModificationDate", GROUP_CONCAT(TITLE, ', ') AS "Child Pages"
FROM page_cte
GROUP BY SPACENAME
ORDER BY "number of pages" DESC;

 

Warm Regards

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
8.5.5
TAGS
AUG Leaders

Atlassian Community Events