GreenDao deep queries with n:m relationships

Question

I have in my application a relationship that is designed like this: relationship description and I'm trying to select all the chats that have at least one user as a friend.

basically, I want to execute this query:

SELECT c.* FROM CHAT c, USER u, UserChats uc 
  WHERE c.type = myType 
  AND u.isFriend = 1 
  AND c.id = uc.chatId 
  AND u.id = uc.userId

I haven't managed to find a way to execute this in the GreenDao libraries and was hoping someone will be able to help me with this.

EDIT:
This is what I have up to now:

List<UsersChats> list = usersChatsDao.queryDeep(
    "WHERE T0." + UserDao.Properties.isFriend.collumnName + " = ? "+
    "AND T1." + ChatDao.Properties.type.collumName + " = ?",
    new String[] {"1", myType});

if(list != null && list.isEmpty() == false) {
    List<Chat> chats = new ArrayList<Chat>();
    for(UsersChats link : list) {
        chats.add(link.getChat());
    }
}

AlexS · Accepted Answer · 2013-11-04T13:11:36.443

2

Since grrendao doesn't implement QueryBuilder.join()-methods at the moment, I think your solution is one of the best you can get right now, as it uses joins internally.

There are only minor drawbacks to it:

you potentially query more tables than you actually need
you have to iterate through a potentially large list
you cannot use listLazy()

Another way would be to use some query like this (presumed IsFriend is an int-column and myType fits to ChatDao.Properties.type:

Query<Chat> qc = chatDao.queryRawCreate(
      " LEFT JOIN "+UserChatsDao.TABLENAME+" UC"+
      " ON T."+ChatDao.Properties.id.columnName+"=UC."+UserChats.Properties.chatId.columnName+
      " LEFT JOIN "+UserDao.TABLENAME+" U"+
      " ON UC."+UserChats.Properties.userId.columnName+"=U."UserDao.Properties.id.columnName+
      " WHERE U."UserDao.Properties.isFriend.columnName+"=?"+
      " AND T."+ChatDao.Properties.type.columnName+"=?", 1, myType);

Or (probably less performant):

Query<Chat> qc = chatDao.queryRawCreate(
      " , "+UserChatsDao.TABLENAME+" UC"+
      " , "+UserDao.TABLENAME+" U"+
      " WHERE T."+ChatDao.Properties.type.columnName+"=?"+
      " AND U."+UserDao.Properties.isFriend.columnName+"=?"+
      " AND T."+ChatDao.Properties.id.columnName+"=UC."+UserChats.Properties.chatId.columnName+
      " AND U."UserDao.Properties.id.columnName+"=UC."+UserChats.Properties.userId.columnName, myType, 1);

Then you can use the desired list()-methods:

qc.list();
qc.listLazy();
...

edited Nov 04 '13 at 13:11

answered Nov 04 '13 at 09:44

AlexS

5,295
3
38
54

That's right and using it is on purpose. `queryRawCreate()` builds a query like this `SELECT T.* FROM YourTable T`. So you can extend the `FROM`-clause and add a `where`-clause. – AlexS Nov 04 '13 at 12:22
I haven't tried it with joins yet. At the moment I am using subselects like described on the [greendao-website](http://greendao-orm.com/documentation/queries/) (See section "raw queries"). – AlexS Nov 04 '13 at 12:37
If the query doesn't work you can try to evalutae the correctness of the SQL using SQLiteManager or similar-tools. As said before `queryRawCreate`just puts `SELECT T.* FROM Chat T`in front. I changed the query to use `LEFT JOIN` instead of `JOIN` and added a query without any `JOIN`s. – AlexS Nov 06 '13 at 12:52
Is it possible to get both table columns in select? – Anuj Oct 08 '14 at 06:02
1

@Anuj Since the result of a query is either a single object or a list of objects of a specific class, only those columns are selected that fit to this class. And since your generated classes are representations of exactly one table only the columns of the corresponding table can be selected. But of course you can write a normal query "outside" of greendao. – AlexS Oct 09 '14 at 19:38

GreenDao deep queries with n:m relationships

1 Answers1