GreenDAO Left Join

Question

Consider I have a model : Company 1 - N Person

I want to display a list of persons with their names and their company's name. But not every person has a company.

I order to avoid that every call to person.getCompany() results in a new SQL query so I was thinking about adding a join :

QueryBuilder<Person> queryBuilder = session.getPersonDao().queryBuilder();
queryBuilder.join(PersonDao.Properties.CompanyId, Company.class);
queryBuilder.list()

The problem is that I only get the persons with a company because the generated query uses JOIN which is equivalent to INNER JOIN. I think I would need LEFT JOIN to also get the persons without a company.

It doesn't seem GreenDAO supports LEFT JOIN right now. Is there another way to make the request without doing a raw query ?

If you are open to using a different ORM, I can suggest JDXA ORM (http://softwaretree.com/v1/products/jdxa/jdxa.html). JDXA will fetch all the qualified Person objects and any associated Company objects. If an associated Company object does not exist, a Person object will still be fetched. — Damodar Periwal, Aug 26 '16 at 18:16

score 0 · Answer 1 · answered Aug 29 '16 at 19:25

0

It can be achieved without writing a raw query.

Here is the snippet:

QueryBuilder<Person> qb = userDao.queryBuilder();
List<Person> outputPersonList = userDao.queryDeep(" ", null);

It compiles at low level as:

SELECT T."_id",T."NAME",T."COMPANY_ID",T0."_id",T0."NAME" 
    FROM PERSON T 
    LEFT JOIN COMPANY T0 
    ON T."COMPANY_ID"=T0."_id"

Of course if the Person model has other relations, all of them will be fetched, so this SQL query would be slow.

However, it basically does, what you expect.

answered Aug 29 '16 at 19:25

R. Zagórski

20,020
5
65
90

I don't think queryDeep() would not be very efficient in my case. And I would also need a where clause, which is somewhat equivalent to a raw query when using queryDeep(). – Yohan D Aug 30 '16 at 11:44
Yes, you would need to pass hand written Where clause similar to the one described in [Greendao samples](https://github.com/greenrobot/greenDAO/blob/1d267398b17ec5e24afffc66e295b469718902a8/tests/DaoTest/src/androidTest/java/org/greenrobot/greendao/daotest/entity/RelationEntityTest.java#L166) – R. Zagórski Aug 30 '16 at 13:10

score 0 · Answer 2 · answered Mar 21 '18 at 09:48

For those who don't know how to do it with raw query:

String query = " LEFT JOIN COMPANY ON T.id = COMPANY.ID where SOME_CONDITIONS";
List<Person> persons = session.getPersonDao().queryRaw(query);

Greendao will insert SELECT T.COLUMN_1, ... T.COLUM_N from PERSON AS T part by itself.
So in query T is just alias name for Person table.

GreenDAO Left Join

2 Answers2