Android Realm Query

Question

I love Realm, the ease of use is so good, but sometimes i struggle in finding a way of doing a specific query.

Supposing i would want to query a a table(RealmObject) and get all the results matching a column name (variable), and i want that to be unique for each id, and also the latest record for a given variable name. For instance:

 realm.where(RealmMessages.thisClass)
                .distinctAsync(RealmMessages.FROM_TABLE)
                .addChangeListener(listener)

This will get me all the messages filtered by each unique 'from' field. But this won't work, as i want the latest by 'date' and unique by 'from'.

How would i approach to do this with realm»? Without manually querying all the objects and tho defeating the purpose of 'lazy objects' ?

Answer 1

You can Sort your results from the Realm using the "realmQuery.findAll(... Sort[] ... Fields[] ...)" method. Also you can do a multi-query by use groups and "or".

You can also use the "distinct()" method to get the "id" repeated only 1 time

I don't know if this is useful for you, but I did a class and some methods that let me do "fast & automatic queries on Realm". These are the files:

Put this in the same package of your "Realm Utils Class" (like a "RealmHelper" in which you instantiate your "Realm" instance and where you have all your methods which queries the realm ecc...

public class RealmQueriesData<T> {

    private List<QueryData> mQueries;
    private QueryFind mFind;
    private Class<T> mResultsClass;

    /* START Factory Methods */
    public static <T> RealmQueriesData<T> newInstanceFindEqualsTo(Class<T> resultsClass, String fieldName, Object value, Case mCase){
        return new RealmQueriesData<>(
            resultsClass,
            QueryData.QUERY_EQUAL, fieldName, value, QueryFind.FIND_ALL, mCase
        );
    }

    public static <T> RealmQueriesData<T> newInstanceCountEqualsTo(Class<T> resultsClass, String fieldName, Object value, Case mCase){
        return new RealmQueriesData<>(
            resultsClass,
            new int[]{QueryData.QUERY_EQUAL, QueryData.QUERY_COUNT}, new String[]{fieldName, null},
            new Object[]{value, null}, new Case[]{mCase, null}
        );
    }

    public static <T> RealmQueriesData<T> newInstanceFindNotIn(Class<T> resultsClass, String fieldName, Object[] valuesIn){
        return new RealmQueriesData<>(
            resultsClass,
            QueryData.QUERY_NOT_EQUAL, fieldName, valuesIn, QueryFind.FIND_ALL
        );
    }
    /* END Factory Methods */

    /* START Constructor Methods */
    public RealmQueriesData(Class<T> resultsClass,
                            int[] queryTypes, String[] fieldNames, Object[] queryValues, Case[] mCases){
        this.mResultsClass = resultsClass;
        this.mQueries = new ArrayList<>();
        for(int i = 0; i < queryTypes.length; i++){
            int type = queryTypes[i];
            String field = fieldNames[i];
            Object value = queryValues[i];
            Case mCase = mCases[i];
            this.mQueries.add(
                new QueryData(type, field, value, mCase)
            );
        }
    }

    public RealmQueriesData(Class<T> resultsClass, int queryType, String fieldName, Object[] queryValuesIn, int findType){
        this.mResultsClass = resultsClass;
        this.mQueries = new ArrayList<>();
        this.mQueries.add(
            new QueryData(queryType, fieldName, queryValuesIn)
        );
        this.mFind = new QueryFind(findType);
    }

    public RealmQueriesData(Class<T> resultsClass, int queryType, String fieldName, Object queryValue, int findType, Case mCase){
        this.mResultsClass = resultsClass;
        this.mQueries = new ArrayList<>();
        this.mQueries.add(
            new QueryData(queryType, fieldName, queryValue, mCase)
        );
        this.mFind = new QueryFind(findType);
    }

    public RealmQueriesData(Class<T> resultsClass){
        this.mResultsClass = resultsClass;
        this.mFind = new QueryFind();
    }
    /* END Constructor Methods */

    /* START Getter & Setter Methods */
    public List<QueryData> getQueries() {
        return mQueries;
    }

    public void setQueries(List<QueryData> queries) {
        this.mQueries = queries;
    }

    public QueryFind getFind() {
        return mFind;
    }

    public void setFind(QueryFind find) {
        this.mFind = find;
    }

    public Class<T> getResultsClass(){
        return mResultsClass;
    }

    public void setResultsClass(Class<T> resultClass){
        this.mResultsClass = resultClass;
    }
    /* END Getter & Setter Methods */

    /* START Utils Methods */
    public void addQuery(QueryData q){
        mQueries.add(q);
    }

    public void addQuery(int type, String fieldName, Object value, Case mCase){
        mQueries.add(new QueryData(type, fieldName, value, mCase));
    }

    public void addQuery(int type, String fieldName, Object value){
        mQueries.add(new QueryData(type, fieldName, value));
    }

    public void addQuery(int type, String fieldName, Object value, Object valueTo){
        mQueries.add(new QueryData(type, fieldName, value, valueTo));
    }

    public void addQuery(int type, String fieldName, Object[] valueIn){
        mQueries.add(new QueryData(type, fieldName, valueIn));
    }

    public void addQuery(int type, String fieldName){
        mQueries.add(new QueryData(type, fieldName));
    }
    /* END Utils Methods */

    @Override
    public String toString() {
        return "RealmQueriesData{" +
            "mQueries=" + mQueries.toString() +
            ", mFind=" + mFind.toString() +
            ", mResultsClass=" + mResultsClass.toString() +
            '}';
    }

}

class QueryData {

    // QueryData Tipes
    public static final int QUERY_NEGATION = 0x0;
    public static final int QUERY_OR = 0x1;
    public static final int QUERY_BEGIN_GROUP = 0x2;
    public static final int QUERY_END_GROUP = 0x3;
    public static final int QUERY_AVERAGE = 0x4;
    public static final int QUERY_COUNT = 0x5;
    public static final int QUERY_DISTINCT = 0x6;
    public static final int QUERY_MAX = 0x7;
    public static final int QUERY_MAX_DATE = 0x8;
    public static final int QUERY_MIN = 0x9;
    public static final int QUERY_MIN_DATE = 0xA;
    public static final int QUERY_SUM = 0xB;
    public static final int QUERY_EQUAL = 0xC;
    public static final int QUERY_NOT_EQUAL = 0xD;
    public static final int QUERY_CONTAINS = 0xE;
    public static final int QUERY_IS_NULL = 0xF;
    public static final int QUERY_NOT_NULL = 0x10;
    public static final int QUERY_LESS = 0x11;
    public static final int QUERY_GREATER = 0x12;
    public static final int QUERY_GREATER_EQUAL = 0x13;
    public static final int QUERY_LESS_EQUAL = 0x14;
    public static final int QUERY_BETWEEN = 0x15;
    public static final int QUERY_LIKE = 0x16;
    public static final int QUERY_BEGINS = 0x17;
    public static final int QUERY_ENDS = 0x18;
    public static final int QUERY_IN = 0x19;
    public static final int QUERY_IS_EMPTY = 0x1A;
    public static final int QUERY_NOT_EMPTY = 0x1B;

    int mType;
    String mFieldName;
    Object mValue;
    Object mValueTo;
    Object[] mValueIn;
    Case mCase;

    /* START Constructor Methods */
    public QueryData(int type, String fieldName, Object value, Case mCase){
        this.mType = type;
        this.mFieldName = fieldName;
        this.mValue = value;
        this.mCase = mCase;
    }

    public QueryData(int type, String fieldName, Object value){
        this.mType = type;
        this.mFieldName = fieldName;
        this.mValue = value;
    }

    public QueryData(int type, String fieldName, Object valueFrom, Object valueTo){
        this.mType = type;
        this.mFieldName = fieldName;
        this.mValue = valueFrom;
        this.mValueTo = valueTo;
    }

    public QueryData(int type, String fieldName, Object[] valueIn){
        this.mType = type;
        this.mFieldName = fieldName;
        this.mValueIn = valueIn;
    }

    public QueryData(int type, String fieldName){
        this.mType = type;
        this.mFieldName = fieldName;
    }
    /* END Constructor Methods */

    /* START Getter & Setter Methods */
    public int getType() {
        return mType;
    }

    public void setType(int type) {
        this.mType = type;
    }

    public String getFieldName() {
        return mFieldName;
    }

    public void setFieldName(String fieldName) {
        this.mFieldName = fieldName;
    }

    public Object getValue(){
        return mValue;
    }

    public void setValue(Object value){
        this.mValue = value;
    }

    public Object getValueTo() {
        return mValueTo;
    }

    public void setValueTo(Object valueTo) {
        this.mValueTo = valueTo;
    }

    public Object[] getValueIn() {
        return mValueIn;
    }

    public void setValueIn(Object[] valueIn) {
        this.mValueIn = valueIn;
    }

    public Case getCase() {
        return mCase;
    }

    public void setCase(Case mCase) {
        this.mCase = mCase;
    }
    /* END Getter & Setter Methods */

    @Override
    public String toString() {
        return "QueryData{" +
            "mType=" + mType +
            ", mFieldName='" + mFieldName + '\'' +
            ", mValue=" + mValue +
            ", mValueTo=" + mValueTo +
            ", mValueIn=" + Arrays.toString(mValueIn) +
            ", mCase=" + mCase +
            '}';
    }

}

class QueryFind {

    // QueryFind Types
    public static final int FIND_ALL = 0x1;
    public static final int FIND_ALL_SORTED_FIELD = 0x2;
    public static final int FIND_ALL_SORTED_FIELD_SORT = 0x3;
    public static final int FIND_ALL_SORTED_ARRAYS = 0x4;

    int mType;
    List<String> mSortFields;
    List<Sort> mSorts;
    boolean mRetAll;

    /* START Constructor Methods */
    public QueryFind(int type, List<String> sortFields, List<Sort> sorts, boolean retAll){
        this.mType = type;
        this.mSortFields = sortFields != null ? sortFields : new ArrayList<String>();
        this.mSorts = sorts != null ? sorts : new ArrayList<Sort>();
        this.mRetAll = retAll;
    }

    public QueryFind(int type, String sortField, Sort sort, boolean retAll){
        this.mType = type;
        this.mSortFields = new ArrayList<>();
        this.mSortFields.add(sortField);
        this.mSorts = new ArrayList<>();
        this.mSorts.add(sort);
        this.mRetAll = retAll;
    }

    public QueryFind(int type){
        this.mType = type;
    }

    public QueryFind(){
        this.mRetAll = true;
    }
    /* END Constructor Methods */

    /* START Getter & Setter Methods */
    public int getType() {
        return mType;
    }

    public void setType(int type) {
        this.mType = type;
    }

    public List<String> getSortFields() {
        return mSortFields;
    }

    public void setSortFields(List<String> sortFields) {
        this.mSortFields = sortFields;
    }

    public List<Sort> getSorts() {
        return mSorts;
    }

    public void setSorts(List<Sort> sorts) {
        this.mSorts = sorts;
    }

    public boolean getRetAll() {
        return mRetAll;
    }

    public void setRetAll(boolean retAll) {
        this.mRetAll = retAll;
    }
    /* END Getter & Setter Methods */

    /* START Utils Methods */
    public void addSort(String sortField, Sort sort){
        mSortFields.add(sortField);
        mSorts.add(sort);
    }
    /* END Utils Methods */

    @Override
    public String toString() {
        return "QueryFind{" +
            "mType=" + mType +
            ", mSortFields=" + mSortFields +
            ", mSorts=" + mSorts +
            ", mRetAll=" + mRetAll +
            '}';
    }

}

Then in you "Realm Library File" (like "RealmHelper") add these methods to do the queries by passing them a "RealmQueriesData" instance:

public class RealmHelper {

    protected Realm mRealm;
    // Instance to have only 1 Realm open 4 every thread in your app
    protected static RealmHelper mInstance;  

    [....]

/* START Realm Query Methods */
    // Like the one below but return a List of objects and not a RealmResults
    public <T> List<T> doQueryOnRealmAsList(RealmQueriesData filters){
        return (List<T>) toList(doQueryOnRealm(filters));
    }

    // With this you can do every type of query which return values with
    // "realmQuery.findAll()" methods
    public <T> RealmResults<T> doQueryOnRealm(RealmQueriesData filters){
        RealmResults<T> ret = null;
        if(filters != null){
            List<QueryData> queries = filters.getQueries();
            QueryFind find = filters.getFind();
            if(queries != null && queries.size() > 0) {
                RealmQuery<T> realmQuery = mRealm.where(filters.getResultsClass());
                for(QueryData query : queries){
                    if(query.getType() == QueryData.QUERY_DISTINCT){
                        ret = realmQuery.distinct(query.getFieldName());
                    } else {
                        realmQuery = getRealmQueryFromQueryData(realmQuery, query);
                    }
                }
                if(find != null) {
                    ret = getRealmQueryResults(realmQuery, find);
                }
            } else {
                if(find != null && find.getRetAll()){
                    ret = (RealmResults<T>) findAllObject(filters.getResultsClass());
                }
            }
        }
        return ret;
    }

    // With this you can do every type of query which return a single value
    // with "realmQuery.findFirst()" method.
    public <T> T doQueryOnRealmSingleResult(RealmQueriesData filters){
        T ret = null;
        if(filters != null){
            List<QueryData> queries = filters.getQueries();
            QueryFind find = filters.getFind();
            if(queries != null && queries.size() > 0){
                RealmQuery<T> realmQuery = mRealm.where(filters.getResultsClass());
                for(QueryData query : queries){
                    realmQuery = getRealmQueryFromQueryData(realmQuery, query);
                }
                ret = realmQuery.findFirst();
            }
        }
        return ret;
    }

    // For queries whose return a Numerical value using SQL built-in functions
    // like "max, count, min, average ..."
    public Object doMathQueryOnRealm(RealmQueriesData filters){
        Object ret = null;
        if(filters != null){
            List<QueryData> queries = filters.getQueries();
            QueryData last = queries.get(queries.size() - 1);
            queries.remove(last);
            if(queries.size() > 0){
                RealmQuery realmQuery = mRealm.where(filters.getResultsClass());
                for(QueryData query : queries){
                    realmQuery = getRealmQueryFromQueryData(realmQuery, query);
                }
                ret = getMathQueryResult(realmQuery, last);
            }
        }
        return ret;
    }

private <T> RealmQuery<T> getRealmQueryFromQueryData(RealmQuery realmQuery, QueryData query){
        String field; Object value; Case mCase;
        switch(query.getType()){
            case QueryData.QUERY_NEGATION:
                realmQuery.not();
                break;
            case QueryData.QUERY_OR:
                realmQuery.or();
                break;
            case QueryData.QUERY_BEGIN_GROUP:
                realmQuery.beginGroup();
                break;
            case QueryData.QUERY_END_GROUP:
                realmQuery.endGroup();
                break;

            case QueryData.QUERY_EQUAL:
                field = query.getFieldName();
                value = query.getValue();
                if(value instanceof String){
                    mCase = query.getCase();
                    realmQuery.equalTo(field, value.toString(), mCase);
                } else if(value instanceof Integer){
                    realmQuery.equalTo(field, (Integer) value);
                } else if(value instanceof Double){
                    realmQuery.equalTo(field, (Double) value);
                } else if(value instanceof Date){
                    realmQuery.equalTo(field, (Date) value);
                }
                break;
            case QueryData.QUERY_NOT_EQUAL:
                field = query.getFieldName();
                value = query.getValue();
                if(value instanceof String){
                    mCase = query.getCase();
                    realmQuery.notEqualTo(field, value.toString(), mCase);
                } else if(value instanceof Integer){
                    realmQuery.notEqualTo(field, (Integer) value);
                } else if(value instanceof Double){
                    realmQuery.notEqualTo(field, (Double) value);
                } else if(value instanceof Date){
                    realmQuery.notEqualTo(field, (Date) value);
                }
                break;

            case QueryData.QUERY_CONTAINS:
                field = query.getFieldName();
                value = query.getValue();
                mCase = query.getCase();
                if(mCase != null){
                    realmQuery.contains(field, value.toString(), mCase);
                } else {
                    realmQuery.contains(field, value.toString());
                }
                break;

            case QueryData.QUERY_IS_NULL:
                realmQuery.isNull(query.getFieldName());
                break;
            case QueryData.QUERY_NOT_NULL:
                realmQuery.isNotNull(query.getFieldName());
                break;

            case QueryData.QUERY_LESS:
                field = query.getFieldName();
                value = query.getValue();
                if(value instanceof Integer){
                    realmQuery.lessThan(field, (Integer) value);
                } else if(value instanceof Double){
                    realmQuery.lessThan(field, (Double) value);
                } else if(value instanceof Date){
                    realmQuery.lessThan(field, (Date) value);
                }
                break;
            case QueryData.QUERY_GREATER:
                field = query.getFieldName();
                value = query.getValue();
                if(value instanceof Integer){
                    realmQuery.greaterThan(field, (Integer) value);
                } else if(value instanceof Double){
                    realmQuery.greaterThan(field, (Double) value);
                } else if(value instanceof Date){
                    realmQuery.greaterThan(field, (Date) value);
                }
                break;
            case QueryData.QUERY_GREATER_EQUAL:
                field = query.getFieldName();
                value = query.getValue();
                if(value instanceof Integer){
                    realmQuery.greaterThanOrEqualTo(field, (Integer) value);
                } else if(value instanceof Double){
                    realmQuery.greaterThanOrEqualTo(field, (Double) value);
                } else if(value instanceof Date){
                    realmQuery.greaterThanOrEqualTo(field, (Date) value);
                }
                break;
            case QueryData.QUERY_LESS_EQUAL:
                field = query.getFieldName();
                value = query.getValue();
                if(value instanceof Integer){
                    realmQuery.lessThanOrEqualTo(field, (Integer) value);
                } else if(value instanceof Double){
                    realmQuery.lessThanOrEqualTo(field, (Double) value);
                } else if(value instanceof Date){
                    realmQuery.lessThanOrEqualTo(field, (Date) value);
                }
                break;
            case QueryData.QUERY_BETWEEN:
                field = query.getFieldName();
                value = query.getValue();
                if(value instanceof Integer){
                    realmQuery.between(field, (Integer) value, (Integer) query.getValueTo());
                } else if(value instanceof Double){
                    realmQuery.between(field, (Double) value, (Double) query.getValueTo());
                } else if(value instanceof Date){
                    realmQuery.between(field, (Date) value, (Date) query.getValueTo());
                }
                break;

            case QueryData.QUERY_LIKE:
                field = query.getFieldName();
                value = query.getValue();
                mCase = query.getCase();
                if(mCase != null){
                    realmQuery.like(field, value.toString(), mCase);
                } else {
                    realmQuery.like(field, value.toString());
                }
                break;
            case QueryData.QUERY_BEGINS:
                field = query.getFieldName();
                value = query.getValue();
                mCase = query.getCase();
                if(mCase != null){
                    realmQuery.beginsWith(field, value.toString(), mCase);
                } else {
                    realmQuery.beginsWith(field, value.toString());
                }
                break;
            case QueryData.QUERY_ENDS:
                field = query.getFieldName();
                value = query.getValue();
                mCase = query.getCase();
                if(mCase != null){
                    realmQuery.endsWith(field, value.toString(), mCase);
                } else {
                    realmQuery.endsWith(field, value.toString());
                }
                break;

            case QueryData.QUERY_IN:
                field = query.getFieldName();
                Object[] values = query.getValueIn();
                if(values instanceof String[]){
                    realmQuery.in(field, (String[]) values);
                } else if(values instanceof Integer[]){
                    realmQuery.in(field, (Integer[]) values);
                } else if(values instanceof Double[]){
                    realmQuery.in(field, (Double[]) values);
                } else if(values instanceof Date[]){
                    realmQuery.in(field, (Date[]) values);
                }
                break;

            case QueryData.QUERY_IS_EMPTY:
                realmQuery.isEmpty(query.getFieldName());
                break;
            case QueryData.QUERY_NOT_EMPTY:
                realmQuery.isNotEmpty(query.getFieldName());
                break;
        }
        return realmQuery;
    }

    private Object getMathQueryResult(RealmQuery realmQuery, QueryData query){
        Object ret = null;
        switch(query.getType()){
            case QueryData.QUERY_AVERAGE:
                ret = realmQuery.average(query.getFieldName());
                break;
            case QueryData.QUERY_COUNT:
                ret = realmQuery.count();
                break;
            case QueryData.QUERY_MAX:
                ret = realmQuery.max(query.getFieldName());
                break;
            case QueryData.QUERY_MAX_DATE:
                ret = realmQuery.maximumDate(query.getFieldName());
                break;
            case QueryData.QUERY_MIN:
                ret = realmQuery.min(query.getFieldName());
                break;
            case QueryData.QUERY_MIN_DATE:
                ret = realmQuery.minimumDate(query.getFieldName());
                break;
            case QueryData.QUERY_SUM:
                ret = realmQuery.sum(query.getFieldName());
                break;
        }
        return ret;
    }

    private <T> RealmResults<T> getRealmQueryResults(RealmQuery<T> realmQuery, QueryFind find){
        RealmResults<T> ret = null;
        switch(find.getType()){
            case QueryFind.FIND_ALL:
                ret = realmQuery.findAll();
                break;
            case QueryFind.FIND_ALL_SORTED_FIELD:
                ret = realmQuery.findAllSorted(find.getSortFields().get(0));
                break;
            case QueryFind.FIND_ALL_SORTED_FIELD_SORT:
                ret = realmQuery.findAllSorted(find.getSortFields().get(0), find.getSorts().get(0));
                break;
            case QueryFind.FIND_ALL_SORTED_ARRAYS:
                ret = realmQuery.findAllSorted(find.getSortFields().toArray(new String[]{}), find.getSorts().toArray(new Sort[]{}));
                break;
        }
        return ret;
    }
    /* END Realm Query Methods */
}

Normally to do a query I create a new "static Factory Method" in the "RealmQueriesData" class so I can do fast queries on realm just by invoking 1 method.

// GET all RealmObjects in your Realm by searching for all 
// realm objects saved that have a specific value for a specific. 
// Remember that the "field name" must be the "field attribute name" in the class
// and not the "SerializedName" label value!
// (As you can see in the method which do the query the "Case SENSITIVE / INSENSITIVE"
// is used only if you query a field which is a String type, if you are querying 
// another type, like Integer/Double/Date, you can set the "CASE" to "null".
RealmResults<YourRealmObject> listCount = doQueryOnRealm(
                    RealmQueriesData.newInstanceFindEqualsTo(
                        YourRealmObject.class, "yourRealmObject Field Name", <field value of YourRealmObject>, <Case (SENSITIVE / INSENSITIVE)>
                    )
                );


// Get a list of all RealmObjects whose have a specific value in a specific field
RealmResults<YourRealmObject> listObjs = doQueryOnRealm(
                    RealmQueriesData.newInstanceFindEqualsTo(
                        YourRealmObject.class, "Field Name of YourRealmObject", <Value of YourRealmObject field>, <Case (SENSITIVE / INSENSITIVE)>
                    )
                );


// Like a COUNT query which look for all records that have a specific value
// in a specific column.
long count = (Long) doMathQueryOnRealm(
            RealmQueriesData.newInstanceCountEqualsTo(
                YourRealmObject.class, "Field Name of YourRealmObject", <Value of YourRealmObject field>, <Case (SENSITIVE / INSENSITIVE)>
            )
        );

I had to do different methods to query the realm because the realm queries can return different values, so I had to do 3 methods for every kind of return value:

• multi-values return ( RealmResults / List ) (findAll method)
• single-value return ( T ) (findFirst method)
• numerical-value return ( Object ) ( max, min, count, ... queries)

I hope this is Helpful for you.

Bye! (z3r0)