I am trying to execute a query which has a ternary operator inside a select statement:
SELECT id,
memory_id,
CASE
WHEN word_to =? THEN word_from
ELSE word_to
end
FROM translations
WHERE word_to =?
OR word_from =?
How can I build such a query using a QueryBuilder object? I tried:
public List<Translation> findMeanings(long wordId) {
try {
Dao<Translation, Long> translations = getDao(Translation.class);
QueryBuilder<Translation, Long> queryBuilder = translations.queryBuilder();
String id = Long.toString(wordId);
queryBuilder.selectRaw(DBColumns.ID, DBColumns.MEMORY_ID, "CASE WHEN word_to="+id+" THEN word_from ELSE word_to END");
queryBuilder.where().eq(DBColumns.WORD_TO, id).or().eq(DBColumns.WORD_FROM, id);
return queryBuilder.query();
} catch (SQLException e) {
Logger.error("db", e.getMessage(), e);
}
return new ArrayList<Translation>();
}
But I am getting an exception:
Could not compile this SELECT_RAW statement since the caller is expecting a SELECT statement. Check your QueryBuilder methods.
Translation class:
@DatabaseTable(tableName = DatabaseTables.TABLE_TRANSLATIONS)
public class Translation extends BaseEntity {
@DatabaseField(foreign = true, foreignColumnName = DBColumns.ID, columnName = DBColumns.WORD_TO)
private Word wordTo;
@DatabaseField(foreign = true, foreignColumnName = DBColumns.ID)
private Memory memory;
//...
where BaseEntity is:
public class BaseEntity {
@DatabaseField(generatedId = true, unique = true)
protected long id;
//....
Could not compile this SELECT_RAW statement since the caller is expecting a SELECT statement. Check your QueryBuilder methods.
As soon as you call qb.selectRaw(...) you will need to use qb.queryRaw(...) instead of query(). This is mentioned in the javadocs for selectRaw(...):
Add raw columns or aggregate functions (COUNT, MAX, ...) to the query. This will turn the query into something only suitable for the Dao.queryRaw(String, String...) type of statement. This can be called multiple times to add more columns to select.
Typically the selectRaw(...) is used with some sort of aggregation function like COUNT() or MAX() and is therefore not compatible with an entity type returned by query().
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments