3 ms·

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 fi

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?

sgarland 3y ago
Given that one says Products.objects.all(), which one do you think it is? Good on you for correctly determining the difference, though!
- happytoexplain 3y ago
  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.
  - stavros 3y ago
    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).
  - Izkata 3y ago
    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.
kileywm 3y ago
Product.objects.filter(color="red") This is the efficient one, filtering results upstream, via SQL, before they are returned to the client.
thedanbob 3y ago
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.
- Izkata 3y ago
  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 *
  - sgarland 3y ago
    > 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.
alpha_squared 3y ago
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.

sgarland 3y ago

Given that one says Products.objects.all(), which one do you think it is? Good on you for correctly determining the difference, though!

happytoexplain 3y ago
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.
- stavros 3y ago
  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).
- Izkata 3y ago
  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.

kileywm 3y ago

Product.objects.filter(color="red") This is the efficient one, filtering results upstream, via SQL, before they are returned to the client.

thedanbob 3y ago

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.

Izkata 3y ago
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 *
- sgarland 3y ago
  > 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.

alpha_squared 3y ago

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.