How to do a database query on substrings?

Question

With the package DatabaseLink I can open a connection to my MYSQL database. Then I can use SQL commands like

SQLExecute[conn, "SELECT * FROM table WHERE column ='some string'"]

SQLExecute[conn, "SELECT * FROM table WHERE column LIKE '%abcd%'"]

With the first command, we look for rows where the column value equals the given string, with the second one for rows where the column string contains the substring 'abcd'.

In Mathematica 12 we have the experimental functions DatabaseReference, RelationalDatabase and EntityStore. The documentation describes how we can use these functions to set up a connection with a database. That works fine. I can inspect all rows of the tables in my database as Entities, of which the types are the table names. So many of these Entities have strings as properties.

Now I want to do queries on the database. For example, return those Entities of type 'table' for which property 'column' equals 'some string'. That is the first SQL-command above. I found it can be done in the following way ( I am not sure if that is the best way for doing a query):

EntityList @ EntityClass["table", "column"->"some string"]

For the second SQL-example, I tried a lot, e.g.

EntityList @ EntityClass["table", "column"->(StringContainsQ[ #, "abcd"]&)]

This results in an error message that the StringContainsQ expression cannot be compiled to SQL.

Any hint how I can do a query on substrings of string values is highly welcome.

Answer 1

I am one of the developers for the new relational database connectivity features.

Very soon, a rather extensive tutorial will be added to the current documentation (via documentation paclet update), which would contain, in particular, examples of the kind you are interested in.

What you need is:

EntityList @ FilteredEntityClass[
  "table", 
  EntityFunction[row, StringContainsQ[row["column"], "abcd"]]
]

Think of EntityFunction as an analogue of Function with named argument, where the argument is bound to a row of a table (in this context).

B.t.w., EntityList may not always be what you want. If you want to extract some fields (columns) from the resulting (virtual) table, you can use EntityValue:

EntityValue[
  FilteredEntityClass[
    "table", 
    EntityFunction[row, StringContainsQ[row["column"], "abcd"]]
  ],
  a-list-of-column-names-you-want-to-extract
]

]