| 2022-10-24 08:03:13 |
zhen |
description |
Image_get_all () uses the left outer join method to query the image_tags table, and the conversion to the sql statement is as follows:
```
...
AS anon_1 LEFT OUTER JOIN image_properties AS image_properties_1 ON anon_1.id = image_properties_1.image_id LEFT OUTER JOIN image_tags AS image_tags_1 ON anon_1.id = image_tags_1.image_id LEFT OUTER JOIN image_locations AS image_locations_1 ON anon_1.id = image_locations_1.image_id ORDER BY anon_1.created_at DESC, anon_1.id DESC
...
```
Since there is no subquery on image_tags in advance to remove logically deleted entries, when there are a large number of logically deleted entries in image_tags (such as frequent update image tag), the query will be very slow.
Can we first make a query on the image_tags table when joining the left outer join? For example:
```
...
AS anon_1 LEFT OUTER JOIN image_properties AS image_properties_1 ON anon_1.id = image_properties_1.image_id LEFT OUTER JOIN (SELECT * FROM image_tags where image_tags.deleted = 0) AS image_tags_1 ON anon_1.id = image_tags_1.image_id LEFT OUTER JOIN image_locations AS image_locations_1 ON anon_1.id = image_locations_1.image_id ORDER BY anon_1.created_at DESC, anon_1.id DESC
...
```
How do you express such a sql statement in sqlalchemy? |
Image_get_all () uses the left outer join method to query the image_tags table, and the conversion to the sql statement is as follows:
```
...
AS anon_1 LEFT OUTER JOIN image_properties AS image_properties_1 ON anon_1.id = image_properties_1.image_id LEFT OUTER JOIN image_tags AS image_tags_1 ON anon_1.id = image_tags_1.image_id LEFT OUTER JOIN image_locations AS image_locations_1 ON anon_1.id = image_locations_1.image_id ORDER BY anon_1.created_at DESC, anon_1.id DESC
...
```
Since there is no subquery on image_tags in advance to remove logically deleted entries, when there are a large number of logically deleted entries in image_tags (such as frequent update image tag), the query will be very slow.
Can we first make a query on the image_tags table when joining the left outer join? For example:
```
...
AS anon_1 LEFT OUTER JOIN image_properties AS image_properties_1 ON anon_1.id = image_properties_1.image_id LEFT OUTER JOIN (SELECT * FROM image_tags where image_tags.deleted = 0) AS image_tags_1 ON anon_1.id = image_tags_1.image_id LEFT OUTER JOIN image_locations AS image_locations_1 ON anon_1.id = image_locations_1.image_id ORDER BY anon_1.created_at DESC, anon_1.id DESC
...
```
How can i express such a sql statement in sqlalchemy? |
|
