Search in sources :

Example 36 with SortingImpl

use of com.yahoo.elide.core.sort.SortingImpl in project elide by yahoo.

the class AggregateBeforeJoinOptimizerTest method testSortOnTimeDimensionInProjectionRequiringJoin.

@Test
public void testSortOnTimeDimensionInProjectionRequiringJoin() {
    SQLTable gameRevenueTable = (SQLTable) metaDataStore.getTable("gameRevenue", NO_VERSION);
    Map<String, Sorting.SortOrder> sortMap = new TreeMap<>();
    sortMap.put("sessionDate", Sorting.SortOrder.desc);
    Query query = Query.builder().source(gameRevenueTable).timeDimensionProjection(gameRevenueTable.getTimeDimensionProjection("sessionDate")).metricProjection(gameRevenueTable.getMetricProjection("revenue")).sorting(new SortingImpl(sortMap, GameRevenue.class, dictionary)).build();
    String expected = "SELECT MAX(`example_GameRevenue_XXX`.`INNER_AGG_XXX`) AS `revenue`," + "PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') AS `sessionDate` " + "FROM (SELECT MAX(`example_GameRevenue`.`revenue`) AS `INNER_AGG_XXX`," + "`example_GameRevenue`.`player_stats_id` AS `player_stats_id` " + "FROM `gameRevenue` AS `example_GameRevenue` " + "GROUP BY `example_GameRevenue`.`player_stats_id` ) " + "AS `example_GameRevenue_XXX` " + "LEFT OUTER JOIN `playerStats` AS `example_GameRevenue_XXX_playerStats_XXX` " + "ON `example_GameRevenue_XXX`.`player_stats_id` = `example_GameRevenue_XXX_playerStats_XXX`.`id` " + "GROUP BY PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') ORDER BY PARSEDATETIME(FORMATDATETIME(`example_GameRevenue_XXX_playerStats_XXX`.`recordedDate`, 'yyyy-MM-dd'), 'yyyy-MM-dd') DESC\n";
    compareQueryLists(expected, engine.explain(query));
    testQueryExecution(query);
}
Also used : Query(com.yahoo.elide.datastores.aggregation.query.Query) SortingImpl(com.yahoo.elide.core.sort.SortingImpl) SQLTable(com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable) TreeMap(java.util.TreeMap) SQLUnitTest(com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest) Test(org.junit.jupiter.api.Test)

Example 37 with SortingImpl

use of com.yahoo.elide.core.sort.SortingImpl in project elide by yahoo.

the class QueryEngineTest method testMultipleTimeGrainsFilteredByMonthAlias.

@Test
public void testMultipleTimeGrainsFilteredByMonthAlias() throws Exception {
    Map<String, Argument> dayArguments = new HashMap<>();
    dayArguments.put("grain", Argument.builder().name("grain").value(TimeGrain.DAY).build());
    Map<String, Argument> monthArguments = new HashMap<>();
    monthArguments.put("grain", Argument.builder().name("grain").value(TimeGrain.MONTH).build());
    Map<String, Sorting.SortOrder> sortMap = new TreeMap<>();
    sortMap.put("highScore", Sorting.SortOrder.asc);
    FilterPredicate predicate = new FilterPredicate(new Path(ClassType.of(PlayerStats.class), dictionary, "recordedDate", "byMonth", new HashSet<>(monthArguments.values())), Operator.IN, Lists.newArrayList(new Day(Date.valueOf("2019-07-01"))));
    Query query = Query.builder().source(playerStatsTable).metricProjection(playerStatsTable.getMetricProjection("highScore")).whereFilter(predicate).timeDimensionProjection(playerStatsTable.getTimeDimensionProjection("recordedDate", "byDay", dayArguments)).timeDimensionProjection(playerStatsTable.getTimeDimensionProjection("recordedDate", "byMonth", monthArguments)).sorting(new SortingImpl(sortMap, PlayerStats.class, dictionary)).build();
    List<PlayerStats> results = toList(engine.executeQuery(query, transaction).getData());
    assertEquals(3, results.size());
    assertEquals(1000, results.get(0).getHighScore());
    assertEquals(new Day(Date.valueOf("2019-07-13")), results.get(0).fetch("byDay", null));
    assertEquals(new Month(Date.valueOf("2019-07-01")), results.get(0).fetch("byMonth", null));
    assertEquals(1234, results.get(1).getHighScore());
    assertEquals(new Day(Date.valueOf("2019-07-12")), results.get(1).fetch("byDay", null));
    assertEquals(new Month(Date.valueOf("2019-07-01")), results.get(1).fetch("byMonth", null));
    assertEquals(2412, results.get(2).getHighScore());
    assertEquals(new Day(Date.valueOf("2019-07-11")), results.get(2).fetch("byDay", null));
    assertEquals(new Month(Date.valueOf("2019-07-01")), results.get(2).fetch("byMonth", null));
}
Also used : Path(com.yahoo.elide.core.Path) Argument(com.yahoo.elide.core.request.Argument) Query(com.yahoo.elide.datastores.aggregation.query.Query) HashMap(java.util.HashMap) PlayerStats(example.PlayerStats) TreeMap(java.util.TreeMap) Month(com.yahoo.elide.datastores.aggregation.timegrains.Month) SortingImpl(com.yahoo.elide.core.sort.SortingImpl) FilterPredicate(com.yahoo.elide.core.filter.predicates.FilterPredicate) Day(com.yahoo.elide.datastores.aggregation.timegrains.Day) HashSet(java.util.HashSet) SQLUnitTest(com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest) Test(org.junit.jupiter.api.Test)

