Vapor 4 case insensitive query

Question

In Vapor 3 you could use filter method with a SQLiteBinaryOperator, so you could create a query with a like operator. I'm trying to do the exact same thing in Vapor 4 but couldn't find anything for that. Here's my code

Vapor 4

func queryUserMovies(_ req: Request) throws -> Future<[Users]> {
    let title = req.parameters.get("title")!
    return Movies.query(on: req.db).filter(\.$title == title).first().unwrap(or:Abort(.notFound, reason: "There's no movie")).flatMap{ movie in
        return movie.$users.query(on: req.db).all()
    }
}

Vapor 3

func queryUserMovies(_ req: Request) throws -> Future<[Users]> {
    guard let movie = req.query[String.self, at: "movie"] else {
        throw Abort(.badRequest, reason: "Not such movie")
    }
    return Movies.query(on: req).filter(\.title, .like, movie).first().unwrap(or:Abort(.notFound, reason: "There's no movie")).flatMap{ movie in
        return movie.users.query(on: req).all()
    }
}

Is there anything similar in Vapor 4 or do I need to perform a raw query in SQL?

score 12 · Accepted Answer · answered Aug 26 '20 at 16:25

12

The equivalent in Vapor 4 is:

func queryUserMovies(_ req: Request) throws -> Future<[Users]> {
    let title = try req.query.get(String.self, at: "title")
    return Movies.query(on: req.db)
        .filter(\.$title, .custom("ilike"), title)
        .first()
        .unwrap(or:Abort(.notFound, reason: "There's no movie"))
        .flatMap{ movie in
            return movie.$users.query(on: req.db).all()
    }
}

You can even perform a wider search to find anything containing that title:

.filter(\.$title, .custom("ilike"), "%\(title)%")

answered Aug 26 '20 at 16:25

0xTim

5,146
11
23

1

Is this save to use with a user entered title, e.g. preventing SQL injection? – swift-lynx Sep 10 '20 at 11:42
2

Yes, [see this answer](https://stackoverflow.com/a/63688526/1823705) as to why – 0xTim Sep 10 '20 at 14:56
1

Use `.custom("like")` on SQLite. – Klaas Oct 18 '22 at 11:48

score 2 · Answer 2 · edited Aug 31 '20 at 15:25

I just had the same issue, but with one more obstacle!

The setup is a Machine relation joined with a Category relation and I wanted to search for occurrences of one or more search-terms in the Machine.name and Category.name with one expression.

(Both the Machine and the Category relations have a name attribute.)

search is of the Type [String.SubSequence], so we can iterate over multiple search-term keywords, which must all be present somewhere in the names.

My solution was:

return Machine
  .query(on: req.db)
  .join(Category.self, on: \Category.$id == \Machine.$category.$id)
  // For each search-term the must be at least one fit with Machine.name and/or Category.name
  .group(.and) {
    var result = $0
    for term in search.map({ "%\(String($0))%" }) {
      // One or both must fit the search-term ...
      result = result.group(.or) {
        $0
          // Does the Machine name fit?
          .filter(\Machine.$name, .custom("ilike"), term)
          // Does the Category.path name fit?
          .filter(
            DatabaseQuery.Field.path(
              Category.path(for: \Category.$name),
              schema: Category.schema
            ),
            DatabaseQuery.Filter.Method.custom("ilike"),
            DatabaseQuery.Value.bind(term)
          )
      }
    }
  }

As you can see there are two .group(...) functions. The outer group (.or) says "for each search-term there must be one fitting inner group rule" and the inner group (.or) says "there must be at lease a fitting Machine.name or Category.name".

Since .custom("ilike") is not supported by the "joined-relation-filter" I used the workaround I found here.

Even tough the question may already be answered, I spend some time figuring this out and thought I could share it here.

score 1 · Answer 3 · answered Aug 25 '20 at 21:24

1

There is the contains operator, ~~, that can be used for this:

Movie.query(on: req).filter(\.$title ~~ movie)

It is case-insensitive.

answered Aug 25 '20 at 21:24

Nick

4,820
18
31
47

1

I'm using `~~` but it's *not* case-insensitive for me. – LinusGeffarth Aug 28 '20 at 08:44
1

@LinusGeffarth, I'm using MySQL, could it be the underlying database? I haven't been able to find the implementation in Fluent yet. Just checked my code and it doesn't do any folding, etc. but is working. – Nick Aug 28 '20 at 19:20
1

Perhaps yeah, I'm using PostgreSQL. I've found the Vapor 3.0.0 version of 0xTim's answer to work for me, while this operator didn't, unfortunately. – LinusGeffarth Aug 28 '20 at 19:22

score 0 · Answer 4 · answered Nov 12 '20 at 16:39

0

If you want to do case-insensitive ordering, you can do this:

Movie.query(on: req).sort(.custom("lower(title)"))

answered Nov 12 '20 at 16:39

Greg Ennis

14,917
2
69
74

Vapor 4 case insensitive query

4 Answers4