what is Query.with_entities()?

what is Query.with_entities()?

sqlalchemy でwith_entitiesのこと聞かれたので日記に書いてみる。

short answer

queryの主体を変えるもの。(Select部分の書き換え)

A.query.filter(A.b_id==B.id).all()
# => [A,A,A,A,A]
A.query.filter(A.b_id==B.id).with_entities(B).all()
# => [B,B,B,B,B]

それぞれqueryは以下の様になる。

## print(A.query.filter(A.b_id==1)
SELECT "A".id AS "A_id", "A".b_id AS "A_b_id", "A".name AS "A_name"
FROM "A"
WHERE "A".b_id = :b_id_1

## print(A.query.filter(A.b_id==1).with_entities(B))
SELECT "B".id AS "B_id", "B".name AS "B_name"
FROM "B", "A"
WHERE "A".b_id = :b_id_1

動く例のgist

with_entities_sample.py https://gist.github.com/podhmo/9273699

long answer

通常のquery(各mapperにquery_propertyを定義している場合)ではqueryを始めたobjectが取得したい対象ということになる。

Model1.query.filter(Model1.id==1).all() #=> [Model1, Model1, Model1]

一方で、取得した対象を微妙に変えたい場合がある。例えば以下の様な状況

  • 特定のフィールドのみが欲しい場合
  • 集約した値も付加したい場合
  • 無理やりQuery objectを再利用したい場合

特定のフィールドのみが欲しい場合

A.query.filter()...と条件を続けていったが最終的に欲しいのがAのidとnameだけだった場合

def some_filter_function(query):
    ## do something
    return query


q = some_filter_function(a_query)
q.with_entities(A.id,A.name)
# => [(A.id,A.name),(A.id,A.name),(A.id,A.name)]

集約した値も付加したい場合

例えばクロス集計とかしたいとき。

以下の様なPersonテーブルがある。この血液型(blood)と性別(gender)でクロス集計したい。

class Person(Base):
    __tablename__ = "Person"
    query = Session.query_property()
    id = sa.Column(sa.Integer, primary_key=True)
    blood = sa.Column(sa.String(1)) #sloppy
    gender = sa.Column(sa.String(1)) #sloppy

この時発行したいSQL文のselect箇所はqueryの始まりのobject(大抵何らかのテーブルにマッピングされたクラス)ではなかったりする。

## q = some_filter(q)

q = q.with_entities(
    Person.blood, 
    f.sum(sa.case([(Person.gender=="F", 1)], else_=0)).label("F"), 
    f.sum(sa.case([(Person.gender=="M", 1)], else_=0)).label("M"), 
)

print("Blod, F, M")

for row in q.group_by(Person.blood).all():
    print("{o.blood}, {o.F}, {o.F}".format(o=row))

""" 
output:

Blod, F, M
A, 1, 1
AB, 0, 0
B, 0, 0
O, 1, 1
"""

動作するサンプル

with_entities2.py https://gist.github.com/podhmo/9274178

無理やりQuery objectを再利用したい場合

以前作ったライブラリの中で特定のユースケースで使われた関数がだいたいのところ用途にマッチしている。 これを使い回したいのだけれど微妙に異なるとかそういうとき。

これは公式のドキュメントの例も当てはまるかもしれない。

# Users, filtered on some arbitrary criterion
# and then ordered by related email address
q = session.query(User).\
            join(User.address).\
            filter(User.name.like('%ed%')).\
            order_by(Address.email)

# given *only* User.id==5, Address.email, and 'q', what
# would the *next* User in the result be ?
subq = q.with_entities(Address.email).\
            order_by(None).\
            filter(User.id==5).\
            subquery()
q = q.join((subq, subq.c.email < Address.email)).\
            limit(1)

正直なところ、with_entitiesの挙動を知らない人がこれを見て理解できるとはちょっと思えないけれど。 使い方の例として良さそうなので紹介してみる。

公式のドキュメントの例のqueryがしたかったこと

