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

Jira Development Jira Server
1

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?

2

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
3

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 )

4

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

5

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 = ?

3 Likes
6

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

7

Thanks @clouless for sharing this!

8

Experimentally we managed to figure out the difference between databases.

  • PostgreSQL - requires column name to be in quotes inside LOWER function e.g. LOWER(“NAME”)
  • Oracle - seems to be happy either way, with and without quotes
  • MySQL - doesn’t work with quotes.
  • MSSQL - yet to be tested but imagine will be same
    So we ended up implementing the logic as follows:

String lowerFunction = ao.moduleMetaData().getDatabaseType() == DatabaseType.MYSQL? “LOWER(NAME)” : “LOWER("NAME")”;
Query.select().where(lowerFunction + " LIKE ?", name)

In case when alias are used the column name only needs to go in quotes e.g.
String lowerFunction = ao.moduleMetaData().getDatabaseType() == DatabaseType.MYSQL? “LOWER(alias.NAME)” : “LOWER(alias."NAME")”;