Is there a better way for relational queries ?

[github2016a]

Hi :
Thanks for segiddins's answer for #1736 ,sorry to bother again. I had saw the doc several times,but still cannot find a better way.sorry for my poor english,I hope i can explain clearly.
for example,there are two objects:

// Swift
class Person: RLMObject {
    dynamic var id : String = ""
    dynamic var name : String = ""
    dynamic var age : Int = 0
    dynamic var books = RLMArray(objectClassName: Book.className())
}
class Book: RLMObject {
    dynamic var ownerId : String = ""
    dynamic var title : String = ""
    dynamic var color : String = ""
}

If i want to see the person with age>40 who has red books(show all book title)
In relational database i just need:

    select count(1) from Person,Book where Book.ownerId = Person.id AND Person.age>40 AND Book.color='red'
    select Person.name,Book.title from Person,Book where Book.ownerId = Person.id AND Person.age>40 AND Book.color='red'

And in Realm I have to:

// get list
func getList()->[[String]]{
    let persons = Person.objectsWhere("age > 40")
    var countNeeded = 0
    var listNeeded = [[String]]()
    for obj in persons {
        var person = obj as Person
        let books = person.books.objectsWhere("color = 'red'")
        for obj2 in books{
            var book = obj as Book
            listNeeded.append([person.name,book.title])
        }
    }
    countNeeded = listNeeded.count
    return listNeeded;
}
// get Count
func getCount()->Int{
    let persons = Person.objectsWhere("age > 40")
    var countNeeded = 0
    for obj in persons {
        var person = obj as Person
        let books = person.books.objectsWhere("color = 'red'")
        countNeeded += Int(books.count)
    }
    return countNeeded
}

When the table data is big,the FOR still take time.There are so many such queries(such as group by) in my project,so it bothered me a lot.
If i made any mistake,or if there is a better way,please let me know.
Best Regards.

[github2016a]

🐬

[github2016a]

🐳

[yoshyosh]

Hi @github2016a

You can use Person.objectsWhere("age > 40 AND ANY books.color = 'red'") to get all the people over age 40 that have red books

Also I notice that you are using a foreignKey approach to Realm, that is not good for NoSQL type dbs! A better way is to just store the objects onto eachother, similar to how you stored an array of books on Person.

class Book: RLMObject {
    dynamic var ownerId : String = ""
    dynamic var title : String = ""
    dynamic var color : String = ""
}

Should be

class Book: RLMObject {
    dynamic var owner: Person?
    dynamic var title : String = ""
    dynamic var color : String = ""
}

Objects are related to other objects because they are stored within each other. You shouldn't need to use any id's to reference them. Id's are good for primary keys if you need to find a very specific objects to update them for example. For the most part you should be fine without them though!

It might look a little weird to you at first since you will be duplicating data in some places, but that's fine, that is the power of NoSQL. It makes your queries much cleaner, and the ability to grab the objects you need much more straightforward

[github2016a]

thanks @yoshyosh ,that helps a lot.wish you a good day.:sunny:

[yoshyosh]

No problem! Don't hesitate to ask if you run into any other problems, I know how it can be transitioning away from SQL type thinking. Enjoy your weekend!

[mgallagher]

Hey @yoshyosh, you've been really helpful for me in the past and I have a similar question. I have no experience with NoSQL-type db's so I was hoping you could help me figure out my schema for my weight-training tracking app. Here's what I've got:

class Exercise : RLMObject // Squats, bench press, etc.
{
    dynamic var name = "none"
    dynamic var defaultReps = 0
    dynamic var defaultSets = 0
}

class Workout : RLMObject // Contains multiple exercises
{
    dynamic var name: String = ""
    dynamic var isActive = false
    dynamic var lastDateCompleted = NSDate(timeIntervalSince1970: 1)
    dynamic var exerciseList = RLMArray(objectClassName: Exercise.className())
}

class SwiftIntObject : RLMObject // Since I couldn't figure out how to make a RLMArray of just type Int
{
    dynamic var weightLifted = 0
}

class CompletedWorkout : RLMObject
{
    dynamic var workout = Workout() // Contains all the exercises
    dynamic var dateCompleted = NSDate(timeIntervalSinceNow: 0)
// Everything below is where I get confused:
    dynamic var weightLiftedForExercise = RLMArray(objectClassName: SwiftIntObject.className())
    dynamic var totalCompletedSetsForExercise = RLMArray(objectClassName: SwiftIntObject.className())
    dynamic var totalCompletedRepsForExercise = RLMArray(objectClassName: SwiftIntObject.className())
}

For the CompletedWorkout model, I'm pretty confused about how to hold the weight lifted and completed sets/reps for each exercise contained within workout. I have it set up above to just use arrays to hold that data, but I don't think it's the best way of doing it since they're not linked to an Exercise object. Any thoughts on how to clean this up?

Second question: Exercises that have the same names like "Squat" can have a different number of reps and sets from each other, e.g. some workout routines have 3 sets of 5 reps and some have 5 sets of 5 reps. Since each Exercise will need to be unique in that regard, should I add a primary key to it? I might be overthinking all of this...

[yoshyosh]

Hey @mgallagher I think the best way to do this would be to set up your model like this
Plans - name, exercises
Workout - name, exercises, completed, totalReps, totalSets, totalWeight, user (or you can use backlinks)
Exercise - name, sets
Set - reps, weight
User - name, currentWorkout, workouts

Plans would be your general templates, when a user selects a plan, you can create a workout by copying its values.

Workout has a completed boolean so that you know whether a user has completed it or not. You can also have some computed properties like totalReps, totalSets, totalWeight, where you would iterate through all the exercises and their sets, then add all those up.

Exercise has sets rather than reps and weight, this allows us to have those varying reps and weight combos for the same type of exercise, like a squat

Hope this helps!

[mgallagher]

That's fantastic and actually makes a lot sense. Thank you very much @yoshyosh!