3
\$\begingroup\$

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
    }
  }
\$\endgroup\$
2
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.