Hacker News Reader

Comment by happytoexplain | Hacker News Reader

As a database novice, what is the difference? I assume one of the two is wastefully fetching all products and then filtering, while the other is omitting the filtered results as part of the fetch, and is therefore more efficient? If so, which is which?

View on HN

Product.objects.filter(color="red")

This is the efficient one, filtering results upstream, via SQL, before they are returned to the client.

Your intuition is correct. Product.objects.filter() will create an optimized query to issue to the database. Meanwhile, Products.objects.all() will retrieve all the records and leaves you with the task of filtering them in-memory.

Correct. I'm not familiar with that ORM, but the first should produce SQL like "SELECT * FROM products WHERE color = 'red'" and only load the records you're interested in, while the second loads everything and throws away probably most.

It's Django (python) if you weren't sure of the name and wanted a look.

The table naming convention from the model name is a bit different, but impossible to know from just that code snippet. Also Django likes to explicitly list every field instead of *

> Also Django likes to explicitly list every field instead of *

Tbf this is a good pattern. It’s rare that you _need_ SELECT *, and if you do, you may not in six months after DDL has added more columns to the table.

Given that one says Products.objects.all(), which one do you think it is?

Good on you for correctly determining the difference, though!

Ah, I see - since I'm not familiar with this language/API, I couldn't really infer what was happening behind the scenes. E.g. '.all()' could be constructing a request, not necessarily executing, and then the 'if' might modify that request intelligently.

You're right, this is Python/Django, where you don't get these semantics (Product is the database model, and it doesn't have access to the "if" to use in the filter). It was more of a general example, though, of "loop vs filtering in the SELECT", so the language doesn't matter much (well, apart from effectively making my point, which it perhaps failed at).

In this ORM, both versions construct without executing until the results are accessed. The list comprehension looping over the results is an access, the "if" happens after that.

The one exception to the obvious "results are accessed" intuition is slices (such as [:3] to mean first 3 elements), which thanks to how python implemented them lets the ORM use them to further modify the query by adding LIMIT instead of executing it.

Reply on news.ycombinator.com