Mi hijo alquiló nuestro piso sin siquiera molestarse en avisarnos. Le dimos todo y nos quedamos sin nada.
Mi marido, Gonzalo, y yo nos casamos a los veintitrés años. Yo ya estaba embarazada, pero por suerte, ambos terminamos la carrera de Magisterio. Nuestras familias no tenían dinero, no había herencias doradas, ni parientes influyentes, ni ahorros. Desde el primer día, tHome > Mobile > Extend string search in a comma-separated string
Extend string search in a comma-separated string
I currently have this case where I need to search a comma-separated string in a list for a certain string.
The working setup that I currently have is:
case class Filter(key: String, value: String)
sealed trait FilterCondition
case class OrFilter(filters: Seq[Filter]) extends FilterCondition
case class AndFilter(filters: Seq[Filter]) extends FilterCondition
def applyCondition(query: String, filterCondition: Option[FilterCondition]): String = {
val arr = ArrayBuffer[String]()
filterCondition.foreach {
case AndFilter(fs) =>
fs.foreach { f =>
arr = s”””${f.key} LIKE ‘%${f.value}%'”””
}
case OrFilter(fs) =>
val ors = fs.map { f =>
s”””${f.key} LIKE ‘%${f.value}%'”””
}
arr = ors.mkString(” OR “)
}
val filterStr = arr.mkString(” AND “)
s”””SELECT * FROM $query WHERE $filterStr;”””
}
def createFilterCondition(filters: Seq[Filter], isOr: Boolean = false): Option[FilterCondition] =
if (filters.nonEmpty) {
if (isOr) Some(OrFilter(filters)) else Some(AndFilter(filters))
} else None
And this would work fine if we were dealing with normal strings. For example:
val query = “Users”
val filters = Seq(
Filter(“Name”, “John”),
Filter(“Age”, “25”),
)
val isOr = false
This would result in:
SELECT * FROM Users WHERE Name LIKE ‘%John%’ AND Age LIKE ‘%25%’;
Now here is the problem. I now have a case where the value in the database is a string but it is comma-separated. For example, given that the database has this entry:
ID: 1
Name: Computer
Tags: electronics,it,gadget,portable
The tags there are saved as a single string. What I need now is, when I do a search with:
val filters = Seq(
Filter(“Tags”, “it”)
)
The resulting SQL query should still be able to match the string from the comma-separated value. The current SQL query that is generated is:
SELECT * FROM Products WHERE Tags LIKE ‘%it%’;
Which would still work. However, if the search string was”electronics”, it would still match in the above scenario. But it would also match if the search string was “tronic”. Or “gad” for “gadget”. That is not the intended behavior.
What I need is to match the exact word in the comma-separated string. So if the search string was”it”, the query should match entries that has “it” in the Tags column, but not “portable,unit”.
I’ve been trying to figure out how to adjust the current setup to account for this case. If there is a simpler solution, that would be great. But I would still like to know if there is a way to extend the current setup to account for this scenario.
CodePudding user response：
In plain SQL you can add commas:
WHERE ‘,’ || Tags || ‘,’ LIKE ‘%,it,%’
Alternatively, in SQLite you can use theinstr function to check for a substring after checking the preceding character is a comma (or the start of the string) and the following character is a comma (or the end of the string). I’m not sure what the best way to express that in Scala is.
CodePudding user response：
Based on @Shawn’s answer, I’ve modified my code so that for the Tags key, I will use the exact search with commas. For all other keys, it would remain as is. Here’s the updated code:
def applyCondition(query: String, filterCondition: Option[FilterCondition]): String = {
val arr = ArrayBuffer[String]()
filterCondition.foreach {
case AndFilter(fs) =>
fs.foreach { f =>
if (f.key == “Tags”) {
arr = s”””,${f.key}, LIKE ‘%,${f.value},%'”””
} else {
arr = s”””${f.key} LIKE ‘%${f.value}%'”””
}
}
case OrFilter(fs) =>
val ors = fs.map { f =>
if (f.key == “Tags”) {
s”””,${f.key}, LIKE ‘%,${f.value},%'”””
} else {
s”””${f.key} LIKE ‘%${f.value}%'”””
}
}
arr = ors.mkString(” OR “)
}
val filterStr = arr.mkString(” AND “)
s”””SELECT * FROM $query WHERE $filterStr;”””
}
This seems to work.
– RelatedNunca imaginé que la ingratitud de nuestro propio hijo nos dejaría tan vacíos, pero al menos hemos recuperado nuestro hogar.
