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?

Answer 1

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)%")