|
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 No88 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 No1 (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 No1:
tasks = await get_users_tasks(1)
for task in tasks:
for service in task.services:
print(f'Task No{task.task_id} - {service.name}. Count: {task.count}')
It returns:
Task No88 - Service_3. Count: {0, 1} # But I need Count: {1}, not {0, 1}, because count of Service_3 is 1 for Task No88 according to the table
Task No88 - Service_1. Count: {0, 1} # But I need Count: {0}, not {0, 1}, because count of Service_1 is 0 for Task No88 according to the table
Task No87 - Service_3. Count: {6} # Correct
Task No86 - Service_3. Count: {3} # Correct
Task No85 - Service_2. Count: {0} # Correct
Attempt No2 (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 No2:
tasks = await get_users_tasks(1)
for task in tasks:
for service in task.services:
print(f'Task No{task.task_id} - {service.name}. Count: {service.count}')
It returns:
Task No88 - Service_3. Count: {1, 3, 6} # But I need Count: {1}, not {1, 3, 6}, because count of Service_3 is 1 for Task No88 according to the table
Task No88 - Service_1. Count: {0} # Correct
Task No87 - Service_3. Count: {1, 3, 6} # Incorrect
Task No86 - Service_3. Count: {1, 3, 6} # Incorrect
Task No85 - Service_2. Count: {0} # Correct
|