CODEWITHSUNDEEP
swiftsqlitesqlite.swift
BLE
BLE

Reputation: 161

Is there a SQLite for Swift method for more complex ORDER BY statements?

I have a query similar to the following that I'd like to perform on an sqlite database:

SELECT name 
FROM table 
WHERE name LIKE "%John%" 
ORDER BY (CASE WHEN name = "John" THEN 1 WHEN name LIKE "John%" THEN 2 ELSE 3 END),name LIMIT 10

I'd like to use SQLite for Swift to chain the query together, but I'm stumped as to how to (or if it is even possible to) use the .order method. 

let name = "John"
let filter = "%" + name + "%"
table.select(nameCOL).filter(nameCOL.like(filter)).order(nameCOL)

Gets me

SELECT name
FROM table 
WHERE name LIKE %John% 
ORDER BY name

Any ideas on how to add to the query to get the more advanced sorting where names that start with John come first, followed by names with John in them?

I saw the sqlite portion of the solution here: SQLite LIKE & ORDER BY Match query

Now I'd just like to implement it using SQlite for Swift

Seems it may be too restrictive for that given the limited examples, anyone else have any experience with more complicated ORDER BY clauses?

Thanks very much.

Upvotes: 0

Views: 594

Answers (1)

HalR
HalR

Reputation: 11083

Sqlite.swift can handle that pretty cleanly with two .order statements:

let name = "John"
let filter = "%" + name + "%"
table.select(nameCol).filter(nameCol.like(filter))
.order(nameCol.like("\(name)%").desc
.order(nameCol)

The .order statements are applied in the order they are listed, the first being primary.

The filter will reduce the results to only those with "John" in them. SQlite.swift can do many complex things. I thought I would need a great deal of raw sql queries when I ported 100s of complex queries over to it, but I have yet to use raw sql.

Upvotes: 1

Related Questions