Selects

Selects

Selects are an important part of every ORM, and CPOrm does it in a powerful and flexible way. The select class is what you will use to retrieve any data in the ORM. This is not the only way though, you can query your data through conventional means as well, but this will make your life easier.

The select class requires the following things:

• Set the table you would like to query.
• Set any filters you would like applied.
• Choose how you would like the data to be returned.

This will give you an indication of some of the ways selects can be constructed.

Basic Select

//Select with no filter
Select.from(User.class).queryAsList();

//Select with basic filter
Select.from(User.class).whereEquals("_id", 1).first()

//Select with limit
Select.from(User.class).limit(1000).queryAsCursor();

//Select with ordering
Select.from(User.class).sortAsc("given_name").limit(200).queryAsIterator();

The basic select can apply any filters, limits, or sorting that might be required, you then specify how you would like to retrieve the information, the options include:

• List
• Cursor
• Iterator
• First/Last
• Count

Advance Filters

//Advance filter with parenthesis
Select.from(User.class)
 .and().like("family_name", "Doe")
 .openBracket()
  .or().equals("given_name", "John")
  .or().equals("given_name", "Soe")
 .closeBracket()
 .queryAsCursor();

//Filter with criterions
DataFilterCriteria criteria = new DataFilterCriteria();
DataFilterCriterion findJohn = new DataFilterCriterion("given_name", DataFilterOperator.LIKE, "John");
DataFilterCriterion findSoe = new DataFilterCriterion("given_name", DataFilterOperator.LIKE, "Soe");

criteria.addClause(findJohn, DataFilterConjunction.OR);
criteria.addClause(findSoe , DataFilterConjunction.OR);

Select.from(User.class)
 .and().like("family_name", "Doe")
 .where(criteria)
 .queryAsCursor();

The above to methods will return the exact same results. With the first method the select class builds the criteria/criterion for you, the second method, although more verbose, does add a lot more flexibility, because the criteria can be built up in various parts of the application and be added to a select at any single point.

Data Filter Criteria behave like parenthesis, the wrap all of the filter clauses added to them in parenthesis. Where the Data Filter Criterion is the actual filter value that needs to be applied to a column. So the resulting query will look something like this

SELECT * FROM USER WHERE family_name LIKE "Doe" AND (given_name = "John" OR given_name = "Soe")

The Select, DataFilterCriteria, and DataFilterCriterion classes all extend the DataFilterClause interface, meaning you can have inner selects in your queries as well

Select roleSelect = Select.from(Role.class).whereEquals("role_name", "admin").include("_id");

Select userSelect = Select.from(User.class).and().in("role_id", roleSelect).queryAsList();

The above code builds a select for the role class, and limits the result columns to only the id column. We then query the user class where the role id for the user is in the results for the role select. The SQL will look something like this

SELECT * FROM USER WHERE role_id IN (SELECT _ID FROM ROLE WHERE role_name = "admin)

There are many more possibilities that I will later include, but hopefully this will be enough to get you started on some of the advanced select features the ORM provides.