I’m creating a bot to rate a user’s appearance from other users in aiogram 3.
I tried to implement saving information about user ratings and for this I created a second model.
class Profile(Base):
__tablename__ = 'profile'
id_all: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
id: Mapped[str] = mapped_column(String(40))
name: Mapped[str] = mapped_column(String(30), nullable=False)
gender: Mapped[str] = mapped_column(String(8), nullable=False)
age: Mapped[str] = mapped_column(String(30), nullable=False)
country: Mapped[str] = mapped_column(String(30), nullable=False)
city: Mapped[str] = mapped_column(String(30), nullable=False)
photography: Mapped[str] = mapped_column(String(150))
description: Mapped[str] = mapped_column(String(255), default='')
count_grades: Mapped[int] = mapped_column(default=0)
all_grades: Mapped[int] = mapped_column(default=0)
class NewGrades(Base):
__tablename__ = 'newgrades'
id_all: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
id_user: Mapped[str] = mapped_column(ForeignKey('profile.id', ondelete='CASCADE'))
liked_id: Mapped[str] = mapped_column(String(40), default=None)
new_count_grades: Mapped[int] = mapped_column(default=0)
grade: Mapped[int] = mapped_column(default=None)
profile: Mapped['Profile'] = relationship(backref='newgrades')
I encountered the following error: sys:1: SAWarning: SELECT statement has a cartesian product between FROM element(s) “newgrades” and FROM element “profile”. Apply join condition(s) between each element to resolve.
To add information to a table and obtain information from it, I use the following ORM:
async def orm_add_grade(session: AsyncSession, data: dict):
obj = (NewGrades, Profile(
id_user=data['id'],
liked_id=data['liked_id'],
grade=data['grade']
))
session.add(obj)
await session.commit()
async def orm_update_new_count_grade(session: AsyncSession, profile_id: int, new_count_grades: str):
query = update(NewGrades).values(new_count_grades=new_count_grades).where(
NewGrades.id_user == str(profile_id))
await session.execute(query)
await session.commit()
async def orm_get_grade(session: AsyncSession, profile_id: int):
query = select(NewGrades, Profile).where(NewGrades.id_user == str(profile_id))
result = await session.execute(query)
return result.scalar()
Handlers in which I use this:
@user_assessment_router.message(or_f(F.text.contains('Rate'), F.text.contains('➡️Skip')))
async def grade(message: Message, session: AsyncSession, state: FSMContext):
await state.clear()
profile = await orm_get_random_profile(session, message.from_user.id)
await state.update_data(liked_id=profile.id)
await state.update_data(all_grades=profile.all_grades)
await state.update_data(count_grades=profile.count_grades + 1)
await state.update_data(id=message.from_user.id)
await message.answer_photo(
profile.photography,
caption=f'''⭐️Name: <strong>{profile.name}, {profile.age}</strong> yearsn
????Country: <strong>{profile.country}</strong>
????City: <strong>{profile.city}</strong>
{profile.description}''',
reply_markup=get_keyboard(
'1',
'2',
'3',
'4',
'5',
'6',
'7',
'8',
'9',
'10',
'➡️Skip',
'????Main menu',
sizes=(5, 5, 2),
))
await state.set_state(AddProfile.grade)
@user_assessment_router.message(AddProfile.grade, F.text)
async def next_grade(message: Message, session: AsyncSession, state: FSMContext):
try:
s = int(message.text)
if 0 < s < 11:
await state.update_data(grade=message.text)
data = await state.get_data()
grade_info = await orm_get_grade(session, message.from_user.id)
await state.update_data(new_count_grades=grade_info.new_count_grades + 1)
await orm_add_grade(session, data)
data = await state.get_data()
await orm_update_new_count_grade(session, data['id'], data['new_count_grades'])
await orm_rate_profile(session, data['liked_id'], int(data['grade']) + int(data['all_grades']))
await orm_count_grades(session, data['liked_id'], int(data['count_grades']))
await grade(message, session, state)
else:
await message.answer('❌Ratings from <b>1</b> before <b>10</b>❌')
except:
await state.clear()
The second day I can’t figure out what the problem is, I searched on different resources, but it’s not exactly what I need.