Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Ormlite inner join on three tables

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

like image 462
Antonis Avatar asked Sep 04 '11 18:09

Antonis


1 Answers

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.

like image 57
Gray Avatar answered Sep 28 '22 01:09

Gray