Comments (2)
Yeah, it's not super easy with Piccolo at the moment - it abstracts away joins, which helps in most situations, but can be harder in others.
I usually solve these problems using a sub select:
from piccolo.columns import Integer, Varchar, ForeignKey,Text
from piccolo.engine.postgres import PostgresEngine
from piccolo.table import Table, create_db_tables, drop_db_tables
from piccolo.query import SelectRaw
DB = PostgresEngine({'database': 'piccolo_left_join_test'})
class User(Table, db=DB):
id = Integer(primary_key=True)
username = Varchar()
class Posts(Table, db=DB):
id = Integer(primary_key=True)
poster = ForeignKey(references=User)
title = Varchar()
class Comments(Table, db=DB):
id = Integer(primary_key=True)
commenter = ForeignKey(references=User)
content = Text()
async def main():
await drop_db_tables(User, Posts, Comments)
await create_db_tables(User, Posts, Comments)
###########################################################################
# Create some test data
await User(id=1, username='Bob').save()
await User(id=2, username='Alice').save()
await Posts(id=1, poster=1, title='Bob post 1').save()
await Posts(id=2, poster=1, title='Bob post 2').save()
await Posts(id=3, poster=2, title='Alice post 1').save()
await Comments(id=1, commenter=1, content='Bob comment 1').save()
await Comments(id=2, commenter=1, content='Bob comment 2').save()
await Comments(id=3, commenter=2, content='Alice comment 1').save()
###########################################################################
# Example query
response = await Posts.select(
Posts.all_columns(),
SelectRaw(
"(SELECT COUNT(*) FROM comments WHERE commenter = posts.poster) AS comment_count"
)
)
print(response)
if __name__ == '__main__':
import asyncio
asyncio.run(main())
from piccolo.
是的,目前 Piccolo 并不是那么容易 - 它抽象了连接,这在大多数情况下都有帮助,但在其他情况下可能会更困难。
我通常使用子选择来解决这些问题:
from piccolo.columns import Integer, Varchar, ForeignKey,Text from piccolo.engine.postgres import PostgresEngine from piccolo.table import Table, create_db_tables, drop_db_tables from piccolo.query import SelectRaw DB = PostgresEngine({'database': 'piccolo_left_join_test'}) class User(Table, db=DB): id = Integer(primary_key=True) username = Varchar() class Posts(Table, db=DB): id = Integer(primary_key=True) poster = ForeignKey(references=User) title = Varchar() class Comments(Table, db=DB): id = Integer(primary_key=True) commenter = ForeignKey(references=User) content = Text() async def main(): await drop_db_tables(User, Posts, Comments) await create_db_tables(User, Posts, Comments) ########################################################################### # Create some test data await User(id=1, username='Bob').save() await User(id=2, username='Alice').save() await Posts(id=1, poster=1, title='Bob post 1').save() await Posts(id=2, poster=1, title='Bob post 2').save() await Posts(id=3, poster=2, title='Alice post 1').save() await Comments(id=1, commenter=1, content='Bob comment 1').save() await Comments(id=2, commenter=1, content='Bob comment 2').save() await Comments(id=3, commenter=2, content='Alice comment 1').save() ########################################################################### # Example query response = await Posts.select( Posts.all_columns(), SelectRaw( "(SELECT COUNT(*) FROM comments WHERE commenter = posts.poster) AS comment_count" ) ) print(response) if __name__ == '__main__': import asyncio asyncio.run(main())
Thanks for the reply, I think I know how I should encapsulate it
from piccolo.
Related Issues (20)
- Add basic math functions
- Improve array serialisation in `get_sql_value`
- Replace deprecated `@abstractproperty`
- Add `not_any` method to `Array`
- Add `Concat` function
- Replace deprecated `startup` and `shutdown` events in asgi templates HOT 1
- Document how to achieve a `OneToOneField` in Piccolo
- Use `TableTest` to cut down boiler plate in tests
- Be able to hardcode migrations as fake
- Reverse relations fetching HOT 11
- Fix typo in `Email` docstring
- Improve `LazyTableReference`
- Unable to import Max, Sum in piccolo[postgres]==0.113.* HOT 4
- is there a debug flag echo raw sql ? HOT 1
- Compound unique indexes HOT 2
- M2M without on_conflict HOT 2
- Joins HOT 2
- Fix docs for `update_password`
- Better error messages for min password length
- Table names compatibility HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from piccolo.