greendao sort by field in related table

Question

is there a way to sort by a field in a related table with greenDao? E.g. I have a table of cars and a table of drivers. Each car has a driver. Now I want to query for ( e.g. blue ) cars and sort by the name of the driver

Arnoldarnoldo

Answer 1

In QueryBuilder, there are methods to specify the sorting order. Look for methods starting with "order...", e.g. orderAsc(Property).

Farci

Oh wait, we don't do joins yet. ;) Guess you have to write some custom (raw) query for now. – Farci

yea just tried again after you answerd and got de.greenrobot.dao.DaoException: Property 'foo' is not part of bar Do you have an example on how to do it with a raw query? – Arnoldarnoldo

order methods are not available on a querybuilder.join it seems – Security

Answer 2

I play around with GreenDao at the moment too and hope my little addition to the comment in the first answer and the description in the queries part of the greenDao documentation helps.

The following snippet should work (didn't test it :)):

Query query = carsDao.queryRawCreate(   ", driver D WHERE T.COLOR='blue' AND T.DRIVER_ID=D._ID ORDER BY D.NAME ASC");

This creates internally a SQL similiar to this:

SELECT T.'id', T.'name', T.'color', T.'driver_id'
FROM cars T, driver D
WHERE T.COLOR='blue'
AND T.DRIVER_ID=D._ID
ORDER BY D.NAME ASC

The first part of the statement is created for you by the queryRawCreate method, the rest is the custom sql statement which was passed to queryRawCreate.

See this question if you wonder where the JOIN statement is.

Brume

Answer 3

You can use QueryBuilders with the (data access objects) DAOs that are generated by the Greendao ORM.

Define before you use (in Activities)

 ProductDao productDao;
 DaoSession daoSession;

You should place the DaoMaster and DaoSession in your application scope. Inside the onCreate() of the class that extends Application.

DaoMaster.DevOpenHelper helper = new DaoMaster.DevOpenHelper(getApplicationContext(), "app-db", null);
SQLiteDatabase db = helper.getWritableDatabase();
daoSession = new DaoMaster(db).newSession();

Initialize before you use

 daoSession = ((MyApplication) getApplication()).getDaoSession();
 productDao = daoSession.getProductDao();

You can sort the results like this to display in the activity.

private void refreshProducts() {
        switch (sorted_by){
            case SORT_BY_DATE:
                cardItems = productDao.queryBuilder().orderAsc(ProductDao.Properties.Id).list();
                setupRecyclerView();
                break;

            case SORT_BY_PRICE:
                cardItems = productDao.queryBuilder().orderDesc(ProductDao.Properties.Price).list();
                setupRecyclerView();
                break;

            case SORT_BY_POPULARITY:
                cardItems = productDao.queryBuilder().orderDesc(ProductDao.Properties.Name).list();
                setupRecyclerView();
                break;
        }
    }

Contrapositive

Answer 4

Easy solution with QueryBuilder.orderRaw() and Join.getTablePrefix()

My example code:

QueryBuilder.LOG_SQL = true;//Enable to see SQL result

  QueryBuilder<Item> query = daoSession.queryBuilder(Item.class);
  Join itemWithCollection = query.join(JoinItemWithCollection.class,
      JoinItemWithCollectionDao.Properties.ItemId);
  String joinedTable = itemWithCollection.getTablePrefix();
  Join collection = query.join(itemWithCollection, JoinItemWithCollectionDao.Properties.CollectionId,
      Collection.class, CollectionDao.Properties.Id);
  String orderCol = JoinItemWithCollectionDao.Properties.SomeOrderCol.columnName;
  collection.where(CollectionDao.Properties.Key.eq(collectionId));
  query.orderRaw(joinedTable+".\""+orderCol+"\" DESC");
  query.limit(limit);
  query.offset(from);

  List<Item> results = query.list();

Disadvantaged