8

Using ORMLite for Android, I need to build a query that returns orders by order id or by customer name. Please consider the following class declarations:

@DatabaseTable(tableName = "order")
public class Order {
    @DatabaseField(generatedId = true)
    private Long id;

    @DatabaseField(foreign = true, canBeNull = false, foreignAutoRefresh = true, columnName = "customer_id")
    private Customer customer;

    // default constructor, getters and setters...
}

@DatabaseTable(tableName = "customer")
public class Customer {
    @DatabaseField(generatedId = true)
    private Long id;

    @DatabaseField
    private String name;

    // default constructor, getters and setters...
}

The raw SQL I'm looking for would be something like this:

SELECT 
    o.* 
FROM
    order o
    JOIN customer c on
        o.customer_id = c.id
WHERE
    (o.id = ?) OR (c.name = ?)

What is the best way to do this using ORMLite?

itmartins
  • 83
  • 1
  • 3

2 Answers2

13

ORMLite now supports simple join queries.

So your query would look something like:

QueryBuilder<Customer, Integer> customerQb = customerDao.queryBuilder();
SelectArg nameSelectArg = new SelectArg();
// this gives the c.name = ?
customerQb.where().eq("name", nameSelectArg);
QueryBuilder<Account, Integer> orderQb = orderDao.queryBuilder();
SelectArg idSelectArg = new SelectArg();
// this gives the o.id = ?
orderQb.where().eq("id", idSelectArg);
orderQb.join(customerQb);
// then you set the args and run the query
nameSelectArg.setValue("jim");
idSelectArg.setValue(1);
List<Order> results = orderQb.join(customerQb).query();
Gray
  • 115,027
  • 24
  • 293
  • 354
  • Gray, we just looked into OrmLite as a potential ORM on Android, and noticed that relations are loaded through separate queries instead of joins, thus suffering from the N+1 problem. What was the rationale behind implementing it that way? Currently it means that `queryForAll` on 100 `A`s that each have a `B` will issue 101 queries instead of one. 1 query to fetch all `A`s, then another 100 queries to fetch the `B` for every `A`. – mxk Mar 12 '13 at 19:01
  • The short answer is because it's not ORMHeavy @Matthias. I get some people complaining about it's code size and some of its missing features... – Gray Mar 12 '13 at 19:04
  • Okay fair enough. I think it's an odd omission though when there's so many other features. This is a common anti pattern and--I think--sort of defeats its own purpose since it requires the client to perform the mapping themselves unless they want to swallow the added cost of this (which is big and grows with table size) – mxk Mar 12 '13 at 19:08
  • 2
    Actually, we were thinking about adding this to the library since we like it so much overall. Would you not even consider accepting patches for this, i.e. do you think the library should behave the way it does? – mxk Mar 12 '13 at 19:10
  • 3
    Oh certainly @Matthias. I recently pushed ORMLite up to Github so branch away. I reserve the right to say that it's too much but I don't think the way it's working now is perfect. https://github.com/j256/ormlite-core/ – Gray Mar 12 '13 at 19:23
  • Wouldn't this give you an AND query? – Crake Aug 09 '13 at 15:33
  • @Matthias did you get anywhere with resolving the N+1 problem? Cheers! – Nolan Amy Jun 04 '14 at 01:25
  • @Nolan no we eventually decided that an ORM does not provide the flexibility we need going forward. We ended up cutting our own solution which uses what I call "fluent models" (or property sets) and talks straight to SQLite via a custom query builder. It also has an extension point to RxJava to push database content to observers through a RowMapper. This has turned out to be quite flexible and powerful so far. – mxk Jun 05 '14 at 08:28
  • @Matthias Ah, thanks! So a property set defines the schema of the model/table? Any chance of finding it on github? :) – Nolan Amy Jun 07 '14 at 00:16
  • @Nolan not quite -- a Property is an immutable, type safe representation of some field in your domain to which you can bind values, e.g. a book title. A PropertySet is a bag of those properties with set semantics. Both are parcelable value types. This is still very much WIP and not open source. The plan is to evolve this into a reactive database layer for Android, where subscribers (e.g. the UI) can listen in to RxJava channels, and get property sets pushed upstream whenever they change. Think marrying ContentObservers with RxJava, minus the horrible ContentProvider API. – mxk Jun 08 '14 at 08:45
  • 1
    @Matthias thanks again for the response - definitely a little over my head, but interesting. fwiw, I've been moving forward with using ORMLite ... might put together some partial solution to the N+1 issue... – Nolan Amy Jun 10 '14 at 22:31
0

No, JOINs are supported in ORMLite https://stackoverflow.com/a/7320091/323128 However, this reference will give a vision how to complete your task

Community
  • 1
  • 1
Maxim
  • 4,152
  • 8
  • 50
  • 77
  • I'm ok in working with raw queries in that case. Is it possible to get a List from a raw query? Or can I work just with GenericRawResults? – itmartins Jul 26 '12 at 16:11
  • DOC:queryRaw methods return a GenericRawResults object which represents a result as an array of strings, array of objects, or user mapped objects. -- So you can build a composition object which you will map on your result to get data in some sort of container better then array of strings – Maxim Jul 26 '12 at 17:33
  • Thanks Maxim, you have been very helpful. However, my actual Order class has 20+ attributes, which makes it very laborious to map each attribute to get an Order object. – itmartins Jul 26 '12 at 18:43
  • @itmartins Look at "2.12 Foreign Object Fields" from reference I pointed if it gives you any tips, IMO it's probably gonna work for you. In Order you'll have a collention of clients or viceversa. Playing with QueryBuilder after you might be able to get what you need – Maxim Jul 26 '12 at 18:47
  • 2
    FYI: as of 4.22 (just released), ORMLite now supports simple JOIN queries. See my answer. – Gray Sep 27 '12 at 20:37
  • Awesome, thanks @Gray, really great job. ORMLite makes android dev's life way easier. – Maxim Sep 27 '12 at 20:42