DynamoDB Query / Scan with combined conditions

Question

I am facing an architectural problem (or a logical one) and am not sure what is the best way to proceed. I am building a small matchmaking app to study AWS Mobile HUB and it's services and get a bit more familiar with it.

My DynamoDB contains a table with rendezvous that have the following 2 properties (and some more which are not important here):

Rendezvous

- from_gender
- interest_gender

Additionally i am using cognito and have my users have a property

User

- usr_gender
- interest_gender

Basically I want to match up users that are interested in from_gender and possess a gender of interest_gender

At the moment, User can be either male or female whereas interest_gender can be either male, female or all.

This means for a user we have 6 combinations:

Gender -> Interested In

Male -> female 
Female -> female 
Female -> all 
Male -> all 
Male -> male
Female -> male

Now it gets tricky:

Example

User A (female interested in all) is looking for rendezvous

She should be shown the 4 following rendezvous types:

from_gender, interest_gender

male, female
male, all
female, female
female, all

which means that I would need a query of sorts (dummycode):

rendevous.query("

(from_gender = male) AND ((interest_gender = female") OR (interest_gender = all)) OR

(from_gender = female) AND ((interest_gender = female") OR (interest_gender = all))

")

However

I have not yet found a way to get this working with query or scan as they do not support OR queries on the same property.

I tried to create indeces (using codes for the different combinations, but i did not find a way to create a queryable index for all cases), note that additionally to this interest I would also need to sort by age (between min / max) and location min/max lat, min/max long.

Are there any smart approaches using DynamoDB or am I forced to go the SQL route?

This is the query expression I have tried latest: Works

func generateScanExpression() -> AWSDynamoDBScanExpression {
    let usr = UserHelper.sharedHelper.currentUser
    let expression = AWSDynamoDBScanExpression()
    expression.limit = 10

    // Male interested in male
    if (usr?.interest == Constants.CONST_MALE) && (usr?.gender == Constants.CONST_MALE){
        expression.filterExpression = "details_fromGender = :fg AND ( match_interest = :ig OR match_interest = :all )"
        expression.expressionAttributeValues = [":fg": Constants.CONST_MALE, ":ig": Constants.CONST_MALE, ":all": Constants.CONST_ALL ]
    }
    // Female interested in female
    if (usr?.interest == Constants.CONST_FEMALE) && (usr?.gender == Constants.CONST_FEMALE){
        expression.filterExpression = "details_fromGender = :fg AND ( match_interest = :ig OR match_interest = :all )"
        expression.expressionAttributeValues = [":fg": Constants.CONST_FEMALE, ":ig": Constants.CONST_FEMALE, ":all": Constants.CONST_ALL ]
    }
    // Male interested in female
    if (usr?.interest == Constants.CONST_FEMALE) && (usr?.gender == Constants.CONST_MALE){
        expression.filterExpression = "details_fromGender = :fg AND ( match_interest = :ig OR match_interest = :all )"
        expression.expressionAttributeValues = [":fg": Constants.CONST_FEMALE, ":ig": Constants.CONST_MALE, ":all": Constants.CONST_ALL ]
    }
    // Female interested in male
    if (usr?.interest == Constants.CONST_MALE) && (usr?.gender == Constants.CONST_FEMALE){
        expression.filterExpression = "details_fromGender = :fg AND ( match_interest = :ig OR match_interest = :all )"
        expression.expressionAttributeValues = [":fg": Constants.CONST_MALE, ":ig": Constants.CONST_FEMALE, ":all": Constants.CONST_ALL ]
    }
    // Male interested in all
    if (usr?.interest == Constants.CONST_ALL) && (usr?.gender == Constants.CONST_MALE){
        expression.filterExpression = "( details_fromGender = :fg OR details_fromGender = :all ) AND match_interest = :ig"
        expression.expressionAttributeValues = [":fg": Constants.CONST_MALE, ":ig": Constants.CONST_MALE ]
    }
    // Female interested in all
    if (usr?.interest == Constants.CONST_ALL) && (usr?.gender == Constants.CONST_FEMALE){
        expression.filterExpression = "( details_fromGender = :fg OR details_fromGender = :all ) AND match_interest = :ig"
        expression.expressionAttributeValues = [":fg": Constants.CONST_MALE, ":ig": Constants.CONST_FEMALE ]
    }

    return expression
}

Answer 1

Are you absolutely certain you can't use an OR condition on a DynamoDB scan operation? I'm not saying you're wrong, I just seem to remember doing it. Here is some code I would try if I were working on the same issue:

func findMatches(interestGender: String, userGender: String, allCondition: String, map: AWSDynamoDBObjectMapper) -> AWSTask<AnyObject> {
    let scan = AWSDynamoDBScanExpression()
    scan.filterExpression = "interest_gender = :interestGender AND usr_gender = :userGender OR interest_gender = :allCondition"
    scan.expressionAttributeValues = [":interest_gender":interestGender, ":usr_gender":userGender, ":interest_gender":allCondition]

    return map.scan(Rendezvous.self, expression: scan).continue({ (task: AWSTask) -> AnyObject? in
        if (task.error != nil) {
            print(task.error)
        }

        if (task.exception != nil){
            print(task.exception)
        }

        if let result = task.result {
            return result.items as! [Rendezvous] as AnyObject?
        }

        return nil
    })

}

This would assume that you have your Rendezvous object that conforms to AWSDynamoDBObjectModel and AWSDynamoDBModeling. Try it, and let me know if it works for you, otherwise I'll delete my answer. I don't have the ability to test it for you until much later tonight, so I'm not 100% certain it will work.