OrmLite: Grouped Where Clauses with Joins

Viewed 317

I am using ORMLite within my Android Application which uses a Database to Create/Update/Delete Objects. I have the following table structure:

Table Wine

  • Country (own table / dao)
  • Winery (own table / dao)
  • Comment
  • PurchasePlace
  • some more columns, which are not relevant for this

What i am doing (only the crucial part):

In Words: I want to show all Wines, where certain fields match a user input, but respecting the wine-type, which relates to a constant.

Preparing the Join Query Builder:

queryBuilderWinery.where().like("name", "%Value%");
queryBuilderCountry.where().like("name", "%Value%");

queryBuilderWine
.join(queryBuilderWinery)
.join(queryBuilderCountry)
.where().like("name", "%Value%")
.or().like("purchasePlace", "%Value%").and().eq("wine_type_id", 1)

Producing:

FROM wine
INNER JOIN winery
   ON wine.winery_id = winery.id
INNER JOIN country
   ON wine.country_id = country.id
WHERE ((wine.name LIKE '%in%' OR wine.comment2 LIKE '%in%') AND wine.wine_type_id = 1)
OR (winery.winery_name LIKE '%in%')
OR (country.country_name LIKE '%in%')

Where clause, which would be needed:

WHERE (wine.comment like '%Value%' OR 
        wine.purchasePlace like '%Value%' OR 
        winery.name like '%Value%' OR 
        country.name like '%Value%') 
    AND wine.wine_type_id = 1

or 

WHERE  wine.wine_type_id = 1
    AND (wine.comment like '%Value%' OR 
        wine.purchasePlace like '%Value%' OR 
        winery.name like '%Value%' OR 
        country.name like '%Value%') 

My question is, can this be done? I have read the documentation on this, i know a could use RPN (Reverse Polish Notation) or create the where clause with groups, but i am not sure on how to group the where clause correctly, since all joins will have an OR Clause as well, and i need the AND Clause grouped seperatly. If i would use Where Grouping, would this have effect on the Join-Conditions as well?

Any help appreciated.

Update: I tried a raw Where Clause, but same Problem here, i cannot imagine a way, where i can group the WhereClause over more than one Table...

WineDAO wineDAO = new DatabaseHelperProvider().get(this).getWineDAO();
CountryDAO countryDAO = new DatabaseHelperProvider().get(this).getCountryDAO();
WineryDAO wineryDAO = new DatabaseHelperProvider().get(this).getWineryDAO();

QueryBuilder<Wine, Long> wineQueryBuilder = wineDAO.queryBuilder();
wineQueryBuilder = wineQueryBuilder.join(countryDAO.queryBuilder()).join(wineryDAO.queryBuilder());

Where<Wine, Long> where = wineQueryBuilder.where().raw("(winery.name = ? OR country.name = ?) AND wine_type_id = ?",
        new SelectArg("winery.name", "in"),
        new SelectArg("country.name", "in"),
        new SelectArg("wine_type_id", 1));

String prepStatement = wineQueryBuilder.prepareStatementString();
List<Wine> query = where.query();
int i = query.size();

This will not work, because the column winery.name obviously is not contained within the table wine...

0 Answers
Related