0

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.

BLE
  • 161
  • 7
  • I don’t know what framework you are using but can’t you send the query as raw sql instead? – Joakim Danielson Feb 01 '20 at 08:21
  • sqlite.swift https://github.com/stephencelis/SQLite.swift. I imagine I could, but seems the least elegant solution given the chain-able nature of the methods. Thank you though. Edited to add tag for sqlite.swift. – BLE Feb 02 '20 at 22:08

1 Answers1

1

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.

HalR
  • 11,411
  • 5
  • 48
  • 80