Vapor Fluent Query Support AND, XOR

Related Projects Vapor
1

Hi,
I am experiencing Vapor and Fluent.
Here is my problem: I have "Customer", and "Color" that has several values.
My problem can be solved with Many-to-Many relationship what Fluent did support.
However, I think it may be complex or expensive for querying later (Not sure about expensive).
Here is what I try to solve this case (BitMasking):

final class Customer: Model {
    //...
    var favoriteColorsBitMask: UInt16 // or UInt32, UInt64 if needed.
    //...
}

And

enum Color: UInt16, Codable {
    case red = 0
    case orange = 1
    case yellow = 2
    case chartreuseGreen = 3
    case green = 4
    case springGreen = 5
    case cyan = 6
    case azure = 7
    case blue = 8
    case violet = 9
    case magenta = 10
    case rose = 11
    
    var categoryBitMask: UInt16 {
        return 1 << self.rawValue
    }
}

Then

var red: Color = .red
var orange: Color = .orange
var green: Color = .green
var redOrangeGreenBitMask = red.categoryBitMask | orange.categoryBitMask | green.categoryBitMask
var peter = Customer(..., favoriteColorsBitMask: redOrangeGreenBitMask, ...)
peter.save(on: request) // Save peter to database.

peter = ... // Fetch peter from database.

// Check if peter likes red color.
if (peter.favoriteColorsBitMask & red.categoryBitMask) == red.categoryBitMask {
    // Do something.
}

So "Color" is not necessary to be a Model, No Fluent Relationship, no database record.

But, how to fetch all Customers in database who like red color, or even like "redOrangeYellowGreenBlueVioletRose" colors? (Not to write Raw SQL Query)

I am thinking could Fluent supports AND, XOR in Query Filter or new technique to solve this.
And, is it good or bad practice in this case as well? :slight_smile:

Thank you so much for your attention and help.

2

Fluent doesn't yet support XOR but you might be able to do that with a custom filter. And you can definitely do it with raw SQL as a workaround.

I imagine the SQL for "fetch all customers who like red" would look something like:

SELECT * FROM customers WHERE (favoriteColors AND <redBit>) > 0

If that's true, then this would be a bit of extra work since Fluent generally expects things to look like column <op> value. An issue for this on GitHub - vapor/fluent-kit: Swift ORM (queries, models, and relations) for NoSQL and SQL databases would be appreciated. Especially if you have ideas on what the QueryBuilder API for this could look like and have examples of the expected output (SQL).

1 Like
3

Thank you for your reply,
Since above enum is safe, it could reduce the risk for raw query.
I will try both custom filter and raw sql as work around.
I am not an advanced vapor/swift developer but I will try to open this issue if I have any idea.

4

Hi @tanner0101,
How about this:

filter(fieldNameOrKeyPath,
       NewEnumOperator.bitwiseAnd(value),
       requiredValue)

Or even nested consecutively and extremely hard to safely implement like this:

enum LogicalOp {} // Skip this for now.
enum BitwiseOp {} // Need this.
enum ArithmeticOp {} // Example below.
enum ComparisonOp {} // Example below.

filter(
    fieldNameOrKeyPath, // Note (1) below.
    ArithmeticOp.plus(value1, ArithmeticOp.multiply(value2, KEEPWALKING...)), // Note (2) below.
    ComparisonOp.equalTo, // Can be .equalTo(requiredValue)
    requiredValue
)

filter(...) {
    // Verify Logic Chain is valid. Maybe "throws" is added.
    // Handle Logic Chain cases.
}

// NOTE:
// (1) First input parameter "fieldNameOrKeyPath" could be Int, String...
// (2) (a) Second input parameter could be some kind of Operator.
//     (b) Enum is just for example, could use Struct, Generic, Protocol...

Not sure it is possible or not, good or bad. Please analyze.
Sorry if it is kind of crazy and unnecessary works. :grin:

5

Oh, it should be posted on your link.
Please check this first. If it is useful I will open an issue on your link GitHub - vapor/fluent-kit: Swift ORM (queries, models, and relations) for NoSQL and SQL databases
Thanks.

6

Updated. Check above message again.

7

I think this could still fit in the field <op> value pattern, only needing to add new functionality to field. From my example:

SELECT * FROM customers WHERE (favoriteColors AND <redBit>) > 0

The end part > 0 still matches <op> value. It's the field that has grown more complex. QueryBuilder has the following filter method:

func filter(_ field: DatabaseQuery.Field, _ op: DatabaseQuery.Method, _ value: DatabaseQuery.Value)

So I think the easiest way would be to add AND/OR/XOR support to DatabaseQuery.Field. This could look like:

query.filter(.binary(.field("favColors"), .and, .bind(redBit)), .greaterThan, .bind(0))

Then we could look into adding operator support to get something like this:

query.filter((\.$favoriteColor && redBit) > 0)
1 Like
8

Wew, so cool, Vapor did have.
Thank you for the detailed explanation.
Cannot wait to see new and/or/xor support.