Deep GreenDao queries with n: m relationships

Question

I have relationships in my application that are created like this: 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 was unable to find a way to accomplish this in GreenDao libraries and was hoping that someone could help me.

EDIT:
This is what I have so far:

 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()); } } 

Answer 1

Since grrendao does not currently implement QueryBuilder.join() methods, I think your solution is one of the best that you can get right now because it uses internal joins.

There are only minor flaws:

• you are potentially querying more tables than you really need
• you need to iterate over a potentially large list
• you cannot use listLazy()

Another way would be to use such a query (suppose IsFriend matches int-column and myType matches 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 (possibly less productive):

 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(); ...