4 ms·

Your first issue is incorrect. You can easily accomplish the query with one sql hit. The extra() command would work if your foreignkey was defined in the commen

by rmg 18y ago

Your first issue is incorrect. You can easily accomplish the query with one sql hit. The extra() command would work if your foreignkey was defined in the comments model.

mdasen 18y ago
Nope. extra() doesn't allow one to do that. extra() can be used to populate an attribute. So, say I wanted to do a comment count I could say Article.objects.all().extra(select={'comment_count': 'SELECT count(*) FROM blog_comment WHERE blog_comment.article_id = blog_article.id'}) But I can't populate a list of comments objects onto it. I've talked to Malcom about it (who wrote the QuerySet Refactor branch - or at least a substantial portion) and it's a known deficiency. Basically, the issue is two-fold. First, no one has stepped up to write the code that would make select_related() work in that fashion. Second, people want the implementation to disallow certain bad situations. The first part is self-explanatory: select_related() should be enhanced to support that, but someone needs to write the code. The second part isn't as much, but it's more interesting. Let's say you execute this query: SELECT * FROM articles LEFT OUTER JOIN comments ON comments.article_id = articles.id How many rows will you get from that? You'll get somewhere in the vicinity of the number of comments (adding a row for any article without comments). Suffice it to say, the results set grows linearly in proportion to the number of articles and comments. Now let's say we have this: SELECT * FROM articles LEFT OUTER JOIN comments ON comments.article_id = articles.id LEFT OUTER JOIN votes ON votes.article_id = articles.id So, we have our article with comments, but also votes now. So, let's say we want to get just one article that has 100 comments and 200 votes. How many lines will that return? In the original SQL query, we would have seen 100 lines (one for each comment) which would have been manageable. Here, we get 100 * 200 lines back. That's 20,000 rows to parse to build a single Article object! So, the Django folk aren't into letting you just hang yourself out to dry like that. There have been proposals to limit it in ways that wouldn't let you do that, but it's really just something genuinely missing and there isn't a way to do it other than running looped queries or doing something ugly. extra() doesn't do the same thing as select_related(). I'm not saying that doing ORM over multi-valued relationships is easy or that you can't execute queries that are really bad doing them, but it still means that there isn't support for a pretty basic function.
- rmg 18y ago
  Wow, that's an excellent response. You're right. I can't think of any means of easily accomplishing that - if at all; which I find odd, because I would think that would occur at least a few times.

by rmg 18y ago

Your first issue is incorrect. You can easily accomplish the query with one sql hit. The extra() command would work if your foreignkey was defined in the comments model.

mdasen 18y ago

Nope. extra() doesn't allow one to do that. extra() can be used to populate an attribute. So, say I wanted to do a comment count I could say Article.objects.all().extra(select={'comment_count': 'SELECT count(*) FROM blog_comment WHERE blog_comment.article_id = blog_article.id'}) But I can't populate a list of comments objects onto it. I've talked to Malcom about it (who wrote the QuerySet Refactor branch - or at least a substantial portion) and it's a known deficiency. Basically, the issue is two-fold. First, no one has stepped up to write the code that would make select_related() work in that fashion. Second, people want the implementation to disallow certain bad situations. The first part is self-explanatory: select_related() should be enhanced to support that, but someone needs to write the code. The second part isn't as much, but it's more interesting. Let's say you execute this query: SELECT * FROM articles LEFT OUTER JOIN comments ON comments.article_id = articles.id How many rows will you get from that? You'll get somewhere in the vicinity of the number of comments (adding a row for any article without comments). Suffice it to say, the results set grows linearly in proportion to the number of articles and comments. Now let's say we have this: SELECT * FROM articles LEFT OUTER JOIN comments ON comments.article_id = articles.id LEFT OUTER JOIN votes ON votes.article_id = articles.id So, we have our article with comments, but also votes now. So, let's say we want to get just one article that has 100 comments and 200 votes. How many lines will that return? In the original SQL query, we would have seen 100 lines (one for each comment) which would have been manageable. Here, we get 100 * 200 lines back. That's 20,000 rows to parse to build a single Article object! So, the Django folk aren't into letting you just hang yourself out to dry like that. There have been proposals to limit it in ways that wouldn't let you do that, but it's really just something genuinely missing and there isn't a way to do it other than running looped queries or doing something ugly. extra() doesn't do the same thing as select_related(). I'm not saying that doing ORM over multi-valued relationships is easy or that you can't execute queries that are really bad doing them, but it still means that there isn't support for a pretty basic function.

rmg 18y ago
Wow, that's an excellent response. You're right. I can't think of any means of easily accomplishing that - if at all; which I find odd, because I would think that would occur at least a few times.