Skip to content

Relationships

The relationships supported by DuckORM are basically of two types:

  • One to Many and Many to One are supported by the ForeignKey field.
  • Many to Many by creating a table and making use of the ForeignKey field to relate the two tables.
  • One to One with the OneToOne field.

Let's look at some examples of using these fields.

OneToMany

  • Set the ForeignKey field to use a One-to-Many relationship.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
class City(Model):
    __tablename__ = 'cities'
    __db__ = db
    model_manager = model_manager

    id: int = Field.Integer(primary_key=True, auto_increment=True)
    name: str = Field.String(unique=True)

    @classmethod
    def relationships(cls):
        cls.persons = OneToMany(
            model=Person,
            name_in_table_fk='city',
            name_relation='person_city'
        )

class Person(Model):
    __tablename__ = 'persons'
    __db__ = db
    model_manager = model_manager

    id_teste: int = Field.Integer(primary_key=True, auto_increment=True)
    first_name: str = Field.String(unique=True)
    last_name: str = Field.String(not_null=True)
    age: int = Field.BigInteger()
    salary: int = Field.BigInteger()

    @classmethod
    def relationships(cls):
        cls.city: City = ForeignKey(
            model=City,
            name_in_table_fk='id',
            name_constraint='person_city_fk')

await model_manager.create_all_tables()

And with that, it will create the two tables and the persons table will have a field referencing the id field of the cities table.

Every field that refers to a relationship must be placed inside the method. relationships, as this method is only executed after all tables are created.

As is done in the last line, it creates all the tables and then starts doing the relationships.

Note

You may have also noticed the method relationships in the City class, but what will this do?

This method doesn't make any changes to the database, just adds the persons: OneToMany field to the class, this field has some methods which are explained here.

ManyToMany

  • To create a Many to Many relationship we also use the ForeignKey field.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
class User(Model):
    __tablename__ = 'users'
    __db__ = db
    model_manager = model_manager

    id: int = Field.Integer(primary_key=True, auto_increment=True)
    name: str = Field.String()

    @classmethod
    def relationships(cls):
        cls.working_day = ManyToMany(model=WorkingDay,
                                        model_relation=UsersWorkingDay)

class WorkingDay(Model):
    __tablename__ = 'working_days'
    __db__ = db
    model_manager = model_manager

    id: int = Field.Integer(primary_key=True, auto_increment=True)
    week_day: str = Field.String()
    working_date: str = Field.String()

    @classmethod
    def relationships(cls):
        cls.users = ManyToMany(model=User, model_relation=UsersWorkingDay)

class UsersWorkingDay(Model):
    __tablename__ = 'users_working_day'
    __db__ = db
    model_manager = model_manager

    id: int = Field.Integer(primary_key=True, auto_increment=True)

    @classmethod
    def relationships(cls):
        cls.users: User = ForeignKey(
            model=User,
            name_in_table_fk='id',
            name_constraint='user_working_day')
        cls.working_days: WorkingDay = ForeignKey(
            model=WorkingDay,
            name_in_table_fk='id',
            name_constraint='working_day_user')

await model_manager.create_all_tables()

First we create the User and WorkingDay tables, they have a Many to Many relationship. To represent this relationship we create a third UsersWorkingDay table that has a reference to the PK of the other two tables, and thus creating the relationship.

Note

The relationships method in the two tables: User and WorkingDay are a little bit different from the previous example. Have the @classmethod signaling that it is a method of the class and not the instance, so it creates the users and working_day in both models.

With that allowing some methods to be executed, like: User add a relationship with WorkingDay without using the UsersWorkingDay model. More examples can be seen here.

OneToOne

  • To represent the One to One relationship, just make use of the OneToOne field.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
class Person(Model):
    __tablename__ = 'persons'
    __db__ = db
    model_manager = model_manager

    id_teste: int = Field.Integer(primary_key=True, auto_increment=True)
    first_name: str = Field.String(unique=True)
    last_name: str = Field.String(not_null=True)
    age: int = Field.BigInteger()
    salary: int = Field.BigInteger()

class Contact(Model):
    __tablename__ = 'contacts'
    __db__ = db
    model_manager = model_manager

    phone: str = Field.String(not_null=True)

    @classmethod
    def relationships(cls):
        cls.id_person = OneToOne(
            model=Person, name_constraint='person_contact')

We create the Person table and the Contact table. We use the OneToOne field. This field will be the PK of that table, being of the same type as the table in the relationship, in this case the same type as the PK of the Person model.

To save a Contact record:

1
2
3
4
5
person_1 = Person(first_name="Rich", last_name="Ramalho", age=22, salary=1250)
contact_person_1 = Contact(phone="XXXXXXXXX-XXXX", id_person=person_1)

person_1 = await Person.save(person_1)
contact_person_1 = await Contact.save(contact_person_1)

And with that, DuckORM will save the relationship contact record with this person. What happens if I try to save the same person again in a another contact?

1
2
3
4
contact_error = Contact(phone="YYYYYYYYY-YYYY", id_person=person_1)

await Contact.save(contact_error) # This line will throw a duplicate 
                                  # record exception.