/** * Add an ORDER BY clause (or an additional ordering term) to this query * * @param orders one or more ordering terms * @return this Query object, to allow chaining method calls */ public Query orderBy(Order... orders) { if (immutable) { return fork().orderBy(orders); } if (this.orders == null) { this.orders = new ArrayList<>(); } SquidUtilities.addAll(this.orders, orders); invalidateCompileCache(); return this; }
/** * Build a {@link Query} combining this object's internal state with the arguments passed. If a * {@link ProjectionMap} is set, the projection elements will be evaluated and transformed accordingly. If the * sortOrder is null or empty, the default order will be used (if one was set). * * @param projection the raw column names to be selected * @param selection a raw selection string * @param selectionArgs array of strings which substitute replaceable arguments in the selection string * @param sortOrder a raw ordering clause * @return a {@link Query} using the projection, selection, selection args, and sort order */ public Query build(String[] projection, String selection, String[] selectionArgs, String sortOrder) { Query query = Query.select(computeProjection(projection)).from(dataSource); boolean hasUserSelection = !SqlUtils.isEmpty(selection); if (hasUserSelection) { query.where(Criterion.fromRawSelection(selection, selectionArgs)); } if (!SqlUtils.isEmpty(sortOrder)) { query.orderBy(Order.fromExpression(sortOrder)); } else if (defaultOrder != null && defaultOrder.length > 0) { query.orderBy(defaultOrder); } if (strictMode && hasUserSelection) { query.requestValidation(); } return query; }
public void testIdColumnForTableModels() { Query query = Query.select(TestModel.PROPERTIES).orderBy(TestModel.ID.asc()); testCursorAdapterInternal(new TestModel(), null, query, new CursorAdapterTest() { @Override public void testCursorAdapter(SquidCursorAdapter<AbstractModel> adapter) { assertTrue(adapter.hasStableIds()); assertEquals(1, adapter.getItemId(0)); assertEquals(2, adapter.getItemId(1)); } }); }
public void testReusableModel() { Query query = Query.select(TestModel.PROPERTIES).orderBy(TestModel.ID.asc()); testCursorAdapterInternal(new TestModel(), null, query, new CursorAdapterTest() { @Override public void testCursorAdapter(SquidCursorAdapter<AbstractModel> adapter) { AbstractModel first = adapter.getItem(0); AbstractModel second = adapter.getItem(1); assertEquals(first, second); } }); }
public void testInCriterion() { List<String> expectedNames = Arrays.asList("bigBird", "cookieMonster"); Query query = Query.select().where(Employee.NAME.in("bigBird", "cookieMonster")).orderBy(Employee.NAME.asc()); testInQuery(expectedNames, query); query = Query.select().where(Employee.NAME.notIn("bigBird", "cookieMonster")).orderBy(Employee.NAME.asc()); testInQuery(Arrays.asList("bert", "elmo", "ernie", "oscar"), query); List<String> list = Arrays.asList("bigBird", "cookieMonster"); query = Query.select().where(Employee.NAME.in(list)).orderBy(Employee.NAME.asc()); testInQuery(expectedNames, query); // Test off-by-one error that used to occur when the in criterion wasn't the last criterion in the list query = Query.select().where(Employee.NAME.in(list).or(Field.field("1").neq(1))).orderBy(Employee.NAME.asc()); testInQuery(expectedNames, query); }
public Query getOrderedTasksWithTags() { Function<Long> unixNow = Function.multiply(1000, Function.functionWithArguments("strftime", "%s", "now")); Function<Long> sinceCompletion = Function.subtract(unixNow, Task.COMPLETION_DATE); return getTasksWithTagsQuery(Task.COMPLETION_DATE.eq(0) .or(sinceCompletion.lt(60000 * 5))) .orderBy(Function.caseWhen(Task.DUE_DATE.neq(0)).desc(), Task.DUE_DATE.asc()); }
private void testBetween(List<Long> expectedIds, boolean useNotBetween) { SquidCursor<Employee> cursor = database.query(Employee.class, Query.select(Employee.ID).where(useNotBetween ? Employee.ID.notBetween(2, 5) : Employee.ID.between(2, 5)).orderBy(Employee.ID.asc())); try { assertEquals(expectedIds.size(), cursor.getCount()); for (Long id : expectedIds) { cursor.moveToNext(); assertEquals(id.longValue(), cursor.get(Employee.ID).longValue()); } } finally { cursor.close(); } }
public void testCustomIdColumn() { Function<Long> idSquared = Function.rawFunction("_id * _id"); LongProperty idSquaredProperty = LongProperty.fromFunction(idSquared, "idSquared"); Query query = Query.select(TestModel.PROPERTIES).selectMore(idSquaredProperty).orderBy(TestModel.ID.asc()); testCursorAdapterInternal(new TestModel(), idSquaredProperty, query, new CursorAdapterTest() { @Override public void testCursorAdapter(SquidCursorAdapter<AbstractModel> adapter) { assertTrue(adapter.hasStableIds()); assertEquals(1, adapter.getItemId(0)); assertEquals(4, adapter.getItemId(1)); } }); }
public void testSelectDistinct() { Query query = Query.selectDistinct(Employee.MANAGER_ID).orderBy(Employee.MANAGER_ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(4, cursor.getCount()); cursor.moveToFirst(); assertEquals(Long.valueOf(0), cursor.get(Employee.MANAGER_ID)); cursor.moveToNext(); assertEquals(Long.valueOf(1), cursor.get(Employee.MANAGER_ID)); cursor.moveToNext(); assertEquals(Long.valueOf(2), cursor.get(Employee.MANAGER_ID)); cursor.moveToNext(); assertEquals(Long.valueOf(5), cursor.get(Employee.MANAGER_ID)); } finally { cursor.close(); } }
private void testGlob(List<Employee> expected, boolean useNotGlob) { SquidCursor<Employee> cursor = database.query(Employee.class, Query.select(Employee.ID, Employee.NAME).where(useNotGlob ? Employee.NAME.notGlob("b*") : Employee.NAME.glob("b*")) .orderBy(Employee.ID.asc())); try { assertEquals(expected.size(), cursor.getCount()); for (Employee e : expected) { cursor.moveToNext(); assertEquals(e.getRowId(), cursor.get(Employee.ID).longValue()); assertEquals(e.getName(), cursor.get(Employee.NAME)); } } finally { cursor.close(); } }
private void testRecyclerAdapterInternal(LongProperty idProperty, RecyclerAdapterTest test) { Query query = Query.select(TestModel.PROPERTIES) .orderBy(TestModel.BIRTHDAY.asc()) .limit(2); if (idProperty != null) { query.selectMore(idProperty); } SquidCursor<TestModel> cursor = database.query(TestModel.class, query); TestRecyclerAdapter adapter = new TestRecyclerAdapter(idProperty); adapter.changeCursor(cursor); try { test.testRecyclerAdapter(adapter); } finally { cursor.close(); } }
public void testOrderByArray() { Long[] order = new Long[]{5L, 1L, 4L}; SquidCursor<Employee> cursor = database.query(Employee.class, Query.select(Employee.ID).limit(order.length).orderBy(Employee.ID.byArray(order))); try { assertEquals(order.length, cursor.getCount()); for (int i = 0; i < order.length; i++) { cursor.moveToPosition(i); assertEquals(order[i], cursor.get(Employee.ID)); } } finally { cursor.close(); } }
public void testBooleanFunctionOnCriterion() { BooleanProperty onCriterion = BooleanProperty .fromFunction(Function.caseWhen(TestModel.FIRST_NAME.eq("Sam")), "firstNameSam"); SquidCursor<TestModel> cursor = database .query(TestModel.class, Query.select(onCriterion).orderBy(Order.asc(TestModel.ID))); try { cursor.moveToFirst(); assertTrue(cursor.get(onCriterion)); cursor.moveToNext(); assertFalse(cursor.get(onCriterion)); } finally { cursor.close(); } }
public void testReverseOrder() { long max = database.countAll(Employee.class); SquidCursor<Employee> cursor = database.query(Employee.class, Query.select(Employee.ID).orderBy(Employee.ID.asc().reverse())); try { assertEquals(max, cursor.getCount()); assertTrue(max > 0); while (cursor.moveToNext()) { long nextId = cursor.get(Employee.ID); if (nextId > max) { fail("IDs not in reverse order"); } max = nextId; } } finally { cursor.close(); } }
public void testIntersect() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .intersect(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(1, cursor.getCount()); cursor.moveToFirst(); assertEquals(cookieMonster, new Employee(cursor)); } finally { cursor.close(); } }
public void testExcept() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .except(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(2, cursor.getCount()); cursor.moveToFirst(); assertEquals(elmo, new Employee(cursor)); cursor.moveToNext(); assertEquals(oscar, new Employee(cursor)); } finally { cursor.close(); } }
public void testSelectFromView() { View view = View.fromQuery(Query.select(Employee.PROPERTIES) .from(Employee.TABLE).where(Employee.MANAGER_ID.eq(bigBird.getRowId())), "bigBirdsEmployees"); database.tryCreateView(view); Query fromView = Query.fromView(view).orderBy(view.qualifyField(Employee.ID).asc()); SquidCursor<Employee> cursor = database.query(Employee.class, fromView); try { assertEquals(3, cursor.getCount()); cursor.moveToFirst(); assertEquals("cookieMonster", cursor.get(Employee.NAME)); cursor.moveToNext(); assertEquals("elmo", cursor.get(Employee.NAME)); cursor.moveToNext(); assertEquals("oscar", cursor.get(Employee.NAME)); } finally { cursor.close(); } }
public void testUnion() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .union(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(3, cursor.getCount()); cursor.moveToFirst(); assertEquals(cookieMonster, new Employee(cursor)); cursor.moveToNext(); assertEquals(elmo, new Employee(cursor)); cursor.moveToNext(); assertEquals(oscar, new Employee(cursor)); } finally { cursor.close(); } }
public void testLimitAndOffsetWithExpressions() { // limit = 1 + (count(*) / 4), offset = count(*) / 2 Field<Integer> limit = Function.add(1, Function.divide( Query.select(IntegerProperty.countProperty()).from(Employee.TABLE).asFunction(), 4)); Field<Integer> offset = Function.divide( Query.select(IntegerProperty.countProperty()).from(Employee.TABLE).asFunction(), 2); Query query = Query.select().orderBy(Employee.NAME.asc()).limit(limit, offset); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(2, cursor.getCount()); cursor.moveToFirst(); assertEquals(elmo, new Employee(cursor)); cursor.moveToNext(); assertEquals(ernie, new Employee(cursor)); } finally { cursor.close(); } }
public void testUnionAll() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .unionAll(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(4, cursor.getCount()); cursor.moveToFirst(); assertEquals(cookieMonster, new Employee(cursor)); cursor.moveToNext(); assertEquals(cookieMonster, new Employee(cursor)); cursor.moveToNext(); assertEquals(elmo, new Employee(cursor)); cursor.moveToNext(); assertEquals(oscar, new Employee(cursor)); } finally { cursor.close(); } }