Example 38 with SortingImpl

use of com.yahoo.elide.core.sort.SortingImpl in project elide by yahoo.

the class QueryEngineTest method testJoinToSort.

/**
 * Test grouping by a dimension with a JoinTo annotation.
 *
 * @throws Exception exception
 */
@Test
public void testJoinToSort() throws Exception {
    Map<String, Sorting.SortOrder> sortMap = new TreeMap<>();
    sortMap.put("countryIsoCode", Sorting.SortOrder.asc);
    sortMap.put("highScore", Sorting.SortOrder.asc);
    Query query = Query.builder().source(playerStatsTable).metricProjection(playerStatsTable.getMetricProjection("highScore")).dimensionProjection(playerStatsTable.getDimensionProjection("overallRating")).dimensionProjection(playerStatsTable.getDimensionProjection("countryIsoCode")).sorting(new SortingImpl(sortMap, PlayerStats.class, dictionary)).build();
    List<Object> results = toList(engine.executeQuery(query, transaction).getData());
    PlayerStats stats1 = new PlayerStats();
    stats1.setId("0");
    stats1.setOverallRating("Good");
    stats1.setCountryIsoCode("HKG");
    stats1.setHighScore(1000);
    PlayerStats stats2 = new PlayerStats();
    stats2.setId("1");
    stats2.setOverallRating("Good");
    stats2.setCountryIsoCode("USA");
    stats2.setHighScore(1234);
    PlayerStats stats3 = new PlayerStats();
    stats3.setId("2");
    stats3.setOverallRating("Great");
    stats3.setCountryIsoCode("USA");
    stats3.setHighScore(2412);
    assertEquals(ImmutableList.of(stats1, stats2, stats3), results);
}
Also used : Query(com.yahoo.elide.datastores.aggregation.query.Query) SortingImpl(com.yahoo.elide.core.sort.SortingImpl) PlayerStats(example.PlayerStats) TreeMap(java.util.TreeMap) SQLUnitTest(com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest) Test(org.junit.jupiter.api.Test)

Example 39 with SortingImpl

use of com.yahoo.elide.core.sort.SortingImpl in project elide by yahoo.

the class SubselectTest method testJoinToSort.

/**
 * Test grouping by a dimension with a JoinTo annotation.
 *
 * @throws Exception exception
 */
