Defining Relations

Relations are defined in a relationMappings dictionary on your Model class. Each key in the dictionary is a name for the relation, and the value is a RelationMapping object.

A RelationMapping requires three properties:

• relation: The type of relation (e.g., RelationType.BelongsToOneRelation).
• modelClass: The Model class that is being related to. This can be the class itself or a string with the class name.
• join: A dictionary describing how the tables are connected.

Relation Types

Sustained supports the following relation types via the RelationType enum:

• BelongsToOneRelation: A one-to-one or many-to-one relationship.
• HasManyRelation: A one-to-many relationship.
• HasOneRelation: A special case of a one-to-one relationship.
• ManyToManyRelation: A many-to-many relationship that requires a through table.

Example: BelongsToOneRelation

This is used for both one-to-one and many-to-one relations. For example, an Animal has one owner (Person), and a Person can own many Animals.

from sustained import Model, RelationType

class Person(Model):
    tableName = 'persons'

class Animal(Model):
    tableName = 'animals'
    relationMappings = {
        'owner': {
            'relation': RelationType.BelongsToOneRelation,
            'modelClass': Person, # Using the class directly
            'join': {
                'from': 'animals.ownerId',
                'to': 'persons.id'
            }
        }
    }

Example: ManyToManyRelation

This is for relationships that require an intermediate (or “through”) table. For example, a Person can act in many Movies, and a Movie has many Persons (actors). The link is stored in a persons_movies table.

class Movie(Model):
    tableName = 'movies'

class Person(Model):
    tableName = 'persons'
    relationMappings = {
        'movies': {
            'relation': RelationType.ManyToManyRelation,
            'modelClass': 'Movie', # Using a string name
            'join': {
                'from': 'persons.id',
                'through': {
                    'from': {'table': 'persons_movies', 'key': 'personId'},
                    'to': {'table': 'persons_movies', 'key': 'movieId'}
                },
                'to': 'movies.id'
            }
        }
    }

Joining Relations

Once relations are defined, you can use a family of ...JoinRelated methods to add JOIN clauses to your query. The method name determines the type of JOIN performed.

• joinRelated() (defaults to INNER JOIN)
• innerJoinRelated()
• leftJoinRelated()
• leftOuterJoinRelated()
• rightJoinRelated()
• rightOuterJoinRelated()
• fullOuterJoinRelated()
• crossJoinRelated()

# SELECT animals.name, persons.name
# FROM animals
# LEFT OUTER JOIN persons
#   ON animals.ownerId = persons.id
query = Animal.query().select('animals.name', 'persons.name').leftOuterJoinRelated('owner')

Joining with an Alias

You can also provide a custom alias for the joined table, which is useful for complex queries or self-joins.

# SELECT *
# FROM animals
# INNER JOIN persons AS p
#   ON animals.ownerId = p.id
query = Animal.query().select('animals.name', 'p.name').innerJoinRelated('owner', alias='p')

Joins with through Tables

When you join a ManyToManyRelation, Sustained automatically handles joining the intermediate table first, followed by the final table.

# SELECT *
# FROM persons
# INNER JOIN persons_movies
#   ON persons.id = persons_movies.personId
# LEFT JOIN movies
#   ON persons_movies.movieId = movies.id
query = Person.query().select('persons.name', 'movies.title').leftJoinRelated('movies')

Notice that the join from persons to persons_movies is an INNER JOIN, while the join from persons_movies to movies is the LEFT JOIN you requested. This is the standard, expected behavior for joining through tables.

Raw Joins

For simple joins where you don’t have or need a pre-defined relation on your model, you can use the raw join methods. These methods are generated dynamically for each join type (join, innerJoin, leftJoin, rightJoin, etc.).

They accept arguments in two main ways: by specifying ON conditions, or by using the USING clause.

Specifying ON Conditions

You can provide three arguments after the table name:

1. The first column for the ON condition.
2. The operator for the ON condition.
3. The second column for the ON condition.

# SELECT persons.*, animals.name
# FROM persons
# LEFT JOIN animals
#   ON persons.id = animals.ownerId
query = Person.query().leftJoin('animals', 'persons.id', '=', 'animals.ownerId')

Using the USING Clause

As an alternative to ON conditions, you can use the using keyword argument with a list of column names. This is suitable when the join columns have the same name in both tables.

# SELECT users.*, profiles.*
# FROM users
# JOIN profiles USING (profile_id)
query = User.query().join('profiles', using=['profile_id'])

Complex Joins with Lambdas

For joins that require multiple or complex ON conditions, you can pass a lambda function as the second argument to any of the join methods. This lambda receives a JoinBuilder object that you can use to construct the join conditions.

The JoinBuilder has the following methods:

• on(col1, op, col2): Adds the initial ON condition. col2 can be a column name string or a QueryBuilder instance (for subqueries).
• andOn(col1, op, col2): Adds an AND condition to the join. col2 can be a column name string or a QueryBuilder instance.
• orOn(col1, op, col2): Adds an OR condition to the join. col2 can be a column name string or a QueryBuilder instance.

# SELECT *
# FROM users
# JOIN accounts
#   ON accounts.id = users.account_id
#   OR accounts.owner_id = users.id
query = User.query().join(
    'accounts',
    lambda j: j.on('accounts.id', '=', 'users.account_id')
    .orOn('accounts.owner_id', '=', 'users.id'),
)

Joins with Subqueries in ON Clause

You can use a QueryBuilder instance as the right-hand side of an ON condition, allowing for powerful join criteria based on subquery results.

# SELECT p.*, (SELECT COUNT(*) FROM pets WHERE pets.owner_id = p.id) AS pet_count
# FROM persons AS p
# JOIN (
#   SELECT owner_id
#   FROM pets
#   GROUP BY owner_id
#   HAVING COUNT(*) > 1
# ) AS owners_with_multiple_pets
#   ON owners_with_multiple_pets.owner_id = p.id
multiple_pets_subquery = Pet.query().select('owner_id').groupBy('owner_id').having('COUNT(*)', '>', 1)

query = Person.query().alias('p').join(
    Subquery(multiple_pets_subquery, 'owners_with_multiple_pets'),
    lambda j: j.on('owners_with_multiple_pets.owner_id', '=', Column('p.id'))
).select(Column('p.*'))

Note that more complex nested join criteria are not supported.