Query only returning one row

[Gonzalo R]

I'm creating dynamic queries with ORMLite. Something like this:

//whereFilter is a Map that contains the filters for the Where clause in the form:
//Map<String, List<String>>  ->   (String) fieldToFilter_1 -> (List<String>) value1, value2, value3...

                Where<T, Object> w = qb.where();

                boolean first = true;
                Iterator <Entry<String, List<String>>> iter = whereFilter.entrySet().iterator();
               
                while(iter.hasNext()) {
                    //if it's not the 1st iteration, add 'AND' to the query to include the second (and on) filter
                    if(first) first = false;
                    else {
                        w = w.and();
                    }
                    Entry<String, List<String>> entry = iter.next();
                   
                    w = w.in(entry.getKey(), entry.getValue());
                }
           
            qb.setWhere(w);
           
            PreparedQuery<T> query = qb.prepare();

            List<T> elements = table.query(query);
-------------
This could produce a query like this:

SELECT *
  FROM strings
WHERE ( lang IN ( 'en' )
           AND
       stringid IN ( 1, 2, 5, 10 )  );

This will return the correct results, however ORMLite will only return the first result (stringId=1). I read something about "problems" with queries that mix ANDs and ORs (http://ormlite.com/javadoc/ormlite-core/doc-files/ormlite_3.html#index-complex-query) but the solution looks pretty static and I can't seem to find a way to include it in my code.

Has anybody come across this problem or has a solution for it?

Thanks a lot!

[Gray Watson]

On May 24, 2013, at 3:34 PM, Gonzalo R <zei...@gmail.com> wrote:

> while(iter.hasNext()) {
> //if it's not the 1st iteration, add 'AND' to the query to include the second (and on) filter
> if(first) first = false;
> else {
> w = w.and();
> }
> Entry<String, List<String>> entry = iter.next();
> w = w.in(entry.getKey(), entry.getValue());
> }

You can do this easier by doing:

for (Map.Entry<String, List<String>> entry : whereFilter.entrySet()) {
w.in(entry.getKey(), entry.getValue());
}
w.and(whereFilter.size());

This is a little strange but there is an and() and or() methods which take a number. They will pull the last number of items from the clause stack and AND them together. Just FYI.

> This could produce a query like this:
>
> SELECT * FROM strings WHERE ( lang IN ( 'en' ) AND stringid IN ( 1, 2, 5, 10 ) );

What query does it product? What shows up in the logs? See: http://ormlite.com/docs/logging

> however ORMLite will only return the first result (stringId=1).

You've done this query and it produces more than 1 result? Can you show your database?

> I read something about "problems" with queries that mix ANDs and ORs (http://ormlite.com/javadoc/ormlite-core/doc-files/ormlite_3.html#index-complex-query) but the solution looks pretty static and I can't seem to find a way to include it in my code.

I don't think that you have this problem. Specifically because you aren't mixing ANDs and ORs. :-)
gray

[Gonzalo R]

Thanks for your quick reply.

You can do this easier by doing:

       for (Map.Entry<String, List<String>> entry : whereFilter.entrySet()) {
           w.in(entry.getKey(), entry.getValue());
       }
       w.and(whereFilter.size());

Nice trick. I used that, though still no luck.

What query does it product?  What shows up in the logs?  See: http://ormlite.com/docs/logging

The query I pasted is the one created. By debugging I took it from the PreparedQuery object. Anyway I also did the 'setprop' thing to dump everything to the logcat and got the same query. These are the related log lines. The id's I used in the previous example were just for the sake of the explanation, these down here are the real id's (not relevant, though):

---
05-24 21:08:02.494: D/StatementBuilder(7544): built statement SELECT * FROM `translations` WHERE (`lang` IN ('es' ) AND `stringid` IN (178 ,180 ,181 ,182 ,183 ,184 ) )
05-24 21:08:02.504: D/BaseMappedStatement(7544): prepared statement 'SELECT * FROM `translations` WHERE (`lang` IN ('es' ) AND `stringid` IN (178 ,180 ,181 ,182 ,183 ,184 ) ) ' with 0 args
05-24 21:08:02.504: D/SelectIterator(7544): starting iterator @1156898120 for 'SELECT * FROM `translations` WHERE (`lang` IN ('es' ) AND `stringid` IN (178 ,180 ,181 ,182 ,183 ,184 ) ) '
05-24 21:08:02.514: D/SelectIterator(7544): closed iterator @1156898120 after 1 rows
05-24 21:08:02.514: D/StatementExecutor(7544): query of 'SELECT * FROM `translations` WHERE (`lang` IN ('es' ) AND `stringid` IN (178 ,180 ,181 ,182 ,183 ,184 ) ) ' returned 1 results
---

You've done this query and it produces more than 1 result?  Can you show your database?

I run this query in a sqlite db editor and it returned all rows (using the real id's from above there are 6 rows). ORMLite still returns just one.

> I read something about "problems" with queries that mix ANDs and ORs (http://ormlite.com/javadoc/ormlite-core/doc-files/ormlite_3.html#index-complex-query) but the solution looks pretty static and I can't seem to find a way to include it in my code.

I don't think that you have this problem.  Specifically because you aren't mixing ANDs and ORs.  :-)

Oh, right. I had modified the code to use "IN" instead of AND and OR and left that part in the message. Sorry for the confusion.

[Gonzalo R]

I just wrote a message but it was deleted. I don't know why.

Anyway, what I wrote is that, forget what I said in this thread. I was just using an outdated DB that didn't have the missing strings. That was the problem.

Sorry for the confusion and thanks for the tips.