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