[Vapor 3] Complex queries results in incorrect SQL

[andrewtheis]

Given the following model:

final class Event: PostgreSQLModel {
    var description: String?
    var endDate: Date?
    var id: Int?
    var name: String
    var startDate: Date
}

And the following query:

let eventQuery = try Event.query(on: request)
    .range(0...50)

let now = Date()
    
try eventQuery.group(.or) { orGroup in
    try orGroup.filter(\.startDate >= Date.distantPast)
    try orGroup.filter(\.endDate >= Date.distantPast)
}.filter(\.startDate <= Date.distantFuture).group(.or) { orGroup in
    try orGroup.filter(\.endDate >= now)
    try orGroup.filter(\.endDate == nil)
}

let searchTerm = "xyz"
try eventQuery.group(.or) { (orGroup) in
    try orGroup.filter(\.name, .custom(.sql("ILIKE")), .data("%\(searchTerm)%"))
    try orGroup.filter(\.description, .custom(.sql("ILIKE")), .data("%\(searchTerm)%"))
}

Results in this SQL:

SELECT * 
FROM "events" 
WHERE (
	("events"."start_date" >= $1 OR "events"."end_date" >= $2)
	AND "events"."start_date" <= $3 
	AND (
		("events"."start_date" >= $4 OR "events"."end_date" >= $5) 
		OR "events"."start_date" <= $6 
		OR "events"."end_date" >= $7 
		OR "events"."end_date" IS NULL
	) 
	AND (
		("events"."start_date" >= $8 OR "events"."end_date" >= $9) 
		OR "events"."start_date" <= $10 
		OR (
			("events"."start_date" >= $11 OR "events"."end_date" >= $12) 
			OR "events"."start_date" <= $13 
			OR "events"."end_date" >= $14
			OR "events"."end_date" IS NULL
		) 
		OR "events"."name" ILIKE $15 
		OR "events"."description" ILIKE $16
	)
) 
LIMIT 50 OFFSET 0 

When the expected SQL is:

SELECT * 
FROM "events" 
WHERE (
	("events"."start_date" >= $1 OR "events"."end_date" >= $2)
	AND "events"."start_date" <= $3 
	AND ("events"."end_date" >= $4 OR "events"."end_date" IS NULL) 
	AND ("events"."name" ILIKE $5 OR "events"."description" ILIKE $6)
) 
LIMIT 50 OFFSET 0 

[tanner0101]

I believe the problem is that sub.query.filters needs to get set to an empty array before passing into the closure here: https://github.com/vapor/fluent/blob/master/Sources/Fluent/Query/Filter/QueryFilter.swift#L187

A unit test should be added for this as well to make sure it doesn't happen again.

[tanner0101]

Keeping this open as a reminder to add a test case.

[tanner0101]

This is fixed in #482