おそらくこのqueryは以下のようなテーブルの構造を想定している。

class User(Base):
    __tablename__ = "User"
    query = Session.query_property()
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(255))
    address = orm.relationship("Address")

class Address(Base):
    __tablename__ = "Address"
    query = Session.query_property()
    id = sa.Column(sa.Integer, primary_key=True)
    email = sa.Column(sa.String(255))
    user_id = sa.Column(sa.Integer, sa.ForeignKey(User.id))

利用者(User)と住所(Address)の2つのテーブルがあり。 利用者(User)に対して複数の住所(Address)が登録できるようになっている。

例えば賞金稼ぎの人向けのサービスでブラックリスト(あるいはビンゴブック)に登録されている賞金首の住所を管理しているのかもしれないし。 単に毎年の年賀状の送り先を管理するためのデータベースなのかもしれない。 どんなシステムなのかはわからないけれど。ともかく、利用者と住所が1対多の関係になっている。

そして、利用者に対して、何らかの順序(例えばアルファベット順)で有効な住所(Address)を返す機能があるのだろうと思う。 「User」というテーブル名なのだから少なくとも利用者(User)がいて、その利用者はこのシステムを使ったのではないかと推測できる。

勢い勇んで所定の住所に貸していたお金を取り返しに行ったものの既にもぬけの空だったのか。 単に送った手紙が宛先不在で返ってきたのか。 結局のところ何が起きたのかよくわからないけれど。ともかく、先のシステムが返した住所は不発に終わったという状況らしい。

このシステムの提供者としては、とりあえず、次の候補となる住所を返したいと、そういう感じなんだろうと思う。

実際のところ

質問自体は「次の住所は何?」ということのなのだけれど。

実際のところはもう少し詳細がはっきりしているらしい。 "ed"という文字が含まれた名前の利用者に、User.idが5の利用者に割り当てられている全ての住所より先の住所の内から次の住所を探したい。 ということなようだ。

「次の住所」をどうやって見つければ良いかということなのだけれど。 組み合わせをsqlで作る方法が分かっていないと何をしているのか判らないかもしれない。 これについてはひとつだけ説明しておいた方が良いことがありそうなのでちょっとだけ脱線して説明を加える。

組み合わせの作り方

おそらく知っている人がほとんどだろうし。考えればわかることではあるけれど。 知らない人には意味不明だし。考えるのが面倒だったりするとどういう挙動か判らないかもしれないので。

以下のことが結構重要だったり。

  • 2つのqueryをjoinすると直積が作れる
  • joinの条件に<をつけるとcombinationがとれる
  • 2つのqueryの内片側を絞ってあげればそれ以降・以前のものということになる

idとvalueの2つだけで説明。

テーブルが以下の様なとき

Model(id,value)
-------
1,a
2,b
3,c
4,d

2つのqueryをjoinすると直積が作れる

Model `join` Model' => 
[
((1,a), ((1,a),(2,b),(3,c),(4,d)))
((2,b), ((1,a),(2,b),(3,c),(4,d)))
((3,c), ((1,a),(2,b),(3,c),(4,d)))
((4,d), ((1,a),(2,b),(3,c),(4,d)))
]

joinの条件に<をつけるとcombinationがとれる

Model `join` Model' on Model.id > Model'.id
[
((1,a), ()) #1.aより小さなものは存在しない
((2,b), ((1,a)))
((3,c), ((1,a),(2,b)))
((4,d), ((1,a),(2,b),(3,c)))
]

2つのqueryの内片側を絞ってあげればそれ以降・以前のものということになる

Model `join` (Model'.id where id == 2)
[
((1,a), ((2,b)))
((2,b), ((2,b)))
((3,c), ((2,b)))
((4,d), ((2,b)))
]

Model `join` Model' on Model.id > (Model'.id where id == 2)
[
((1,a), ())
((2,b), ())
((3,c), ((2,b)))
((4,d), ((2,b)))
]

