piccolo-orm
Reverse on_join when one to one Foreign Key for objects interface
I need a way to create object with reverse foreign key
like this
await Band.select(
Band.name,
Band.id.join_on(FanClub.band).address.as_alias("address")
)
but for objects:
[!WARNING]
await Band.objects( Band.name, Band.id.join_on(FanClub.band).address.as_alias("address") )return ERROR
Is it is possible to fetch reverse join for many objects - i.e. get list? Actually, it will be awesome if you tell me easy way to serialize complex dict into Table object
Example of order dict
{'advertisement.id': 16, 'advertisement.ads_type': 1, 'advertisement.seller': 3, 'advertisement.wallet': 22, 'advertisement.fiat_currency': 4, 'advertisement.crypto_currency': 4, 'advertisement.buyer_requirements': 1, 'advertisement.minimum_rating': 0.0, 'advertisement.total_amount_mantissa': 44000, 'advertisement.total_amount_exponent': -2, 'advertisement.min_amount_mantissa': 2000, 'advertisement.min_amount_exponent': -2, 'advertisement.max_amount_mantissa': 50000, 'advertisement.max_amount_exponent': -2, 'advertisement.is_active': True, 'advertisement.price_per_unit_mantissa': 4, 'advertisement.price_per_unit_exponent': 1, 'advertisement.price_per_unit': 40000000000, 'advertisement.time_limit_seconds': 3600, 'advertisement.terms_of_use': 'User 2', 'advertisement.remarks': None, 'advertisement.country': 229, 'advertisement.status': 1, 'advertisement.bank_payment_methods': [127656, 218460, 6, 7], 'advertisement.payment_methods_amounts_mantissa': [], 'advertisement.payment_methods_amounts_exponent': [], 'advertisement.created_at': datetime.datetime(2024, 12, 23, 13, 45, 33, 447435, tzinfo=datetime.timezone.utc), 'advertisement.expires_at': datetime.datetime(2025, 1, 23, 13, 45, 33, 445700, tzinfo=datetime.timezone.utc), 'advertisement.updated_at': datetime.datetime(2024, 12, 23, 13, 45, 33, 447458, tzinfo=datetime.timezone.utc), 'advertisement.hash': 'c94267710caf3c0b8a944c5302abcc136521cbe81d694f6a66da57585ea4787a', 'advertisement.seller.id': 3, 'advertisement.seller.first_name': 'Олександра', 'advertisement.seller.telegram_id': 501904289, 'advertisement.seller.username': 'k_sssashkaaa', 'advertisement.seller.photo_url': 'https://t.me/i/userpic/320/-P1E47kcGbXmbww4nthwAwyJNnXNBvhr49WXAFFE_X4.svg', 'advertisement.seller.auth_date': datetime.datetime(2024, 12, 18, 17, 13, 30, tzinfo=datetime.timezone.utc), 'advertisement.seller.created_at': datetime.datetime(2024, 12, 18, 17, 13, 34, 173242, tzinfo=datetime.timezone.utc), 'advertisement.seller.locked': False, 'advertisement.seller.is_active': True, 'advertisement.seller.secret_code': None, 'advertisement.seller.secret_code_created_at': datetime.datetime(2024, 12, 18, 17, 13, 34, 173242, tzinfo=datetime.timezone.utc), 'advertisement.seller.mnemonic_temp': None, 'advertisement.seller.mnemonic': ['never', 'top', 'pet', 'brick', 'leaf', 'talk', 'hero', 'random', 'blood', 'super', 'sibling', 'pulse', 'piano', 'expire', 'poem', 'dove', 'fox', 'unaware', 'yellow', 'crystal'], 'advertisement.seller.email': None, 'advertisement.seller.code': 0, 'advertisement.seller.email_temp': None, 'advertisement.seller.email_code': None, 'advertisement.seller.expiration_time': None, 'advertisement.seller.email_code_created_at': datetime.datetime(2024, 12, 18, 17, 13, 34, 173242, tzinfo=datetime.timezone.utc), 'advertisement.seller.pin_code': [1, 1, 1, 1], 'advertisement.seller.email_verif': True, 'advertisement.seller.secret_key': '6AY2PR3CE5YAM6LCA524QBTQAP3UF4UO', 'advertisement.seller.sec_key_verif': True, 'advertisement.seller.count_entering_pincode': 0, 'advertisement.seller.count_entering_2fa': 0, 'advertisement.seller.reputation_score': 0.0, 'advertisement.seller.kyc_status': 1, 'advertisement.seller.kyc_verified_at': None, 'advertisement.seller.aml_status': 1, 'advertisement.seller.aml_verified_at': None, 'advertisement.seller.is_mediator': False, 'advertisement.seller.is_admin': False, 'advertisement.seller.count_all_deals': 31, 'advertisement.seller.count_completed_deals': 0, 'advertisement.wallet.id': 22, 'advertisement.wallet.user_id': 3, 'advertisement.wallet.address': 'TKmZp7KyBXTcfeCqWCDxESkFa3SDAkBRAA', 'advertisement.wallet.locked_mantissa': 600, 'advertisement.wallet.locked_exponent': -2, 'advertisement.wallet.currency': 4, 'advertisement.wallet.is_active': True, 'buyer.id': 2, 'buyer.first_name': 'Pavlo', 'buyer.telegram_id': 395818114, 'buyer.username': 'pavlohd', 'buyer.photo_url': 'https://t.me/i/userpic/320/SHX-KVsX91RDsoJ-ejBKST1Vnacq-sEfEOEO8OXuF0g.svg', 'buyer.auth_date': datetime.datetime(2024, 12, 4, 17, 9, 22, tzinfo=datetime.timezone.utc), 'buyer.created_at': datetime.datetime(2024, 12, 4, 17, 9, 25, 943975, tzinfo=datetime.timezone.utc), 'buyer.locked': False, 'buyer.is_active': True, 'buyer.secret_code': None, 'buyer.secret_code_created_at': datetime.datetime(2024, 12, 4, 17, 9, 25, 943975, tzinfo=datetime.timezone.utc), 'buyer.mnemonic_temp': None, 'buyer.mnemonic': ['final', 'essay', 'scissors', 'crumble', 'black', 'almost', 'noble', 'drift', 'foster', 'drip', 'fine', 'final', 'silver', 'will', 'crush', 'airport', 'keep', 'question', 'body', 'merge'], 'buyer.email': '[email protected]', 'buyer.code': 0, 'buyer.email_temp': None, 'buyer.email_code': None, 'buyer.expiration_time': None, 'buyer.email_code_created_at': datetime.datetime(2024, 12, 4, 17, 9, 25, 943975, tzinfo=datetime.timezone.utc), 'buyer.pin_code': [1, 1, 1, 1], 'buyer.email_verif': True, 'buyer.secret_key': 'ZSET7FPS6FQOHQ2VQTTATV343JGUFV6B', 'buyer.sec_key_verif': True, 'buyer.count_entering_pincode': 0, 'buyer.count_entering_2fa': 0, 'buyer.reputation_score': 4.0, 'buyer.kyc_status': 1, 'buyer.kyc_verified_at': None, 'buyer.aml_status': 1, 'buyer.aml_verified_at': None, 'buyer.is_mediator': False, 'buyer.is_admin': False, 'buyer.count_all_deals': 6, 'buyer.count_completed_deals': 0, 'chat_id': 31, 'payment_method.bank_payment_method.bank_id': 127656, 'wallet.id': 7, 'wallet.user_id': 2, 'wallet.address': 'TQjcKhhaHw6Zewbh3fxjmMri5tUJLQaFwY', 'wallet.locked_mantissa': 21300, 'wallet.locked_exponent': -2, 'wallet.currency': 4, 'wallet.is_active': True, 'id': 41, 'advertisement': 16, 'buyer': 2, 'amount_mantissa': 2000, 'amount_exponent': -2, 'payment_method': 24, 'order_status': 6, 'wallet': 7, 'price_per_unit_mantissa': 4, 'price_per_unit_exponent': 1, 'is_satisfied': False, 'comment': '', 'created_at': datetime.datetime(2025, 1, 6, 15, 14, 8, 507772, tzinfo=datetime.timezone.utc), 'expires_at': datetime.datetime(2025, 1, 6, 16, 14, 8, 507782, tzinfo=datetime.timezone.utc), 'completed_at': datetime.datetime(2025, 1, 6, 15, 14, 8, 508082, tzinfo=datetime.timezone.utc)}
Actually, it will be awesome if you tell me easy way to serialize complex dict into Table object
@serg-yalosovetsky Piccolo uses Pydantic to serialize data (through create_pydantic_model or Pydantic BaseModel). Can you share your tables schema or more information so I can try to understand your problem correctly?
@sinisaos Of course. Thank you for quick answer)
class Order(Table, tablename="orders"):
id = Serial(primary_key=True, index=True)
advertisement = ForeignKey(references=Advertisement, null=False, default=0, on_delete=OnDelete.set_null)
buyer = ForeignKey(references=Users, null=False, on_delete=OnDelete.set_null)
amount_mantissa = BigInt(default=0, null=False)
amount_exponent = BigInt(default=0, null=False)
payment_method = ForeignKey(references=UserPaymentMethods, null=False, on_delete=OnDelete.set_null)
order_status = ForeignKey(references=OrderStatus, null=False)
wallet = ForeignKey(references=Wallets, null=False)
price_per_unit_mantissa = BigInt(default=0, null=False)
price_per_unit_exponent = BigInt(default=0, null=False)
is_satisfied = Boolean(default=False, null=False)
comment = Text(null=True)
created_at = Timestamptz(default=TimestamptzNow(), null=False)
expires_at = Timestamptz(null=False)
completed_at = Timestamptz(null=True)
class Chat(Table, tablename="chats"):
id = Serial(primary_key=True, index=True)
order = ForeignKey(references=Order, null=False, unique=True)
status = ForeignKey(references=DisputeStatus, null=False)
created_at = Timestamptz(default=TimestamptzNow(), null=False)
basically i just selected order object and advertisement object
await Order.objects(Order.advertisement).get(Order.id == 1)
but now i also need to fetch chat_id for this order
await Order.objects(
Order.advertisement,
Order.id.join_on(Chat.order).id.as_alias("chat_id")
).get(Order.id == 1)
it is work for select, but doesn't work on objects
await Order.select(
Order.advertisement,
Order.id.join_on(Chat.order).id.as_alias("chat_id")
).get(Order.id == 1)
@sinisaos actually, why pydantic? imho it's overcomplicated, attrs/cattrs is much better
@serg-yalosovetsky I think you should use get_related to get the required result. Something like this
order_select = (
await Order.select(
Order.advertisement,
Order.id.join_on(Chat.order).id.as_alias("chat_id"),
Order.created_at,
)
.where(Order.id == 1)
.first()
)
order_object = await Order.objects(Order.advertisement).get(Order.id == 1)
related_object_join_on = await order_object.get_related(
Order.id.join_on(Chat.order)
)
related_object_reverse = await order_object.get_related(Chat.order.reverse())
print(order_select) # {'advertisement': 1, 'chat_id': 1, 'created_at': datetime.datetime(2025, 1, 21, 18, 9, 36, tzinfo=datetime.timezone.utc)}
print(related_object_join_on) # (1,1,'2025-01-21T18:10:16+00:00')
print(related_object_reverse) # (1,1,'2025-01-21T18:10:16+00:00')
I hope that helps.
I saw this code, but i want to create efficient code And so i want to use one request for all stuff I can do this with select statement, but unable to do with objects statement
@serg-yalosovetsky I don't know if this suits your needs, but for single query, I think you can use reverse FK from Chat table. Something like this:
order_object_single_query = await Chat.objects(
Chat.all_related("order"), # use fk column as string
Chat.order.all_related("advertisement"), # use fk column as string
).get(Chat.id == 1)
print(order_object_single_query) # (1,1,'2025-01-21T18:10:16+00:00')
If you use the Piccolo to_dict method, you get a nice nested dict (json)
{
"id":1,
"order":{
"id":1,
"advertisement":{
"id":1,
"name":"Advertisement one"
},
"amount_mantissa":0,
"amount_exponent":0,
"price_per_unit_mantissa":0,
"price_per_unit_exponent":0,
"is_satisfied":false,
"comment":"Text",
"created_at":"2025-01-21T18:09:36+00:00",
"expires_at":"2025-01-21T18:09:36+00:00",
"completed_at":"2025-01-21T18:09:36+00:00"
},
"created_at":"2025-01-21T18:10:16+00:00"
}
Or you can use select query and convert dict result to object.
Or you can try something like this.
order_objects = await Order.objects(
Order.advertisement,
Order.id.join_on(Chat.order),
).get(Order.id == 1)
print(order_objects) # ({'created_at': datetime.datetime(2025, 1, 21, 18, 10, 16, tzinfo=datetime.timezone.utc), 'id': 1, 'order': 1},1,0,0,0,0,False,'Text','2025-01-21T18:09:36+00:00','2025-01-21T18:09:36+00:00','2025-01-21T18:09:36+00:00')
@serg-yalosovetsky I don't know if this suits your needs, but for single query, I think you can use reverse FK from
Chattable. Something like this:order_object_single_query = await Chat.objects( Chat.all_related("order"), # use fk column as string Chat.order.all_related("advertisement"), # use fk column as string ).get(Chat.id == 1) print(order_object_single_query) # (1,1,'2025-01-21T18:10:16+00:00') If you use the Piccolo
to_dictmethod, you get a nice nested dict (json){ "id":1, "order":{ "id":1, "advertisement":{ "id":1, "name":"Advertisement one" }, "amount_mantissa":0, "amount_exponent":0, "price_per_unit_mantissa":0, "price_per_unit_exponent":0, "is_satisfied":false, "comment":"Text", "created_at":"2025-01-21T18:09:36+00:00", "expires_at":"2025-01-21T18:09:36+00:00", "completed_at":"2025-01-21T18:09:36+00:00" }, "created_at":"2025-01-21T18:10:16+00:00" } Or you can use select
queryand convert dict result to object.
it will work only if every order has a chat but i have some order witout chats
Or you can try something like this.
order_objects = await Order.objects( Order.advertisement, Order.id.join_on(Chat.order), ).get(Order.id == 1) print(order_objects) # ({'created_at': datetime.datetime(2025, 1, 21, 18, 10, 16, tzinfo=datetime.timezone.utc), 'id': 1, 'order': 1},1,0,0,0,0,False,'Text','2025-01-21T18:09:36+00:00','2025-01-21T18:09:36+00:00','2025-01-21T18:09:36+00:00')
await Order.objects(Order.id, Order.id.join_on(Chat.order))
---------------------------------------------------------------------------
NotImplementedError Traceback (most recent call last)
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/base.py:256, in Query.querystrings(self)
255 try:
--> 256 return self.postgres_querystrings
257 except NotImplementedError:
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/base.py:235, in Query.postgres_querystrings(self)
233 @property
234 def postgres_querystrings(self) -> t.Sequence[QueryString]:
--> 235 raise NotImplementedError
NotImplementedError:
During handling of the above exception, another exception occurred:
ValueError Traceback (most recent call last)
Cell In[29], line 1
----> 1 await Order.objects(Order.id, Order.id.join_on(Chat.order))
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/methods/objects.py:490, in Objects.run(self, node, in_pool, use_callbacks)
484 async def run(
485 self,
486 node: t.Optional[str] = None,
487 in_pool: bool = True,
488 use_callbacks: bool = True,
489 ) -> t.List[TableInstance]:
--> 490 results = await super().run(node=node, in_pool=in_pool)
492 if use_callbacks:
493 # With callbacks, the user can return any data that they want.
494 # Assume that most of the time they will still return a list of
495 # Table instances.
496 modified: t.List[TableInstance] = (
497 await self.callback_delegate.invoke(
498 results, kind=CallbackType.success
499 )
500 )
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/base.py:192, in Query.run(self, node, in_pool)
189 async def run(
190 self, node: t.Optional[str] = None, in_pool: bool = True
191 ) -> QueryResponseType:
--> 192 return await self._run(node=node, in_pool=in_pool)
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/base.py:171, in Query._run(self, node, in_pool)
168 if isinstance(engine, PostgresEngine):
169 engine = engine.extra_nodes[node]
--> 171 querystrings = self.querystrings
173 if len(querystrings) == 1:
174 results = await engine.run_querystring(
175 querystrings[0], in_pool=in_pool
176 )
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/base.py:258, in Query.querystrings(self)
256 return self.postgres_querystrings
257 except NotImplementedError:
--> 258 return self.default_querystrings
259 elif engine_type == "sqlite":
260 try:
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/methods/objects.py:472, in Objects.default_querystrings(self)
470 select.columns(*fk.all_columns())
471 else:
--> 472 raise ValueError(f"{fk} doesn't seem to be a ForeignKey.")
474 # Make sure that all intermediate objects are fully loaded.
475 for parent_fk in fk._meta.call_chain:
ValueError: "id" SERIAL PRIMARY KEY NOT NULL doesn't seem to be a ForeignKey.
i have very weird problem, and i don't know what to do with it
await Order.select(Order.id, Order.id.join_on(Chat.order).id.as_alias("chat_id"), Order.id.join_on(Chat.order).id.join_on(Dispute.chat).id.as_alias("dispute_id"), Order.id.join_on(Chat.order).id.join_on(Dispute.chat).plaintiff.as_alias("dispute_plaintiff"),)
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/methods/select.py:666, in Select.run(self, node, in_pool, use_callbacks, **kwargs)
659 async def run(
660 self,
661 node: t.Optional[str] = None,
(...)
664 **kwargs,
665 ) -> t.List[t.Dict[str, t.Any]]:
--> 666 results = await super().run(node=node, in_pool=in_pool)
667 if use_callbacks:
668 return await self.callback_delegate.invoke(
669 results, kind=CallbackType.success
670 )
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/base.py:192, in Query.run(self, node, in_pool)
189 async def run(
190 self, node: t.Optional[str] = None, in_pool: bool = True
191 ) -> QueryResponseType:
--> 192 return await self._run(node=node, in_pool=in_pool)
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/base.py:174, in Query._run(self, node, in_pool)
171 querystrings = self.querystrings
173 if len(querystrings) == 1:
--> 174 results = await engine.run_querystring(
175 querystrings[0], in_pool=in_pool
176 )
177 return await self._process_results(results)
178 else:
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/engine/postgres.py:555, in PostgresEngine.run_querystring(self, querystring, in_pool)
553 response = await self._run_in_pool(query, query_args)
554 else:
--> 555 response = await self._run_in_new_connection(query, query_args)
557 if self.log_responses:
558 self.print_response(query_id=query_id, response=response)
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/engine/postgres.py:529, in PostgresEngine._run_in_new_connection(self, query, args)
527 except asyncpg.exceptions.PostgresError as exception:
528 await connection.close()
--> 529 raise exception
531 await connection.close()
532 return results
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/engine/postgres.py:526, in PostgresEngine._run_in_new_connection(self, query, args)
523 connection = await self.get_new_connection()
525 try:
--> 526 results = await connection.fetch(query, *args)
527 except asyncpg.exceptions.PostgresError as exception:
528 await connection.close()
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/asyncpg/connection.py:690, in Connection.fetch(self, query, timeout, record_class, *args)
669 """Run a query and return the results as a list of :class:`Record`.
670
671 :param str query:
(...)
687 Added the *record_class* parameter.
688 """
689 self._check_open()
--> 690 return await self._execute(
691 query,
692 args,
693 0,
694 timeout,
695 record_class=record_class,
696 )
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/asyncpg/connection.py:1864, in Connection._execute(self, query, args, limit, timeout, return_status, ignore_custom_codec, record_class)
1852 async def _execute(
1853 self,
1854 query,
(...)
1861 record_class=None
1862 ):
1863 with self._stmt_exclusive_section:
-> 1864 result, _ = await self.__execute(
1865 query,
1866 args,
1867 limit,
1868 timeout,
1869 return_status=return_status,
1870 record_class=record_class,
1871 ignore_custom_codec=ignore_custom_codec,
1872 )
1873 return result
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/asyncpg/connection.py:1961, in Connection.__execute(self, query, args, limit, timeout, return_status, ignore_custom_codec, record_class)
1953 result, stmt = await self._do_execute(
1954 query,
1955 executor,
(...)
1958 ignore_custom_codec=ignore_custom_codec,
1959 )
1960 else:
-> 1961 result, stmt = await self._do_execute(
1962 query,
1963 executor,
1964 timeout,
1965 record_class=record_class,
1966 ignore_custom_codec=ignore_custom_codec,
1967 )
1968 return result, stmt
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/asyncpg/connection.py:2004, in Connection._do_execute(self, query, executor, timeout, retry, ignore_custom_codec, record_class)
1993 async def _do_execute(
1994 self,
1995 query,
(...)
2001 record_class=None
2002 ):
2003 if timeout is None:
-> 2004 stmt = await self._get_statement(
2005 query,
2006 None,
2007 record_class=record_class,
2008 ignore_custom_codec=ignore_custom_codec,
2009 )
2010 else:
2011 before = time.monotonic()
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/asyncpg/connection.py:432, in Connection._get_statement(self, query, timeout, named, use_cache, ignore_custom_codec, record_class)
429 else:
430 stmt_name = ''
--> 432 statement = await self._protocol.prepare(
433 stmt_name,
434 query,
435 timeout,
436 record_class=record_class,
437 ignore_custom_codec=ignore_custom_codec,
438 )
439 need_reprepare = False
440 types_with_missing_codecs = statement._init_types()
File ~/work_dir/stage-order-management/.venv/lib/python3.12/site-packages/asyncpg/protocol/protocol.pyx:165, in prepare()
DuplicateAliasError: table name "orders$id$chats$id" specified more than once
also it is don't work if i want to fetch only plaintiff_id:
await Order.select(Order.id, Order.id.join_on(Chat.order).id.as_alias("chat_id"), Order.id.join_on(Chat.order).id.join_on(Dispute.chat).plaintiff.as_alias("dispute_id"),)
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/methods/select.py:666, in Select.run(self, node, in_pool, use_callbacks, **kwargs)
659 async def run(
660 self,
661 node: t.Optional[str] = None,
(...)
664 **kwargs,
665 ) -> t.List[t.Dict[str, t.Any]]:
--> 666 results = await super().run(node=node, in_pool=in_pool)
667 if use_callbacks:
668 return await self.callback_delegate.invoke(
669 results, kind=CallbackType.success
670 )
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/base.py:192, in Query.run(self, node, in_pool)
189 async def run(
190 self, node: t.Optional[str] = None, in_pool: bool = True
191 ) -> QueryResponseType:
--> 192 return await self._run(node=node, in_pool=in_pool)
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/query/base.py:174, in Query._run(self, node, in_pool)
171 querystrings = self.querystrings
173 if len(querystrings) == 1:
--> 174 results = await engine.run_querystring(
175 querystrings[0], in_pool=in_pool
176 )
177 return await self._process_results(results)
178 else:
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/engine/postgres.py:555, in PostgresEngine.run_querystring(self, querystring, in_pool)
553 response = await self._run_in_pool(query, query_args)
554 else:
--> 555 response = await self._run_in_new_connection(query, query_args)
557 if self.log_responses:
558 self.print_response(query_id=query_id, response=response)
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/engine/postgres.py:529, in PostgresEngine._run_in_new_connection(self, query, args)
527 except asyncpg.exceptions.PostgresError as exception:
528 await connection.close()
--> 529 raise exception
531 await connection.close()
532 return results
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/piccolo/engine/postgres.py:526, in PostgresEngine._run_in_new_connection(self, query, args)
523 connection = await self.get_new_connection()
525 try:
--> 526 results = await connection.fetch(query, *args)
527 except asyncpg.exceptions.PostgresError as exception:
528 await connection.close()
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/asyncpg/connection.py:690, in Connection.fetch(self, query, timeout, record_class, *args)
669 """Run a query and return the results as a list of :class:`Record`.
670
671 :param str query:
(...)
687 Added the *record_class* parameter.
688 """
689 self._check_open()
--> 690 return await self._execute(
691 query,
692 args,
693 0,
694 timeout,
695 record_class=record_class,
696 )
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/asyncpg/connection.py:1864, in Connection._execute(self, query, args, limit, timeout, return_status, ignore_custom_codec, record_class)
1852 async def _execute(
1853 self,
1854 query,
(...)
1861 record_class=None
1862 ):
1863 with self._stmt_exclusive_section:
-> 1864 result, _ = await self.__execute(
1865 query,
1866 args,
1867 limit,
1868 timeout,
1869 return_status=return_status,
1870 record_class=record_class,
1871 ignore_custom_codec=ignore_custom_codec,
1872 )
1873 return result
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/asyncpg/connection.py:1961, in Connection.__execute(self, query, args, limit, timeout, return_status, ignore_custom_codec, record_class)
1953 result, stmt = await self._do_execute(
1954 query,
1955 executor,
(...)
1958 ignore_custom_codec=ignore_custom_codec,
1959 )
1960 else:
-> 1961 result, stmt = await self._do_execute(
1962 query,
1963 executor,
1964 timeout,
1965 record_class=record_class,
1966 ignore_custom_codec=ignore_custom_codec,
1967 )
1968 return result, stmt
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/asyncpg/connection.py:2004, in Connection._do_execute(self, query, executor, timeout, retry, ignore_custom_codec, record_class)
1993 async def _do_execute(
1994 self,
1995 query,
(...)
2001 record_class=None
2002 ):
2003 if timeout is None:
-> 2004 stmt = await self._get_statement(
2005 query,
2006 None,
2007 record_class=record_class,
2008 ignore_custom_codec=ignore_custom_codec,
2009 )
2010 else:
2011 before = time.monotonic()
File ~/work_dir/order-management/.venv/lib/python3.12/site-packages/asyncpg/connection.py:432, in Connection._get_statement(self, query, timeout, named, use_cache, ignore_custom_codec, record_class)
429 else:
430 stmt_name = ''
--> 432 statement = await self._protocol.prepare(
433 stmt_name,
434 query,
435 timeout,
436 record_class=record_class,
437 ignore_custom_codec=ignore_custom_codec,
438 )
439 need_reprepare = False
440 types_with_missing_codecs = statement._init_types()
File ~/work_dir/stage-order-management/.venv/lib/python3.12/site-packages/asyncpg/protocol/protocol.pyx:165, in prepare()
UndefinedTableError: invalid reference to FROM-clause entry for table "chats"
HINT: Perhaps you meant to reference the table alias "orders$id".
order chat and dispute classes
class Order(Table, tablename="orders"):
id = Serial(primary_key=True, index=True)
advertisement = ForeignKey(references=Advertisement, null=False, default=0, on_delete=OnDelete.set_null)
buyer = ForeignKey(references=Users, null=False, on_delete=OnDelete.set_null)
amount_mantissa = BigInt(default=0, null=False)
amount_exponent = BigInt(default=0, null=False)
payment_method = ForeignKey(references=UserPaymentMethods, null=False, on_delete=OnDelete.set_null)
order_status = ForeignKey(references=OrderStatus, null=False)
wallet = ForeignKey(references=Wallets, null=False)
price_per_unit_mantissa = BigInt(default=0, null=False)
price_per_unit_exponent = BigInt(default=0, null=False)
is_satisfied = Boolean(default=False, null=False)
comment = Text(null=True)
created_at = Timestamptz(default=TimestamptzNow(), null=False)
expires_at = Timestamptz(null=False)
completed_at = Timestamptz(null=True)
class Chat(Table, tablename="chats"):
id = Serial(primary_key=True, index=True)
order = ForeignKey(references=Order, null=False, unique=True)
status = ForeignKey(references=DisputeStatus, null=False)
created_at = Timestamptz(default=TimestamptzNow(), null=False)
class Dispute(Table, tablename="disputes"):
id = Serial(primary_key=True, index=True)
chat = ForeignKey(references=Chat, null=False, unique=True)
plaintiff = ForeignKey(references=Users, null=False)
defendant = ForeignKey(references=Users, null=False)
reason = Varchar(length=500, null=True)
evidence = JSONB(null=True) # Store paths to uploaded evidence files
result = ForeignKey(references=DisputeResult, null=True)
resolution = Varchar(length=500, null=True)
winner = ForeignKey(references=Users, null=True)
mediator = ForeignKey(references=Users, null=True)
status = ForeignKey(references=DisputeStatus, null=False)
created_at = Timestamptz(default=TimestamptzNow(), null=False)
resolved_at = Timestamptz(null=True)
it is work in simplified form
await Order.select(Order.id, Order.id.join_on(Chat.order).id.as_alias("chat_id"), Order.id.join_on(Chat.order).id.join_on(Dispute.chat).id.as_alias("dispute_id"),)
Out[35]:
[{'id': 75, 'chat_id': 54, 'dispute_id': None},
{'id': 89, 'chat_id': 68, 'dispute_id': 9},
{'id': 88, 'chat_id': 67, 'dispute_id': 10},
{'id': 90, 'chat_id': 69, 'dispute_id': None}]
@serg-yalosovetsky In the objects() queries (inside the parentheses), you can only use prefetched objects, not table columns like in the select() query (such as Order.id, etc.) and that ValueError shows.
https://github.com/piccolo-orm/piccolo/blob/02ef5f5c7ae9c1c4c3f20cd588395886dd74499c/piccolo/query/methods/objects.py#L294-L300
I would use select() queries or get_related as in this comment. I know it's less efficient because of the two queries to the database, but it works and is advised in the Piccolo docs. Maybe someone else has some ideas. Cheers.
I have one request with select and select raw, but it's inconvenient