How to build queries from a set of "optional parameters" like chained filters, ordering, limits etc?

[SwiftedMind]

Hey, I'm currently in the process of migrating my GRDB setup to sqlite-data, which is looking amazing! It's been going super smoothly so far (though the compiler is really struggling; I barely get any autocompletion) and I'm looking forward to replacing my own iCloud sync code with the one in sqlite-data, which is likely much more robust than what I have built 😅.

One thing I'm having trouble with, though, is finding a proper way to build queries from "optional parameters" like chained filters, ordering, limits, etc.

In my old setup with GRDB, I’ve been building a versatile filter request that I can configure with a nice MemoryFilter structure. A simplified version looks like this:

public struct MemoryFilter: Sendable, Hashable {
	public var query: String?

	public var createdAfter: Date?
	public var createdBefore: Date?

	public var tagIds: [UUID]?
	public var memoryIds: [UUID]? // Filter by specific ids
	public var isRead: Bool?
	public var isFlagged: Bool?

	public var limit: Int?
	public var offset: Int?
}

Using this filter object, I can create a database request with GRDB:

private func filteredRequest(filter: MemoryFilter) throws -> QueryInterfaceRequest<MemoryRecord> {
	var memoryFilters: [SQLExpression] = []

	if let query = filter.query {
		let nameMatches = MemoryRecord.Columns.name.like("%\(query)%")
		let contentMatches = MemoryRecord.Columns.content.like("%\(query)%")
		memoryFilters.append([nameMatches, contentMatches].joined(operator: .or))
	}

	if let tagIds = filter.tagIds, tagIds.isEmpty == false {
		memoryFilters.append(tagIds.contains(MemoryRecord.Columns.tagId))
	}

	if let memoryIds = filter.memoryIds, !memoryIds.isEmpty {
		let memoryIdMatches = memoryIds.map { MemoryRecord.Columns.id == $0 }
		memoryFilters.append(memoryIdMatches.joined(operator: .or))
	}

	if let isRead = filter.isRead {
		if isRead {
			memoryFilters.append(MemoryRecord.Columns.isRead == true)
		} else {
			memoryFilters.append(MemoryRecord.Columns.isRead == false)
		}
	}

	if let isFlagged = filter.isFlagged {
		memoryFilters.append(MemoryRecord.Columns.isFlagged == isFlagged)
	}

	if let createdAfter = filter.createdAfter {
		memoryFilters.append(MemoryRecord.Columns.createdAt >= createdAfter)
	}

	if let createdBefore = filter.createdBefore {
		memoryFilters.append(MemoryRecord.Columns.createdAt <= createdBefore)
	}

	// Create Memory Request
	var memoryRequest = MemoryRecord.all()

	// Add limit if specified
	if let limit = filter.limit {
		memoryRequest = memoryRequest.limit(limit, offset: filter.offset)
	}

	return memoryRequest.filter(memoryFilters.joined(operator: .and))
}

I'm unsure how to build a query like this with StructuredQueries. Most of the filters can be composed like this:

// in extension of Memory
static func `where`(filter: MemoryFilter) -> SelectOf<Memory> {
	Self
		.where { memory in
			if let query = filter.query {
				memory.name.like(query) || memory.content.like(query)
			}
		}
		.where { memory in
			if let tagIds = filter.tagIds {
				memory.tagId.in(tagIds)
			}
		}
		.where { memory in
			if let memoryIds = filter.memoryIds {
				memory.id.in(memoryIds)
			}
		}
		.where { memory in
			if let isRead = filter.isRead {
				memory.isRead.eq(isRead)
			}
		}
		.where { memory in
			if let isFlagged = filter.isFlagged {
				memory.isFlagged.eq(isFlagged)
			}
		}
		.where { memory in
			if let createdAfter = filter.createdAfter {
				memory.createdAt.gt(createdAfter)
			}
		}
		.where { memory in
			if let createdBefore = filter.createdBefore {
				memory.createdAt.lt(createdBefore)
			}
		}
}

But this is where it's getting tricky. I want to conditionally add a limit to the query, but the limit() function does not accept an optional value. So I've tried the following, returning a SelectOf<Memory>:

// in extension of Memory
static func `where`(filter: MemoryFilter) -> SelectOf<Memory> {
	let baseQuery = Self
		.where { /* ... */ }
		.where { /* ... */ }
		/* ... */
	
	if let limit = filter.limit {
		return baseQuery.limit(limit, offset: filter.offset)
	}
	
	return baseQuery.asSelect()
}

Is this the right way to do it?

Additionally, I am trying to figure out how you would "chain" this where(filter:) with other query "options". For example:

extension Memory {
  static let ordered = Self.order { $0.name.asc() }
  static func `where`(filter: MemoryFilter) -> any SelectStatement { /* ... */ }
}

try Memory.ordered.where(filter: .init()) // Doesn't work
try Memory.where(filter: .init()).ordered // Doesn't work

With the extension on Memory itself, I can only ever use one of the properties/functions when building the query. What do I need to extend to make this work generally on a memory query? I've played around with some things and the following does compile:

public extension SelectOf<Memory> {
	var ordered: SelectOf<Memory> {
		order { $0.name.asc() }
	}
	
