Public bug reported:
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?
** Affects: glance
Importance: Undecided
Status: New
** Description changed:
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?
+ How can i express such a sql statement in sqlalchemy?
--
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to Glance.
https://bugs.launchpad.net/bugs/1994012
Title:
list method query all image-related tags that were already deleted
Status in Glance:
New
Bug 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 can i express such a sql statement in sqlalchemy?
To manage notifications about this bug go to:
https://bugs.launchpad.net/glance/+bug/1994012/+subscriptions
--
Mailing list: https://launchpad.net/~yahoo-eng-team
Post to : [email protected]
Unsubscribe : https://launchpad.net/~yahoo-eng-team
More help : https://help.launchpad.net/ListHelp
