概要
sqlalchemyを利用して1:多構造のテーブルからlimitで指定した件数のデータを取得したいです。
このとき、子テーブルのカラムを条件として指定して取得するのですが、limitの対象が子テーブルの行数となってしまい、親データが指定行数取得できませんでした。
group_byを子テーブルに指定することで解決したのですが、今度は子テーブルのカラムに対する条件を指定しなかった場合エラーメッセージが出てしまい困っています。
SQLド素人なもので、なにか解決方法があれば教えていただきたく。
環境
python version 3.9
mysql version 8.0
sqlalchemy version 1.4.27
現象再現ソースコード
search_users関数を編集して用意してある2つのテストに通れば大丈夫そうなのですが、、
アプローチとしては
- group_byを指定しなくもuserテーブルのレコード数をlimitの対象とするようにする
- group_byを指定した状態でWHEREの条件とされなくてもエラーがでなくする
- search_pet_nameがfalsyの場合に全データ(null含む)取得可能なダミーのフィルタをappendする
あたりかと考えています
python
1from sqlalchemy.ext.declarative import declarative_base 2from sqlalchemy import create_engine, Column, Integer, String, MetaData, ForeignKey 3from sqlalchemy.orm import joinedload, relationship, sessionmaker 4from sqlalchemy.sql.expression import and_ 5import logging 6 7 8MYSQL = 'mysql://user:password@127.0.0.1:3306/db' 9engine = create_engine(MYSQL) 10logging.basicConfig() 11logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO) 12metadata = MetaData() 13 14 15metadata = MetaData() 16Base = declarative_base() 17 18class User(Base): 19 __tablename__ = 'user' 20 id = Column(Integer, primary_key=True) 21 name = Column(String(30), nullable=False) 22 pets = relationship( 23 "Pet", backref="user" 24 ) 25 26class Pet(Base): 27 __tablename__ = "pet" 28 name = Column(String(30), primary_key=True) 29 user_id = Column(Integer, ForeignKey("user.id"), index=True) 30 31 32 33Session = sessionmaker(bind=engine) 34 35session = Session() 36 37def insert_user(session): 38 d = [] 39 for i in range(1, 101): 40 d.append( 41 User( 42 name=f"user{i}", 43 ) 44 ) 45 session.add_all(d) 46 session.commit() 47 48def insert_pets(users, session): 49 for i, user in enumerate(users): 50 for j in range(1, i%3+1): 51 user.pets.append( 52 Pet( 53 name=f"{user.name}のペット{j}" 54 ) 55 ) 56 session.commit() 57 58def search_users(limit, search_pet_name): 59 filters = [] 60 if search_pet_name: 61 filters.append(Pet.name.like(f"%{search_pet_name}%")) 62 users = ( 63 session.query(User) 64 .outerjoin(Pet) 65 .options(joinedload(User.pets)) 66 .filter(and_(True, *filters)) # SEARCH_PET_NAMEがfalsyな場合、Pet.nameに対する条件が存在せずエラー 67 .group_by(Pet.user_id) # コメントアウトすると SEARCH_PET_NAMEがfalsyであっても動作するが、件数が子レコード基準となってしまう 68 .limit(limit) 69 .all() 70 ) 71 return users 72 73def setup(): 74 Base.metadata.create_all(bind=engine) 75 insert_user(session) 76 users = session.query(User).options(joinedload(User.pets)).all() 77 insert_pets(users, session) 78 79def test1(): 80 SEARCH_PET_NAME = "ペット" 81 LIMIT = 66 82 users = search_users(LIMIT, SEARCH_PET_NAME) 83 print(len(users)) 84 assert len(users) == LIMIT 85 86def test2(): 87 SEARCH_PET_NAME = None 88 LIMIT = 80 89 users = search_users(LIMIT, SEARCH_PET_NAME) 90 print(len(users)) 91 assert len(users) == LIMIT 92 93setup()#初回のみ 94test1() 95test2()
エラーメッセージ
search_pet_nameがfalsyな場合に発生するメッセージです
MySQLdb._exceptions.OperationalError: (1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")
こちらについて調べてみるとMysql側の設定の変更で解決する手法が出てきましたが、DBの設定変更は不可です
あなたの回答
tips
プレビュー