django advanced queryset

django advanced queryset.py

m2m

class Post(models.Model):
    slug = models.SlugField(max_length=200, unique=True)
    likes = models.ManyToManyField(User, related_name='blogpost_like', blank=True)


post = get_object_or_404(Post, slug=slug)
if post.likes.filter(id=request.user.id).exists():
    post.likes.remove(request.user)
else:
    post.likes.add(request.user)
.....................................................................................

Group By >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.

q = Bank.objects.filter(status="no").values("month").annotate(duration_count=Count("duration"))
>> <QuerySet [
    {'month': 'may', 'duration_count': 1305}, 
    {'month': 'apr', 'duration_count': 237}, 
    {'month': 'aug', 'duration_count': 554}, 
    {'month': 'jan', 'duration_count': 132}, ...]

>> q.query.__str__()
>> 
SELECT month, COUNT(duration) AS `duration_count` 
FROM bank
WHERE status = no
GROUP BY month
ORDER BY NULL'


......................................................................................
NULL values handling functions
COALESCE(), IFNULL(), ISNULL(), and NVL()

IFNULL(column_name, 'default_value') --> value
ISNULL(column_name) --> bool
coalesce(<calculated_value>, 'default_value') --> if calculated_value value is NULL else default_value

.......................................................................................
select coalesce(age/id, 0) from bank;

`coalesce(age/id, 0)` --> will return default value if the calculation is null (divide by zero also gives Null)
age/null --> null
null/id --> null
id/null --> null

..........................................................................................

Round(1.732, 2) ---> 1.73

>> select Round(1.732, 2) from bank;

..........................................................................................
from django.db.models import Model, Count, Max, Min

Bank.objects.values("month").annotate(max_age=Max("age"), min_age=Min("age"))

>>
[{'month': 'may', 'max_age': 84, 'min_age': 20}, 
 {'month': 'apr', 'max_age': 80, 'min_age': 20}, 
 {'month': 'aug', 'max_age': 81, 'min_age': 21}, ...]

>>
SELECT month, MAX(age) AS `max_age`, MIN(age) AS `min_age`
FROM `bank` 
GROUP BY month 
ORDER BY NULL

...........................................................................................
Bank.objects.values("month").annotate(
    max_age=Max("age"), min_age=Min("age")
).filter(max_age__gt=70)
>>
SELECT month, MAX(age) AS `max_age`, MIN(age) AS `min_age` 
FROM bank 
GROUP BY month 
HAVING MAX(age) > 70 ORDER BY NULL
............................................................................................

Blog.objects.select_related('author')
>>
SELECT `blog`.`id`, `blog`.`author_id`, `blog`.`title` 
FROM `blog` 
INNER JOIN `user` 
ON (`blog`.`author_id` = `user`.`id`)

.........................................................................................
ANY is SQL
- `ANY` returns the tuple of values


SELECT ProductID, ProductName
FROM Products
WHERE ProductID = ANY(SELECT ProductID FROM OrderDetails WHERE Quantity < 2);

............................................................................................
ALL
- return the list of values if all the entries/ROWs have the `WHERE` condition true

SELECT ProductName
FROM Products
WHERE ProductID = ALL(SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
...............................................................................................