I'm using the (excellent) Peewee ORM for my query needs in Python and I now want to convert the following query:
select t1.created, t1.property_id, t1.requesting_user_id, t1.type, t1.response
from pdr t1
inner join (
select max(id) as id, property_id, requesting_user_id
from pdr
where property_id = 7
group by requesting_user_id
) as t2 on t2.id = t1.id
So I came up with the following:
PDR.select()\
.join(PDR)\
.where(PDR.property == 7)\
.group_by(PDR.requesting_user)
but this creates the following sql:
SELECT t1.id, t1.created, t1.property_id, t1.requesting_user_id, t1.type, t1.comment, t1.responding_user_id, t1.property_details_request_id, t1.response
FROM pdr AS t1
INNER JOIN pdr AS t1
ON (t1.property_details_request_id = t1.id)
WHERE (t1.property_id = 7)
GROUP BY t1.requesting_user_id
I tried a couple other variations, but I'm kinda stuck.
Does anybody know how I can convert my query to Peewee? All tips are welcome!
Try the following (untested, but hopefully helpful):
PDRAlias = PDR.alias()
subq = (PDRAlias
.select(fn.MAX(PDRAlias.id).alias('max_id'), PDRAlias.property, PDRAlias.requesting_user)
.where(PDRAlias.property == 7)
.group_by(PDRAlias.requesting_user)
.alias('subq'))
query = (PDR
.select()
.join(subq, on=(subq.c.max_id == PDR.id)))
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加