Sqlite.swift create dynamic complex queries

Question

I have 1 table with multiple columns. On the app, we are looking forward to add 4 dynamic filters like (cat, size, color,shape).

We know we can create a filter to sqllite like so:

user = user.select(name) 
        .filter((color == "Blue") && (size = "Big") && (cat="a") && (shape="round")) 
        .order(name.asc, name) // ORDER BY "email" DESC, "name"
        .limit(5, offset: 0)

But what happens if a filter, let's say that for color we want to search for all colors. Then,

.filter((color == "?????") && (size = "Big") && (cat="a") && (shape="round"))

Any ideas on how to create dynamic filters for this case?

Martin R · Accepted Answer · 2015-07-23 19:21:39Z

10

The filter() method takes an Expression<Bool> argument, and compound expressions can be created dynamically with the logical operators &&, ||, etc.

Simple example:

// Start with "true" expression (matches all records):
var myFilter = Expression<Bool>(value: true)

// Dynamically add boolean expressions:
if shouldFilterColor {
    myFilter = myFilter && (color == "Blue")
}
if shouldFilterSize {
    myFilter = myFilter && (size == "Big")
}
// ... etc ...

// Use compound filter:
query = user.select(name) 
        .filter(myFilter) 
        .order(name.asc, name)
        .limit(5, offset: 0)

answered Jul 23, 2015 at 19:21

Martin R

541k98 gold badges1.3k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

danielsalare Over a year ago

For some reason the filter is not working on my side, it's not marking errors or warnings and seems to be a good way to go. I've tried this (removed ifs) but no luck. var myFilter = Expression<Bool>(value: true) myFilter = myFilter && (name == "Facebook")

Martin R Over a year ago

@edse: It worked in my simple test, that's all I can say. Note that you can inspect the generated SQL code with println(myFilter.SQL) and println(myFilter.bindings), perhaps that helps to isolate the problem.

danielsalare Over a year ago

not doubting it works, it's clear that is the solution but I can't seem to make it work properly. This is the code I have, but don't know why when filtering it wont work. :S gist.githubusercontent.com/edse83/e03df13150eaf8e0c193/raw/…

stephencelis Over a year ago

Because the filter is being built up dynamically, you can avoid the true expression by setting up a base query:

let base = users.select(name).order(name.asc).limit(5); let filtered: Query; if shouldFilterColor { filtered = base.filter(color == "Blue") }…

.

Collectives™ on Stack Overflow

Sqlite.swift create dynamic complex queries

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related