Ormlite inner join on three tables

Question

i want to create an inner join on three tables like this one for example:

SELECT C.Description, D.ItemDescription
  FROM OrderDetailStatement AS D 
 INNER JOIN OrderHeaderStatement AS H 
    ON H.OrderHeaderStatementRefID = D.OrderHeaderStatementRefID 
 INNER JOIN customers AS C 
    ON H.CustomerRefID = C.CustomerRefID
 WHERE (D.MixedValue > 1000)

but i'm a little bit confused, could you please provide me a walkthrough?

thanks in advance

I just tried with pure sql, because i still don't get it-how to write the query using ormlite ormlite. I just need an example. Any example involving 3 tables. — Antonis, Sep 04 '11 at 20:47
Note that we are adding basic JOIN support in 4.42 @Antonis. I've updated my answer. — Gray, Sep 19 '12 at 20:52

Gray · Accepted Answer · 2016-01-22T22:36:55.313

36

ORMLite now supports simple JOIN statements. You can do something like the following:

// start the order header query
QueryBuilder<OrderHeader, Integer> orderHeaderQb = orderHeaderDao.queryBuilder();
QueryBuilder<Customer, Integer> customerQb = customerDao.queryBuilder();
// join with the order query
orderHeaderQb.join(customerQb);
// start the order statement query
QueryBuilder<OrderStatement, Integer> orderStatementQb =
    orderStatementDao.queryBuilder();
orderStatementQb.where().gt("mixedvalue", 100);
// join with the order-header query
orderStatementQb.join(orderHeaderQb);
List<OrderStatement> orderStatementQb.query();

Notice, however, that you can only get entities from the query builder using this mechanism. If you want to get your two description fields from different objects then you would have to still use a raw-query.

There is support for "raw queries" including the Dao.queryRaw() method where you can use your own SQL. I suspect you've found them already. Here are the docs for raw queries.

edited Jan 22 '16 at 22:36

answered Sep 06 '11 at 12:41

Gray

115,027
24
293
354

http://ormlite.com/javadoc/ormlite-core/doc-files/ormlite_3.html#index-select-arguments – LuminiousAndroid Oct 27 '14 at 08:14
1

From @haddr: As of now, if you want fully hydrated entities from other tables, you can use `foreignAutoRefresh` on the `@DatabaseField` annotation on foreign keys. With that, when queried, all foreign entities will have their fields retrieved. – Gray Jan 22 '16 at 22:37
@Gray how can we do a raw query returning fields from different objects? I'm trying to find an answer somewhere but cannot find it... – rmpt Sep 28 '16 at 11:15

Ormlite inner join on three tables

1 Answers1

Linked