Model `join` Model' on (Model.id == 2) > Model'.id
[
((2,b), ((1,a),(2,b)))
]

脱線終わり。元の場所に戻る。

式の説明(sqlalchemy documentの説明,無理やりQuery objectを再利用したい場合)

ちょっとだけ公式ドキュメントに載っていたぱっと見判らない式の説明をしてみる。

元々の問題は意訳すると以下の様なものだった。

"ed"という文字が含まれた名前の利用者が犯人の候補らしい。
捜査の結果、User.idが5の人が怪しい。
User.idが5の人に割り当てられていた住所をに調べ行ってみたものの全部空振りに終わった。
これらの住所より(多分アルファベット順で)前の住所も全て虱潰しに調べてある。

見つけ出さないと腹の虫が収まらないので次の捜索先の住所を教えて欲しい。

説明のために内容を少し書き換えてあるけれど。システムの内部は恐らく以下のようなイメージ。

利用者の検索に便利なクラスのメソッドかモジュールで定義されている関数かとりあえず何かがある。 ここではUserFindModuleに定義されているfind_by_nameという関数があるということにする。

class UserFindModule:
    @staticmethod
    def find_by_name(name):
        """ 何か良さそうな関数 """
        return Session.query(User).\
            join(User.address).\
            filter(User.name.like(name)).\
            order_by(Address.email)

これで対象の利用者(edが含まれた利用者)のクエリーが取得できる。そして先ほどの組み合わせの要領で次の捜索先を探せる。

具体的には以下の手順。

  • 元となる候補のquery作成(q)
  • (おそらく後で何かの罰を受ける)User.id==5の住所を取得(sub query)
  • User.id==5の持つ住所よりも何らかの順序関係(例えばアルファベット順)で大きいものを取るjoinを作る

そしてこれが本来の目的だったけれど。with_entitiesがどこで使われているかというと。 元となる候補のqueryからAddress.emailのみのsubqueryを作るのに使われている。 (元々はqueryの主体がUserだったものをAddress.emailに変更)

## 元となる候補のquery作成
q = UserFindModule.find_by_name("%ed%")


## (おそらく後で何かの罰を受ける)User.id==5の住所を取得
subq = (
    q.with_entities(Address.email) ## そもそもUser.email以外要らない
    .order_by(None) ## order_byが邪魔
    .filter(User.id==5) ## idが5のものだけ使いたい
).subquery()

## User.id==5の持つ住所よりも何らかの順序関係(例えばアルファベット順)で大きいものを取るjoinを作る
## qはorder_byが効いているので次の順序が手に入る。
q = q.join((subq, subq.c.email < Address.email)).\
            limit(1)
落ち葉拾い

order_byやlimitなどはNoneを渡すと付加してた設定を取り消せる

print(Person.query.limit(1))
print(Person.query.limit(1).limit(None))

"""
SELECT "Person".id AS "Person_id", "Person".blood AS "Person_blood", "Person".gender AS "Person_gender" 
FROM "Person"
 LIMIT ? OFFSET ?
SELECT "Person".id AS "Person_id", "Person".blood AS "Person_blood", "Person".gender AS "Person_gender" 
FROM "Person"
"""

print(Person.query.order_by(sa.desc(Person.id)))
print(Person.query.order_by(sa.desc(Person.id)).order_by(None))
print(Person.query.order_by(sa.desc(Person.id)).order_by(sa.asc(Person.id)))

"""
SELECT "Person".id AS "Person_id", "Person".blood AS "Person_blood", "Person".gender AS "Person_gender" 
FROM "Person" ORDER BY "Person".id DESC
SELECT "Person".id AS "Person_id", "Person".blood AS "Person_blood", "Person".gender AS "Person_gender" 
FROM "Person"
SELECT "Person".id AS "Person_id", "Person".blood AS "Person_blood", "Person".gender AS "Person_gender" 
FROM "Person" ORDER BY "Person".id DESC, "Person".id ASC
"""