How to use "IN" and "LIKE" in Active Objects?

Hi all!

I have a query like:

Query query = Query.select().
                .where("a IN (?,?) AND " + "b (?)", first, second, third);

It works well, but I need to use “LIKE” to make my finder more flexible. I want to retrieve smth like this:

Query query = Query.select().
                .where("a IN LIKE(?,?) AND " + "b (?)", first, second, third);

At the same time, I found that SQL does not use "IN’ and “LIKE” in one SELECT.

How I can solve it?

Hi,
I would just build some for-loop in java and produce something like this

"(a LIKE ? OR b LIKE ?) AND b (?)"

I have written my best-practices together here:

Maybe it is of some help :slight_smile:

3 Likes

Thanks for your help, @clouless!

Great and easy solution! I just added case insensitivity, and retrieve the next:

String first = “%” + first + “%”;
String second= “%” + second + “%”;
Query query = Query.select().
                .where(
                           "(LOWER(a) LIKE LOWER(?) OR LOWER(a) LIKE LOWER(?)) +
                           " AND b = (?)", first, second, third);

I hope this solution will help not only me.
@clouless, thanks again )

Great article @clouless, thanks for sharing! :slight_smile:

I struggled for quite some time and there was not much documentation :smiley:

@OleksiiSkachkov You should be careful with LOWER() I cannot exactly remember which database engine had problems with that but I test my app against MySQL, PostgreSQL, Oracle and MSSQL and one of them had problems with LOWER(). That is why I created these lowercase shadow columns as a workaround. But maybe active objects can now handle it :slight_smile:

And also be careful. AND is stronger than OR. So you really need some extra brackets. ( some LIKE ? OR other LIKE ? ) AND foo = ?

1 Like

@clouless, thanks
I fixed conditions in previous post. What about “LOWER” - I will check it.

Thanks @clouless for sharing this!