-
Type:
Story
-
Status: Done
-
Resolution: Done
-
Affects Version/s: None
-
Fix Version/s: Search Services 2.0
-
Labels:None
-
Epic Link:
-
Sprint:Team Ninja-King - S&I 29, Team Ninja-King - S&I 30, Team Ninja-King - S&I 31, Team Ninja-King - S&I 32, Team Ninja-King - S&I 33
-
Release Train:Southall
-
Delivery Team:Search
-
Story Points:8
Background
As a business user
When I connect to Insight Engine from Tableau
Then I want to be able use dates functions in the ORDER BY clause
The Tableau functions we need to support are DAY, MONTH, YEAR, Extract <unit of time> from <date field> (this is the function DAY, MONTH, YEAR translates to) and DATE
e.g.
SELECT MONTH(cm_modified) FROM ... WHERE ... GROUP BY MONTH(cm_modified) ORDER BY MONTH(cm_modified)
SELECT YEAR(cm_modified) FROM ... WHERE ... ORDER BY YEAR(cm_modified)
Potential Approach
There are two possible scenarios that involve two different areas of change:
1) ORDER BY following the GROUP BY
Here we sort grouped records, they are facet buckets from the Solr perspective.
This is managed through comparing tuple column values. The sort happens after the results are retrieved by Solr, specifically in one of the processing streams of aggregations:
org.apache.solr.client.solrj.io.comp.FieldComparator through a org.apache.solr.client.solrj.io.stream.RankStream wrapping the existent stream in the SolrTable class:
org/alfresco/solr/sql/SolrTable.java:1004
Enabling Date functions in this scenario is going to be a Transformer implementation that translate the ORDER BY to the related virtual field (cm_date_day, cm_date_month ect)
2) ORDER BY on its own
Assuming we have added index time support fot the part of date fields, a translation needs to happen to transform the ORDER BY to such fields from the date functions.
This is recommended to happen in the Transformer module ( org.alfresco.solr.sql.transformer )
The integration should be seamless , but it is recommended to take good care of this area: org/alfresco/solr/sql/SolrTable.java:560
To assess the proper behaviour.
Acceptance Criteria
- no regression in the date sql ORDER BY related tests
- mentioned date functions to work in the ORDER BY
- add e2e tests
- is related to by
-
SEARCH-2167 Support for Date Functions in GROUP BY Clause
- Done
- relates to
-
SEARCH-2141 Spike: Planning for CDATA Date support
- Done
- mentioned in
-
Page Loading...