Many-to-many relation, adding property from association table

[kast3t]

Discussed in https://github.com/python-gino/gino/discussions/787

^{Originally posted by conguerorKK July 13, 2021} Greetings,

I've 3 tables: "tasks", "services" and "assoc_tasks_services". They contain data like this:

tasks:

task_id	user_id	price
...	...	...
84	1	174.25
85	1	250.25
86	1	300.00
87	1	1050.49
88	1	600.00

services:

service_id	name
...	...
4	Service_1
5	Service_2
19	Service_3

assoc_tasks_services:

task_id	service_id	count
...	...	...
84	4	0
85	5	0
86	19	3
87	19	6
88	19	1
88	4	0

So, in "tasks" I keep tasks, which may include several services, in "services" I keep names of services, their prices, etc., and in "assoc_tasks_services" I keep associations: which task, which services and count of service (for each service it might be 0, 1, or more).

There are 2 problems:

1.) I can't use .limit() correctly. For example, I need only last 5 tasks (84-88), but when I make query, it returns 85, 86, 87, 88 and 88 (it doesn't count 2 entries of task №88 as one).

2.) How can I attach property "count" of table "assoc_tasks_services" in every service in every task?

Please, help me. I'm not good at programming, but I'm trying to understand how can I realize it. Thank you!

Code of initialisation of tables:

class Service(db.Model):
    __tablename__ = 'services'

    service_id = db.Column(db.Integer, autoincrement=True, primary_key=True, nullable=False)
    name = db.Column(db.String, nullable=False)
    price = db.Column(db.Numeric(7, 2))

    def __init__(self, **kw):
        super().__init__(**kw)
        self._tasks = set()
        self._count = set()

    @property
    def tasks(self):
        return self._tasks

    @property
    def count(self):
        return self._count

    def add_count(self, count):
        self._count.add(count)

class Task(db.Model):
    __tablename__ = 'tasks'

    task_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    user_id = db.Column(db.BigInteger, db.ForeignKey('clients.user_id', ondelete='CASCADE'), nullable=False)
    price = db.Column(db.Numeric(7, 2))

    def __init__(self, **kw):
        super().__init__(**kw)
        self._services = set()
        self._count = set()

    @property
    def services(self):
        return self._services

    def add_service(self, service):
        self._services.add(service)
        service._tasks.add(self)

    @property
    def count(self):
        return self._count

    def add_count(self, count):
        self._count.add(count)

class AssocTasksServices(db.Model):
    __tablename__ = 'assoc_tasks_services'

    task_id = db.Column(db.Integer, db.ForeignKey('tasks.task_id', ondelete='CASCADE'), nullable=False)
    service_id = db.Column(db.Integer, db.ForeignKey('services.service_id', ondelete='CASCADE'), nullable=False)
    count = db.Column(db.Integer, nullable=False)

Attempt №1 (added property "count" to task. It's the most successful attempt):

async def get_users_tasks(user_id: int) -> List[Task]:
    query = Task.outerjoin(AssocTasksServices, Task.task_id == AssocTasksServices.task_id). \
        outerjoin(Service, AssocTasksServices.service_id == Service.service_id). \
        select().where(Task.user_id == user_id).order_by(Task.task_id.desc()).limit(5)

    tasks_loader = Task.distinct(Task.task_id).load(add_service=Service.distinct(Service.service_id),
                                                    add_count=AssocTasksServices.count))
    tasks = await query.gino.load(tasks_loader).all()
    return tasks

Printing result №1:

tasks = await get_users_tasks(1)
for task in tasks:
    for service in task.services:
        print(f'Task №{task.task_id} - {service.name}. Count: {task.count}')

It returns:

Task №88 - Service_3. Count: {0, 1}  # But I need Count: {1}, not {0, 1}, because count of Service_3 is 1 for Task №88 according to the table
Task №88 - Service_1. Count: {0, 1}  # But I need Count: {0}, not {0, 1}, because count of Service_1 is 0 for Task №88 according to the table
Task №87 - Service_3. Count: {6}  # Correct
Task №86 - Service_3. Count: {3}  # Correct
Task №85 - Service_2. Count: {0}  # Correct

Attempt №2 (added property "count" to service):

async def get_users_tasks(user_id: int) -> List[Task]:
    query = Task.outerjoin(AssocTasksServices, Task.task_id == AssocTasksServices.task_id). \
        outerjoin(Service, AssocTasksServices.service_id == Service.service_id). \
        select().where(Task.user_id == user_id).order_by(Task.task_id.desc()).limit(5)

    tasks_loader = Task.distinct(Task.task_id).load(add_service=Service.distinct(Service.service_id).load(
                                                add_count=AssocTasksServices.count))
    tasks = await query.gino.load(tasks_loader).all()
    return tasks

Printing result №2:

tasks = await get_users_tasks(1)
for task in tasks:
    for service in task.services:
        print(f'Task №{task.task_id} - {service.name}. Count: {service.count}')

It returns:

Task №88 - Service_3. Count: {1, 3, 6}  # But I need Count: {1}, not {1, 3, 6}, because count of Service_3 is 1 for Task №88 according to the table
Task №88 - Service_1. Count: {0}  # Correct
Task №87 - Service_3. Count: {1, 3, 6}  # Incorrect
Task №86 - Service_3. Count: {1, 3, 6}  # Incorrect
Task №85 - Service_2. Count: {0}  # Correct</div>