Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

How to find all the assets that has been published for more than 1 year using JCR SQL2?

Avatar

Avatar
Validate 10
Level 2
Rui_J
Level 2

Likes

4 likes

Total Posts

39 posts

Correct Reply

1 solution
Top badges earned
Validate 10
Validate 1
Boost 3
Boost 1
Affirm 1
View profile

Avatar
Validate 10
Level 2
Rui_J
Level 2

Likes

4 likes

Total Posts

39 posts

Correct Reply

1 solution
Top badges earned
Validate 10
Validate 1
Boost 3
Boost 1
Affirm 1
View profile
Rui_J
Level 2

25-10-2016

How do I find all the assets that has been published for more than 1 year using JCR SQL2 ? Because each asset has a different cq:lastReplicated value, I believe I need to do something like 

SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:base] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, '/content/myapp') AND parent.[jcr:primaryType]='cq:Page' AND datediff(child.[cq:lastReplicated], CURRENTDATE)>365

For CURRENTDATE I can get it in java and cast it in SQL2, but how do I do a datediff, that part I don't know.

Please help.

Thank you

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile
smacdonald2008
Level 10

25-10-2016

Answers (3)

Answers (3)

Avatar

Avatar
Validate 10
Level 6
leeasling
Level 6

Likes

73 likes

Total Posts

292 posts

Correct Reply

75 solutions
Top badges earned
Validate 10
Validate 1
Boost 50
Boost 5
Boost 3
View profile

Avatar
Validate 10
Level 6
leeasling
Level 6

Likes

73 likes

Total Posts

292 posts

Correct Reply

75 solutions
Top badges earned
Validate 10
Validate 1
Boost 50
Boost 5
Boost 3
View profile
leeasling
Level 6

25-10-2016

Can't help with SQL2, but, using the query building/debugger it's pretty darn simple.

http://localhost:4502/libs/cq/search/content/querydebug.html?_charset_=UTF-8&query=type%3Dcq%3APage%...

Avatar

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile
smacdonald2008
Level 10

25-10-2016

Why won;t a date range work - set the first date to a longer time period and then the recent date to a year ago. For testing - see if that works, I have used a date range in many queries. 

Avatar

Avatar
Validate 10
Level 2
Rui_J
Level 2

Likes

4 likes

Total Posts

39 posts

Correct Reply

1 solution
Top badges earned
Validate 10
Validate 1
Boost 3
Boost 1
Affirm 1
View profile

Avatar
Validate 10
Level 2
Rui_J
Level 2

Likes

4 likes

Total Posts

39 posts

Correct Reply

1 solution
Top badges earned
Validate 10
Validate 1
Boost 3
Boost 1
Affirm 1
View profile
Rui_J
Level 2

25-10-2016

smacdonald2008 wrote...

Here is a blog that i show how to use Dates and JCR SQL2: 

http://scottsdigitalcommunity.blogspot.ca/2015/12/using-date-values-in-jcr-sql2-queries.html

 

 

Thank you.Please correct me if I am wrong, for the solution you provided above, I have to execute this query for each asset.

For example, /content/path1 's [cq:lastReplicated] is 2013-12-20, so path1's expiry date should be 2014-12-20. Because the expiry date is based on [cq:lastReplicated].

Consider another /content/path2 's [cq:lastReplicated] is 2015-12-20, so path2's expiry date should be 2016-12-20.

By using a date range will not work, I have to use a datediff([cq:lastReplicated], NOW)>365 days. Unfortunately, it looks like JCR SQL2 does not have this function.