ferronrsmith · October 8, 2023 19:53 · Jun 7, 2020 · Jun 5, 2020
diff --git a/querysets.md b/querysets.md
@@ -1,17 +1,17 @@
 # QuerySet API reference (stock portfolio example)
 An in-depth guide to Django QuerySet (Django 3.0, updated June 2020).
 
-
 ### When QuerySets are evaluated
-Internally, a QuerySet can be constructed, filtered, sliced, and generally passed around without actually hitting the database. No database activity actually occurs until you do something to evaluate the queryset. Examples of that "something" that case a QuerySet to evaluate:
+Internally, a QuerySet can be constructed, filtered, sliced, and generally passed around without actually hitting the database. No database activity actually occurs until you do something to evaluate the queryset. Examples of that "something" that cause a QuerySet to evaluate:
 1. **Iteration**: QuerySet executes its database query the first time you iterate over it
     ```python
     for q in Quote.objects.all():
         print(q.symbol)
     ```
 2. **Slicing**: Slicing an unevaluated QuerySet usually returns another unevaluated QuerySet, but Django will execute the database query if you use the “step” parameter of slice syntax, and will return a list
 
-3. **`repr()`**: A QuerySet is evaluated when you call repr() on it. This is for convenience in the Python interactive interpreter, so you can immediately see your results when using the API interactively.
+3. **`repr()`**: A QuerySet is evaluated wh
+4. en you call repr() on it. This is for convenience in the Python interactive interpreter, so you can immediately see your results when using the API interactively.
 
 4. **`len()`**: A QuerySet is evaluated when you call len() on it. This, as you might expect, returns the length of the result list. Note: use `.count()` instead.
 
