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...