Reverse on_join when one to one Foreign Key for objects interface

[serg-yalosovetsky]

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': 'pd@techwhirl.io',
'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)}

[sinisaos]

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?

[serg-yalosovetsky]

@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)

[serg-yalosovetsky]

@sinisaos
actually, why pydantic?
imho it's overcomplicated, attrs/cattrs is much better

[sinisaos]

@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.

[serg-yalosovetsky]

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

[sinisaos]

@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.

[sinisaos]

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]

@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.

it will work only if every order has a chat
but i have some order witout chats

[serg-yalosovetsky]

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.

[serg-yalosovetsky]

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}]

[sinisaos]

@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.

piccolo/piccolo/query/methods/objects.py

Lines 294 to 300 in 02ef5f5

	class Objects(
	Query[TableInstance, t.List[TableInstance]], t.Generic[TableInstance]
	):
	"""
	Almost identical to select, except you have to select all fields, and
	table instances are returned, rather than just data.
	"""

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.

[serg-yalosovetsky]

I have one request with select and select raw, but it's inconvenient