@@ -66,7 +66,7 @@ CustomUser.objects.all().db # default
       # exclude trades that are done in 2019 OR with commission >= 0.5
       Quote.objects.exclude(trade_date__year=2019).exclude(commission__lte=0.5)
       ```
-- `annotate()`: Annotates each object with the provided list of query expressions, see Query Expressions section for reference.
+- `annotate()`: Annotates each object with the provided list of query expressions, see Query Expressions section for reference
 - `order_by()` and `reverse()`
 - `disinct()`
 - `values()`and `values_list()`
@@ -184,7 +184,3 @@ Notice in the example above `get()` returns the object, in this case an instance
 - `create()`
 
 ### Aggregation Functions
-
-
-
-
diff --git a/querysets.md b/querysets.md
@@ -0,0 +1,190 @@
+# QuerySet API reference (stock portfolio example)
+An in-depth guide to Django QuerySet (Django 3.0, updated June 2020).
+
+
+### When QuerySets are evaluated
+Internally, a QuerySet can be constructed, filtered, sliced, and generally passed around without actually hitting the database. No database activity actually occurs until you do something to evaluate the queryset. Examples of that "something" that case a QuerySet to evaluate:
+1. **Iteration**: QuerySet executes its database query the first time you iterate over it
+    ```python
+    for q in Quote.objects.all():
+        print(q.symbol)
+    ```
+2. **Slicing**: Slicing an unevaluated QuerySet usually returns another unevaluated QuerySet, but Django will execute the database query if you use the “step” parameter of slice syntax, and will return a list
+
+3. **`repr()`**: A QuerySet is evaluated when you call repr() on it. This is for convenience in the Python interactive interpreter, so you can immediately see your results when using the API interactively.
+
+4. **`len()`**: A QuerySet is evaluated when you call len() on it. This, as you might expect, returns the length of the result list. Note: use `.count()` instead.
+
+5. **list()**: Forces evaluation of QuerySet
+    ```python
+    list(Quote.objects.all())
+    # [<Quote: G3B.SI: 223.00 units @2.66>, <Quote: RTX: 15.00 units @60.53>]
+    ```
+
+6. Testing QuerySet in a boolean context, such as using `bool()`, `or`, `and` or an `if` statement, will cause the query to be executed.
+    ```python
+    # doesn't evaluate:
+    Quote.objects.filter(symbol="RTX")
+    # evaluate (notice the if statement):    
+    if Quote.objects.filter(symbol="RTX"):
+        print("Raytheon in portfolio")
+    ```
+
+---
+
+### QuerySet API
+The QuerySet class has two public attributes you can use for introspection:
+1. `ordered`
+    - True if the QuerySet is ordered — i.e. has an order_by() clause or a default ordering on the model. False otherwise.
+2. `db`
+    - The database that will be used if this query is executed now.
+
+
+```python
+from apps.pipeline.models import Portfolio, Quote
+from apps.users.models import CustomUser
+
+CustomUser.objects.exists() # True
+
+type(CustomUser) # <class 'django.db.models.base.ModelBase'>
+type(CustomUser.objects) # <class 'django.contrib.auth.models.UserManager'>
+type(CustomUser.objects.exists) # <class 'method'>
+type(CustomUser.objects.all()) # <class 'django.db.models.query.QuerySet'>
+
+CustomUser.objects.all().ordered # False
+CustomUser.objects.all().db # default
+```
+
+#### Methods that return new QuerySets
+- `filter()`: Returns a new QuerySet using the lookup parameters (`**kwargs`) specified in the **Field lookups** format. Multiple parameters are joined via `AND` in the underlying SQL statement.
+    - Field lookups are how you specify the meat of an SQL `WHERE` clause. They're specified as keyword arguments to the QuerySet methods `filter()`, `exclude()` and `get()`.
+    - When no lookup type is provided, **it assumes `exact`** for exact match
+- `exclude()`: Returns a new QuerySet containing objects that do not match the lookup parameters (`**kwargs`). Pay attention to the difference:
+    - ```python
+      # exclude trades that are done in 2019 AND with commission >= 0.5  
+      Quote.objects.exclude(trade_date__year=2019, commission__lte=0.5)
+      # exclude trades that are done in 2019 OR with commission >= 0.5
+      Quote.objects.exclude(trade_date__year=2019).exclude(commission__lte=0.5)
+      ```
+- `annotate()`: Annotates each object with the provided list of query expressions, see Query Expressions section for reference.
+- `order_by()` and `reverse()`
+- `disinct()`
+- `values()`and `values_list()`
+- `dates()` and `datetimes()`
+- `none()` and `all()`
+- `union()`, `intersection()`, `difference()`
+- `select_related()` and `prefetch_related()`
+- `extra()`
+- `defer()` and `only()`
+- `using()`
+- `select_for_update()`
+- `raw()`
+
+Some filtering operations using all **Field lookups** in practice:
+
+```python
+# Notice it assumes exact if no lookup type is specified
+Quote.objects.get(id=1)
+# <Quote: G3B.SI: 223.00 units @2.66>
+
+# sql: SELECT ... WHERE id = 1;
+Quote.objects.get(id__exact=1)
+# <Quote: G3B.SI: 223.00 units @2.66>
+
+# sql: SELECT ... WHERE symbol ILIKE 'Rtx';
+Quote.objects.get(symbol__iexact='Rtx')
+
+# case-sensitive containment test
+Quote.objects.get(symbol__contains='SI')
+
+# case-insensitive containment test
+# sql: SELECT ... WHERE symbol ILIKE '%Si%'
+Quote.objects.get(symbol__icontains='Si')
+
+# In a given iterable, often a list, tuple or queryset
+# SELECT ... WHERE id IN ('RTX', 'LMT', 'BA');
+Quote.objects.get(symbol__in=['RTX', 'LMT', 'BA'])
+
+# sql: SELECT * FROM pipeline_quote WHERE portfolio_id IN 
+#   (SELECT id FROM pipeline_portfolio WHERE owner_id=3);
+currentuserpf = Portfolio.objects.filter(owner_id=3)
+Quote.objects.filter(portfolio__in=currentuserpf)
+# <CopyQuerySet [<Quote: RTX: 15.00 units @60.53>, <Quote: G3B.SI: 223.00 units @2.66>]>
+
+# .values('id', 'name') will throw error. Use one field value for filter
+currentuserpf = Portfolio.objects.filter(name__contains='US').values('id')
+Quote.objects.filter(portfolio__in=currentuserpf)
+
+# gt, gte (>=), lt, lte (<=)
+Quote.objects.filter(commission__gte=2)
+
+# startswith, istartwith (case-insensitive), endswith, iendswith
+Quote.objects.filter(comment__startswith='DBS')
+
+# range
+# sql: SELECT ... WHERE trade_date BETWEEN '2020-03-01' and '2020-06-30';
+startdate = datetime.date(2020,3,1)
+enddate = datetime.date(2020,6,30)
+Quote.objects.filter(trade_date__range=(startdate, enddate))
+# <CopyQuerySet [<Quote: RTX: 15.00 units @60.53>, <Quote: G3B.SI: 223.00 units @2.66>]>
+
+# date to cast value of a DateTime field into Date; Doesn't work on DateField 
+# since no casting is required / valid
+Quote.objects.filter(trade_timestamp__date=datetime.date(2020, 5, 28))
+Quote.objects.filter(trade_timestamp__date__gt=datetime.date(2020, 5, 28))
+
+# year (date) and iso_year (datetime), month and day (date and datetime)
+Quote.objects.filter(trade_date__year=2020)
+Quote.objects.filter(trade_date__year__gte=2020)
+Quote.objects.filter(trade_timestamp__iso_year=2020)
+Quote.objects.filter(trade_timestamp__iso_year__gt=2019)
+Quote.objects.filter(trade_date__month__gte=5)
+Quote.objects.filter(trade_date__day__lte=29)
+
+# week returns 1-52 or 53 according to ISO-8601, starts on Monday.
+# week_day ranges from 1 (sunday) to 7 (saturday) 
+# quarter ranges from 1 to 4
+Quote.objects.filter(trade_date__week__gte=10, trade_date__week__lte=52)
+Quote.objects.filter(trade_date__week_day=1)
+Quote.objects.filter(trade_date__quarter=1)
+
+# When USE_TZ is True, fields converted to current timezone before filtering
+Portfolio.objects.filter(last_updated__time__gte=datetime.time(12,5))
+Portfolio.objects.filter(last_updated__hour=12)
+Portfolio.objects.filter(last_updated__hour__gte=11)
+Portfolio.objects.filter(last_updated__minute__gte=9)
+Portfolio.objects.filter(last_updated__second__gte=9)
+
+# isnull takes either True/False, correspond to SQL's IS NULL and IS NOT NULL
+Quote.objects.filter(comment__isnull=True)
+
+# regex (case-sensitive) and iregex (case-insensitive)
+Quote.objects.filter(comment__regex=r'^(DBS|Dbs) +')
+Quote.objects.filter(comment__iregex=r'^(dbs|posb) +')
+```
+
+As a reminder, these are the field lookups that instruct our SQL WHERE clause and they are specified as arguments to the QuerySet methods `filter()`, `exclude()` and `get()` even though examples in the snippet above demonstrate their usage with the `filter()` method.
+
+#### Operators that return new QuerySets
+
+
+#### Methods that do not return QuerySets
+Many QuerySet methods evaluate the QuerySet and then return something _other than_ a QuerySet:
+
+```python
+type(Quote.objects.get(id=1))
+<class 'apps.pipeline.models.Quote'>
+
+type(Quote.objects.filter(id=1))
+<class 'postgres_copy.managers.CopyQuerySet'>
+```
+Notice in the example above `get()` returns the object, in this case an instance of Quote. It doesn't return a QuerySet.
+
+- `get()`
+- `create()`
+
+### Aggregation Functions
+
+
+
+