alimuradov/orders_crud.py

## orders_crud.py
from datetime import datetime
from email.policy import default
import uuid
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import relationship, backref
from sqlalchemy.sql import func
from sqlalchemy.ext.mutable import MutableDict, MutableList

from app.database.base_class import Base
from app.models.stock import Stock

class OrderProducts(Base):
    """Таблица строк заказ для упрощенного заказа"""
    rowsId = sa.Column(sa.Integer, primary_key=True, index=True)
    id = sa.Column(UUID(as_uuid=True), sa.ForeignKey('stock.prtId', ondelete="CASCADE"), nullable=False, index=True)
    article = sa.Column(sa.Integer)
    name = sa.Column(sa.String, nullable=True, index=True)
    price = sa.Column(sa.Float)
    qty = sa.Column(sa.Float)
    orderId = sa.Column(sa.Integer, sa.ForeignKey('order.id', ondelete="CASCADE"), nullable=False, index=True)
    order = relationship("Order", back_populates="products")
    stock = relationship("Stock", back_populates="product", primaryjoin=Stock.prtId==id)


class Order(Base):
    """Упрощенная таблица для заказов"""
    id = sa.Column(sa.Integer, primary_key=True, index=True)
    storeId = sa.Column(UUID(as_uuid=True), sa.ForeignKey('store.storeId', ondelete="CASCADE"), nullable=True, index=True)
    store = relationship("Store",  back_populates="order")
    date = sa.Column(sa.DateTime, nullable=False)
    src = sa.Column(sa.String)
    status = sa.Column(sa.String)
    phone = sa.Column(sa.String, nullable=True)
    customerName = sa.Column(sa.String)
    customerId = sa.Column(UUID(as_uuid=True), nullable=True)
    samovivoz = sa.Column(sa.Boolean, default=False)
    type = sa.Column(sa.String, nullable=True, default="delivery")
    price = sa.Column(sa.Float, nullable=True)
    deliveryInfo  = sa.Column(MutableDict.as_mutable(JSONB))
    # goods = sa.Column(MutableList.as_mutable(JSONB))
    сreated_at = sa.Column(sa.DateTime(timezone=True), server_default=func.now())
    updated_at = sa.Column(sa.DateTime(timezone=True), onupdate=func.now())
    products = relationship("OrderProducts", back_populates="order", cascade="all, delete-orphan", primaryjoin=OrderProducts.orderId==id)


