# MySQL natural search and like search

I have written two queries for searching, but I want to simplify them as it seems needed.

def searching(term: String): List[String] = {
DB.withConnection { implicit c =>
var list = List[String]()
val pinfo = SQL("SELECT body FROM tableName WHERE body like {term} limit 0,14 ").on("term" -> ("%" + term + "%"))()
pinfo.map {
row =>
list ::= row[String]("body")
}
val res = SQL("SELECT body FROM tableName WHERE MATCH (body) AGAINST ({term} IN BOOLEAN MODE) limit 0,14 ").on("term" -> term)()
res.map {
row =>
list ::= row[String]("body")
}
return list.distinct
}
}


Since the two queries don't depend on each other, and they both return the same column, it seems you can UNION them.

I cannot test this, but it seems this should work:

  def searching(term: String): List[String] = {
DB.withConnection { implicit c =>
val result = SQL(
"SELECT body FROM tableName WHERE body like {liketerm} limit 0,14 " +
"UNION " +
"SELECT body FROM tableName WHERE " +
"MATCH (body) AGAINST ({term} IN BOOLEAN MODE) limit 0,14 ").on(
"liketerm" -> ("%" + term + "%"),
"term" -> term
)()
result.map(row => row[String]("body")).distinct
}
}


Not only this is simpler, running one query instead of two is more efficient.

Before simplifying your program, you should consider its meaningfulness: you use two queries with limit but without any order by clause: since in SQL the order of the result is not specified unless an order by clause is present, every time you call the function you could get a different (random) set of tuples.