	func `where`(filter: MemoryFilter) -> SelectOf<Memory> {
		let baseQuery = self
			.where { /* ... */ }
			.where { /* ... */ }
			/* ... */
		
		if let limit = filter.limit {
			return baseQuery.limit(limit, offset: filter.offset)
		}
		
		return baseQuery.asSelect()
	}
}

public extension Memory {
	static let ordered = Self.all.asSelect().ordered
	static func `where`(filter: MemoryFilter) -> SelectOf<Memory> {
		Self.all.asSelect().where(filter: filter)
	}
}

try Memory.ordered.where(filter: .init()) // Compiles
try Memory.where(filter: .init()).ordered // Compiles

Is this the right way to do it?

Thanks! 😃

[mbrandonw]

Hi @SwiftedMind, for the most part you are on the right track. Here are a few comments on your explorations so far:

Most of the filters can be composed like this:

One small improvement you can make to this is the following:

Self
  .where { memory in
    if let query = filter.query {
      memory.name.like(query) || memory.content.like(query)
    }
    if let tagIds = filter.tagIds {
      memory.tagId.in(tagIds)
    }
    if let memoryIds = filter.memoryIds {
      memory.id.in(memoryIds)
    }
    if let isRead = filter.isRead {
      memory.isRead.eq(isRead)
    }
    if let isFlagged = filter.isFlagged {
      memory.isFlagged.eq(isFlagged)
    }
    if let createdAfter = filter.createdAfter {
      memory.createdAt.gt(createdAfter)
    }
    if let createdBefore = filter.createdBefore {
      memory.createdAt.lt(createdBefore)
    }
  }

But this is where it's getting tricky. I want to conditionally add a limit to the query, but the limit() function does not accept an optional value. So I've tried the following, returning a SelectOf:

Yeah overall this is correct, though of course it isn't the nicest thing. It is a bummer we don't have a conditional way of applying limit, and so that's something we will look into.

The only small change I would make is to use Memory.all to immediately get into a Select as opposed to Where, and that allows you to drop the strange asSelect:

 static func `where`(filter: MemoryFilter) -> SelectOf<Memory> {
   let baseQuery = Self
+    .all
     .where { /* ... */ }
     .where { /* ... */ }
     /* ... */
   
   if let limit = filter.limit {
     return baseQuery.limit(limit, offset: filter.offset)
   }
   
-  return baseQuery.asSelect()
+  return baseQuery
 }

Additionally, I am trying to figure out how you would "chain" this where(filter:) with other query "options". For example:

In this code snippet you erased the return type to any SelectStatement. Is there a reason you did that? If you preserve the SelectOf<Memory>, then this should work:

try Memory.where(filter: .init()).ordered

…because we leverage static dynamic member lookup so that ordered is accessible on the resulting Where.

However, this:

try Memory.ordered.where(filter: .init())

…won't work until Swift gets dynamic member look up methods, which hopefully is coming soon.

There is another way to get reusable queries by defining helpers directly on the table columns themselves. We have docs for that here.

[SwiftedMind]

Thanks for the reply!

One small improvement you can make to this is the following:
(...)
The only small change I would make is to use Memory.all to immediately get into a Select as opposed to Where, and that allows you to drop the strange asSelect:

Oh, yep, that's a good idea 😅

In this code snippet you erased the return type to any SelectStatement. Is there a reason you did that? If you preserve the SelectOf, then this should work:

Sorry, the any SelectStatement was from earlier experimentation. I've tried a lot of things while writing the post and overlooked this remnant 😅. I didn't notice the dynamic member lookup support, it's working when I use the SelectOf<Memory> 👍.

Is it fine, though, to put these in a SelectOf<Memory> extension instead, until Swift gets better dynamic member lookup support? Because then they can be applied in any order, it looks like.

---

One somewhat adjacent thing: In the hopes of a more stable autocompletion, I've installed Cursor and tried the autocompletion there (which is actually a lot faster so far), and I think I have found a bug in the Sourcekit-LSP 😅 The LSP server keeps crashing when I use SelectOf<Memory> with the code above. The only "fix" I found was creating a typealias to help the compiler:

public typealias MemorySelect = Select<Void, Memory, Void>

This seems to prevent the crashes. Maybe this is happening in Xcode as well, causing the autocompletion to be problematic sometimes 🤔? I think I will create a bug ticket in the SourceKit-LSP repo with an example project. Maybe it'll help

But I appreciate the help :) I'm loving SQLiteData so far

[SwiftedMind]

Hm, the combined .where closure doesn't compile:

Self
  .all
  .where { memory in // Error: Type '()' cannot conform to 'QueryExpression'
    if let query = filter.query {
      memory.name.like(query) || memory.content.like(query)
    }
    if let tagIds = filter.tagIds {
      memory.tagId.in(tagIds)
    }
    if let memoryIds = filter.memoryIds {
      memory.id.in(memoryIds)
    }
    if let isRead = filter.isRead {
      memory.isRead.eq(isRead)
    }
    if let isFlagged = filter.isFlagged {
      memory.isFlagged.eq(isFlagged)
    }
    if let createdAfter = filter.createdAfter {
      memory.createdAt.gt(createdAfter)
    }
    if let createdBefore = filter.createdBefore {
      memory.createdAt.lt(createdBefore)
    }
  }

It's only working for a single if let. Once I add more than that, I get the error: Error: Type '()' cannot conform to 'QueryExpression' 🤔