Skip to content

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

Open
@kast3t

Description

@kast3t

Discussed in #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>

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions