1

I am trying to write a query where I can get content from a location in AEM dated from today's day back 24 months, but the date is not based on the publish/modify/creation date. They are asking for the dates to be based on the content called the "publish release date".

I have the following working off the creation date:

String sqlstmt = "SELECT * "
    + "FROM [nt:unstructured] AS comp "
    + "WHERE ISDESCENDANTNODE(comp, '" + pathLocation + "') "
    + "  AND [sling:resourceType] = 'nvenergy/components/content/pressrelease' "
    + "  AND comp.[jcr:created] >= "
    + "    CAST('" + julianEndDateString + "T00:00:00.000Z' AS DATE) "
    + "  AND comp.[jcr:created] < "
    + "    CAST('" + julianCurrentDateString + "T00:00:00.000Z' AS DATE) "
    + "  ORDER BY 1 ASC";

but I really need it to be off the content's "publish release date" found here: 

...
if (currentNode.hasProperty("pressreleasepublishdate")) {
    Calendar publishedDate =
        currentNode.getProperty("pressreleasepublishdate").getDate();
...

Can I do a join or used something to use the node's property for the release date instead of the [jcr:created] as my range? Or do I have to execute the query hold it in a temp list and resort based on the node's property for the release date?

Siu Ching Pong -Asuka Kenji-
  • 7,943
  • 8
  • 53
  • 74
Carolyn Rider
  • 11
  • 1
  • 1
    Are those press releases all stored in the same location or scattered throughout the content? I am asking because if this was the case I would strongly advice to _not_ use a JCR query. Instead use the resource API and iterate the tree. – Jens May 17 '17 at 06:28
  • I agree with Jens that you should try to avoid querying the repository using the com.day.cq.search.QueryBuilder if possible. If not, why don't you simply change the `jcr:created` with `pressreleasepublishdate` (I don't think you will need the brackets anymore)? Table 10.3 on this page https://docs.jboss.org/jbossdna/0.7/manuals/reference/html/jcr-query-and-search.html offers more details on how to query the JCR repository by property constraints. – iusting May 17 '17 at 07:39
  • They are stored in the same location, however the client asked to make it flexible to be able to change the location to year specific. So the list component using this class can point to a new location each year. I have tried to replace the jcr:created with pressreleasepublishdate but I am not getting results back but that also does not work. – Carolyn Rider May 17 '17 at 17:00
  • ` String sqlstmt = "SELECT comp.* FROM [cq:PageContent] AS comp WHERE ISDESCENDANTNODE(comp, '"+pathLocation+"') AND (comp.[pressreleasepublishdate] <> '' AND comp.[pressreleasepublishdate] > CAST('"+julianCurrentDateString+"T00:00:00.000Z' AS DATE)) order by comp.[pressreleasepublishdate] asc"; ` – Carolyn Rider May 17 '17 at 17:01

0 Answers0