def get_orders_crud(
    db:Session,
    skip: int = 0,
    limit: int = 100,
    date_from: datetime = None,
    date_to: datetime = None,
    courier_id: UUID4 = None,
    customer_id: UUID4 = None):

    stmt = select(
        Order.id,
        Order.storeId,
        Order.date,
        Order.src,
        Order.status,
        Order.customerName,
        Order.customerId,
        Order.type,
        Order.deliveryInfo,
        Order.products,
        func.sum(OrderProducts.price).label("price")).join(OrderProducts, Order.id==OrderProducts.orderId).options(subqueryload(Order.products))
    if date_from is not None:
        stmt = stmt.where(Order.date > date_from)
    if date_to is not None:
        stmt = stmt.where(Order.date < date_to)
    if courier_id is not None:
        stmt = stmt.where(Order.deliveryInfo['courierId'].astext == courier_id)
    if customer_id is not None:
        stmt = stmt.where(Order.customerId == customer_id)
    if limit is not None:
        stmt = stmt.limit(limit)
    if skip is not None:
        stmt = stmt.offset(skip)
    stmt = stmt.order_by(desc(Order.date))
    stmt = stmt.group_by(Order.id)
    res = db.execute(stmt).all()
    return res


ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/uvicorn/protocols/http/h11_impl.py", line 366, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
    return await self.app(scope, receive, send)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/fastapi/applications.py", line 261, in __call__
    await super().__call__(scope, receive, send)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
    raise e
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/routing.py", line 656, in __call__
    await route.handle(scope, receive, send)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/routing.py", line 259, in handle
    await self.app(scope, receive, send)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/routing.py", line 61, in app
    response = await func(request)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/fastapi/routing.py", line 227, in app
    raw_response = await run_endpoint_function(
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/fastapi/routing.py", line 162, in run_endpoint_function
    return await run_in_threadpool(dependant.call, **values)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/concurrency.py", line 39, in run_in_threadpool
    return await anyio.to_thread.run_sync(func, *args)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/anyio/to_thread.py", line 28, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(func, *args, cancellable=cancellable,
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 818, in run_sync_in_worker_thread
    return await future
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 754, in run
    result = context.run(func, *args)
  File "/home/nariman/dev/apteka149/backend/app/api/v5/orders.py", line 46, in get_orders_list
    orders = get_orders_crud(
  File "/home/nariman/dev/apteka149/backend/app/crud/order.py", line 149, in get_orders_crud
    res = db.execute(stmt).all()
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1692, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 326, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1490, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 524, in _compile_w_cache
    compiled_sql = self._compiler(
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 559, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 776, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 451, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 486, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 3265, in visit_select
    compile_state = select_stmt._compile_state_factory(
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/base.py", line 510, in create_for_statement
    return klass.create_for_statement(statement, compiler, **kw)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/context.py", line 731, in create_for_statement
    opt.process_compile_state(self)
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 260, in process_compile_state
    self._process(
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 905, in _process
    val._bind_loader(
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 1061, in _bind_loader
    entity = self._find_entity_prop_comparator(
  File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 1146, in _find_entity_prop_comparator
    raise sa_exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Query has only expression-based entities, which do not apply to relationship property "Order.products"
	from datetime import datetime
	from email.policy import default
	import uuid
	import sqlalchemy as sa
	from sqlalchemy.dialects.postgresql import UUID, JSONB
	from sqlalchemy.orm import relationship, backref
	from sqlalchemy.sql import func
	from sqlalchemy.ext.mutable import MutableDict, MutableList

	from app.database.base_class import Base
	from app.models.stock import Stock

	class OrderProducts(Base):
	"""Таблица строк заказ для упрощенного заказа"""
	rowsId = sa.Column(sa.Integer, primary_key=True, index=True)
	id = sa.Column(UUID(as_uuid=True), sa.ForeignKey('stock.prtId', ondelete="CASCADE"), nullable=False, index=True)
	article = sa.Column(sa.Integer)
	name = sa.Column(sa.String, nullable=True, index=True)
	price = sa.Column(sa.Float)
	qty = sa.Column(sa.Float)
	orderId = sa.Column(sa.Integer, sa.ForeignKey('order.id', ondelete="CASCADE"), nullable=False, index=True)
	order = relationship("Order", back_populates="products")
	stock = relationship("Stock", back_populates="product", primaryjoin=Stock.prtId==id)


	class Order(Base):
	"""Упрощенная таблица для заказов"""
	id = sa.Column(sa.Integer, primary_key=True, index=True)
	storeId = sa.Column(UUID(as_uuid=True), sa.ForeignKey('store.storeId', ondelete="CASCADE"), nullable=True, index=True)
	store = relationship("Store", back_populates="order")
	date = sa.Column(sa.DateTime, nullable=False)
	src = sa.Column(sa.String)
	status = sa.Column(sa.String)
	phone = sa.Column(sa.String, nullable=True)
	customerName = sa.Column(sa.String)
	customerId = sa.Column(UUID(as_uuid=True), nullable=True)
	samovivoz = sa.Column(sa.Boolean, default=False)
	type = sa.Column(sa.String, nullable=True, default="delivery")
	price = sa.Column(sa.Float, nullable=True)
	deliveryInfo = sa.Column(MutableDict.as_mutable(JSONB))
	# goods = sa.Column(MutableList.as_mutable(JSONB))
	сreated_at = sa.Column(sa.DateTime(timezone=True), server_default=func.now())
	updated_at = sa.Column(sa.DateTime(timezone=True), onupdate=func.now())
	products = relationship("OrderProducts", back_populates="order", cascade="all, delete-orphan", primaryjoin=OrderProducts.orderId==id)


	def get_orders_crud(
	db:Session,
	skip: int = 0,
	limit: int = 100,
	date_from: datetime = None,
	date_to: datetime = None,
	courier_id: UUID4 = None,
	customer_id: UUID4 = None):

	stmt = select(
	Order.id,
	Order.storeId,
	Order.date,
	Order.src,
	Order.status,
	Order.customerName,
	Order.customerId,
	Order.type,
	Order.deliveryInfo,
	Order.products,
	func.sum(OrderProducts.price).label("price")).join(OrderProducts, Order.id==OrderProducts.orderId).options(subqueryload(Order.products))
	if date_from is not None:
	stmt = stmt.where(Order.date > date_from)
	if date_to is not None:
	stmt = stmt.where(Order.date < date_to)
	if courier_id is not None:
	stmt = stmt.where(Order.deliveryInfo['courierId'].astext == courier_id)
	if customer_id is not None:
	stmt = stmt.where(Order.customerId == customer_id)
	if limit is not None:
	stmt = stmt.limit(limit)
	if skip is not None:
	stmt = stmt.offset(skip)
	stmt = stmt.order_by(desc(Order.date))
	stmt = stmt.group_by(Order.id)
	res = db.execute(stmt).all()
	return res



	ERROR: Exception in ASGI application
	Traceback (most recent call last):
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/uvicorn/protocols/http/h11_impl.py", line 366, in run_asgi
	result = await app(self.scope, self.receive, self.send)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
	return await self.app(scope, receive, send)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/fastapi/applications.py", line 261, in __call__
	await super().__call__(scope, receive, send)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/applications.py", line 112, in __call__
	await self.middleware_stack(scope, receive, send)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/middleware/errors.py", line 181, in __call__
	raise exc
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/middleware/errors.py", line 159, in __call__
	await self.app(scope, receive, _send)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in __call__
	raise exc
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/exceptions.py", line 71, in __call__
	await self.app(scope, receive, sender)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
	raise e
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
	await self.app(scope, receive, send)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/routing.py", line 656, in __call__
	await route.handle(scope, receive, send)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/routing.py", line 259, in handle
	await self.app(scope, receive, send)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/routing.py", line 61, in app
	response = await func(request)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/fastapi/routing.py", line 227, in app
	raw_response = await run_endpoint_function(
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/fastapi/routing.py", line 162, in run_endpoint_function
	return await run_in_threadpool(dependant.call, **values)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/starlette/concurrency.py", line 39, in run_in_threadpool
	return await anyio.to_thread.run_sync(func, *args)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/anyio/to_thread.py", line 28, in run_sync
	return await get_asynclib().run_sync_in_worker_thread(func, *args, cancellable=cancellable,
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 818, in run_sync_in_worker_thread
	return await future
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 754, in run
	result = context.run(func, *args)
	File "/home/nariman/dev/apteka149/backend/app/api/v5/orders.py", line 46, in get_orders_list
	orders = get_orders_crud(
	File "/home/nariman/dev/apteka149/backend/app/crud/order.py", line 149, in get_orders_crud
	res = db.execute(stmt).all()
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1692, in execute
	result = conn._execute_20(statement, params or {}, execution_options)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
	return meth(self, args_10style, kwargs_10style, execution_options)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 326, in _execute_on_connection
	return connection._execute_clauseelement(
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1490, in _execute_clauseelement
	compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 524, in _compile_w_cache
	compiled_sql = self._compiler(
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 559, in _compiler
	return dialect.statement_compiler(dialect, self, **kw)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 776, in __init__
	Compiled.__init__(self, dialect, statement, **kwargs)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 451, in __init__
	self.string = self.process(self.statement, **compile_kwargs)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 486, in process
	return obj._compiler_dispatch(self, **kwargs)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
	return meth(self, **kw)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 3265, in visit_select
	compile_state = select_stmt._compile_state_factory(
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/sql/base.py", line 510, in create_for_statement
	return klass.create_for_statement(statement, compiler, **kw)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/context.py", line 731, in create_for_statement
	opt.process_compile_state(self)
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 260, in process_compile_state
	self._process(
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 905, in _process
	val._bind_loader(
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 1061, in _bind_loader
	entity = self._find_entity_prop_comparator(
	File "/home/nariman/.local/share/virtualenvs/backend-HlH61cai/lib/python3.9/site-packages/sqlalchemy/orm/strategy_options.py", line 1146, in _find_entity_prop_comparator
	raise sa_exc.ArgumentError(
	sqlalchemy.exc.ArgumentError: Query has only expression-based entities, which do not apply to relationship property "Order.products"