Open
Description
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
Labels
No labels