Search code examples
sqlfiltermariadbfieldmetabase

SQL Keyword Search using multiple optional field filters (Metabase)

I'm new to SQL(Self Taught) and to StackOverflow. So I apologize if there's a bit a newbspeak.

Currently I'm attempting to write a query within Metabase that allows a user to search multiple fields within a certain date period without being 100% exact to what they're searching for, sort of like a keyword search.

Search requests:
Date Time...(Required)
User...(optional)
String A...(optional)
String B...(optional)
String C...(optional)

SELECT 
a.DateTime,
a.User,
b.StringA,
b.StringB,
b.StringC

FROM TableA a

JOIN tableB b
   ON TableAid = TableBid

WHERE a.datetime between {{start}} and {{end}}
[[AND a.User = {{user}}]]
[[AND b.StringA = {{StringA}}]]
[[AND b.StringB = {{StringB}}]]
[[AND b.StringC = {{StringC}}]];

The syntax above appears to work correctly but only if they have the complete words inputted into their respective fields. I'm looking to use the Like operator to retrieve all rows in columns A B and C that have a string similar to the one given.

I tried using

[[AND b.StringA = Like ('%'+{{StringA}}+'%')]]

and

[[AND b.StringA = Like CONCAT('%'+{{StringA}}+'%')]]

to no avail and attempted a few of the @StringA solutions in some of the threads here but didn't have any luck.

Is anyone able to give me a hand here? Any help would be greatly appreciated.

Solution

  • So I actually wound up figuring this out after a little trial and error and wanted to come back and update the thread in case someone else had the same issue.

    Rather than

    [[AND b.StringA = Like CONCAT('%'+{{StringA}}+'%')]]

    it needs to look like

    [[and b.StringA like (concat('%',{{StringA}},'%'))]]

    A minor syntactical error but man this had me stumped for a bit.

    Anyways I appreciate all the help people attempted to provide and hope this helps someone in the future.