Complex SQL Queries on Ormar (Window Functions, Partition, Not Exists and Nested Selects) #460
-
I would like to execute a slightly complex SQL query on my model. Given a model class OperType(str, enum.Enum):
BID = "BID"
WIT = "WIT" # WIthdrawn
class Auction(orm.Model):
id: int = orm.Integer(primary_key=True)
type: OperType = orm.String(max_length=32, choices=list(OperType))
product_name: str = orm.String(max_length=32)
user_name: str = orm.String(max_length=32)
value: int = orm.Integer()
created_at: datetime.datetime = orm.DateTime(default=datetime.datetime.now)
class Meta(BaseMeta):
tablename = 'auction' I would like to perform the given SQL Query: select type, product_name, user_name, value, created_at
from (
select a1.*, dense_rank() over (partition by product_name, user_name order by value desc) as rnk
from auction a1
where a1.product_name = 'Ball'
and type = 'BID'
and not exists (select *
from auction a2
where a2.product_name = a1.product_name
and a2.user_name = a1.user_name
and a2.type = 'WIT'
and a2.created_at > a1.created_at)
) t
where rnk = 1
order by created_at; Equivalantly I could get similar results using
I know how to execute the filter and sorting. But I have no Idea in how to run the Just to explain what that query do, it will fetch all highest How can I achieve equivalent results with orrmar, it should be amazing if that could be done directly on DB without the overhead to pass the whole data to Python, but if not possible, how can I do the same over the models in directly in Python? |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment
-
Since each query in ormar starts and ends with a Your best call is to simply execute a raw query that you already have and you can do so through databases: https://www.encode.io/databases/database_queries/#raw-queries Note that you have to use the same databases that is used in model's |
Beta Was this translation helpful? Give feedback.
Since each query in ormar starts and ends with a
pydantic
/ormar
model it's currently not possible through ormar (i.e. there is no column danse_rank in your model so ormar would have to create it on the fly and infer a type properly, there are discussions and issues around ctes and other functions but it's not implemented yet).Your best call is to simply execute a raw query that you already have and you can do so through databases: https://www.encode.io/databases/database_queries/#raw-queries
Note that you have to use the same databases that is used in model's
Meta
.