@Test
public void testJoinToSort() throws Exception {
    Map<String, Sorting.SortOrder> sortMap = new TreeMap<>();
    sortMap.put("subCountryIsoCode", Sorting.SortOrder.asc);
    sortMap.put("highScore", Sorting.SortOrder.asc);
    Query query = Query.builder().source(playerStatsTable).metricProjection(playerStatsTable.getMetricProjection("highScore")).dimensionProjection(playerStatsTable.getDimensionProjection("overallRating")).dimensionProjection(playerStatsTable.getDimensionProjection("subCountryIsoCode")).sorting(new SortingImpl(sortMap, PlayerStats.class, dictionary)).build();
    List<Object> results = toList(engine.executeQuery(query, transaction).getData());
    PlayerStats stats1 = new PlayerStats();
    stats1.setId("0");
    stats1.setOverallRating("Good");
    stats1.setSubCountryIsoCode("HKG");
    stats1.setHighScore(1000);
    PlayerStats stats2 = new PlayerStats();
    stats2.setId("1");
    stats2.setOverallRating("Good");
    stats2.setSubCountryIsoCode("USA");
    stats2.setHighScore(1234);
    PlayerStats stats3 = new PlayerStats();
    stats3.setId("2");
    stats3.setOverallRating("Great");
    stats3.setSubCountryIsoCode("USA");
    stats3.setHighScore(2412);
    assertEquals(3, results.size());
    assertEquals(stats1, results.get(0));
    assertEquals(stats2, results.get(1));
    assertEquals(stats3, results.get(2));
}
Also used : Query(com.yahoo.elide.datastores.aggregation.query.Query) SortingImpl(com.yahoo.elide.core.sort.SortingImpl) PlayerStats(example.PlayerStats) TreeMap(java.util.TreeMap) SQLUnitTest(com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest) Test(org.junit.jupiter.api.Test)

Example 40 with SortingImpl

use of com.yahoo.elide.core.sort.SortingImpl in project elide by yahoo.

the class ViewTest method testSortingNestedRelationshipAttribute.

@Test
public void testSortingNestedRelationshipAttribute() throws Exception {
    Map<String, Sorting.SortOrder> sortMap = new TreeMap<>();
    sortMap.put("countryViewViewIsoCode", Sorting.SortOrder.desc);
    Query query = Query.builder().source(playerStatsWithViewSchema).metricProjection(playerStatsWithViewSchema.getMetricProjection("lowScore")).dimensionProjection(playerStatsWithViewSchema.getDimensionProjection("countryViewViewIsoCode")).sorting(new SortingImpl(sortMap, PlayerStatsWithView.class, dictionary)).build();
    List<Object> results = toList(engine.executeQuery(query, transaction).getData());
    PlayerStatsWithView usa0 = new PlayerStatsWithView();
    usa0.setId("0");
    usa0.setLowScore(35);
    usa0.setCountryViewViewIsoCode("USA");
    PlayerStatsWithView hk1 = new PlayerStatsWithView();
    hk1.setId("1");
    hk1.setLowScore(72);
    hk1.setCountryViewViewIsoCode("HKG");
    assertEquals(2, results.size());
    assertEquals(usa0, results.get(0));
    assertEquals(hk1, results.get(1));
    // the join would not happen for a view join
    PlayerStatsWithView actualStats1 = (PlayerStatsWithView) results.get(0);
    assertNull(actualStats1.getCountry());
}
Also used : Query(com.yahoo.elide.datastores.aggregation.query.Query) PlayerStatsWithView(example.PlayerStatsWithView) SortingImpl(com.yahoo.elide.core.sort.SortingImpl) TreeMap(java.util.TreeMap) SQLUnitTest(com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest) Test(org.junit.jupiter.api.Test)

Aggregations

SortingImpl (com.yahoo.elide.core.sort.SortingImpl)53 Test (org.junit.jupiter.api.Test)53 SQLUnitTest (com.yahoo.elide.datastores.aggregation.framework.SQLUnitTest)29 Query (com.yahoo.elide.datastores.aggregation.query.Query)29 TreeMap (java.util.TreeMap)29 HashMap (java.util.HashMap)23 EntityProjection (com.yahoo.elide.core.request.EntityProjection)15 Book (example.Book)15 PlayerStats (example.PlayerStats)12 Sorting (com.yahoo.elide.core.request.Sorting)10 Path (com.yahoo.elide.core.Path)9 Relationship (com.yahoo.elide.core.request.Relationship)8 Author (example.Author)8 DataStoreTransaction (com.yahoo.elide.core.datastore.DataStoreTransaction)7 FilterExpression (com.yahoo.elide.core.filter.expression.FilterExpression)7 SQLTable (com.yahoo.elide.datastores.aggregation.queryengines.sql.metadata.SQLTable)7 DataStoreIterable (com.yahoo.elide.core.datastore.DataStoreIterable)6 FilterPredicate (com.yahoo.elide.core.filter.predicates.FilterPredicate)6 InPredicate (com.yahoo.elide.core.filter.predicates.InPredicate)6 Day (com.yahoo.elide.datastores.aggregation.timegrains.Day)6