# One to Many Relationship
### OR operation in ORM
> Use Q
```shell
In [1]: from django.db.models import Q
In [2]: User.objects.filter(Q(age=30) | Q(last_name='김'
...: )).count()
Out[2]: 25
```
### QuerySet
```shell
In [4]: User.objects.all()
Out[4]: , , , , , , , , , , , , , , , , , , , , '...(remaining elements truncated)...']>
In [5]: type(User.objects.all())
Out[5]: django.db.models.query.QuerySet
```
* When querying (method)
* **Lookup**
* `get()`
* Returns the **object** matching the given lookup parameters
* Returns exactly one or Error occurs
* ex) **RUD (Read / Update / Delete)**
* `filter()`
* Returns a new **QuerySet** containing objects that match the given lookup parameters.
* (if none, returns empty QuerySet)
* ex) **Search**
* `AND`
* `method chaining`
* filter. filter. ....
* `OR`
* `Q Object`
* (Q ( ) | Q ( ) )
* `LIKE`
* ex) age\_\_lte
* ex) name\_\_startswith
* `exclude()`
* Returns a new QuerySet containing objects that do not match the given lookup parameters.
\
ex)
```shell
In [1]: Article.objects.all()
Out[1]: SELECT "articles_article"."id",
"articles_article"."title",
"articles_article"."content"
FROM "articles_article"
LIMIT 21
Execution time: 0.000412s [Database: default]
In [2]: Article.objects.create(title='1st post',content='haha')
INSERT INTO "articles_article" ("title", "content")
VALUES ('1st post', 'haha')
Execution time: 0.024278s [Database: default]
Out[2]:
```
\
### Aggregation
* Django query for individual Object CRUD
* Used when you want to see QuerySet as combined results
* ex)
```python
# Average price across all books.
>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': 34.35}
# Max price across all books.
>>> from django.db.models import Max
>>> Book.objects.all().aggregate(Max('price'))
{'price__max': Decimal('81.20')}
# Difference between the highest priced book and the average price of all books.
>>> from django.db.models import FloatField
>>> Book.objects.aggregate(
... price_diff=Max('price', output_field=FloatField()) - Avg('price'))
{'price_diff': 46.85}
# All the following queries involve traversing the Book<->Publisher
# foreign key relationship backwards.
# Each publisher, each with a count of books as a "num_books" attribute.
>>> from django.db.models import Count
>>> pubs = Publisher.objects.annotate(num_books=Count('book'))
>>> pubs
, , ...]>
>>> pubs[0].num_books
73
# Each publisher, with a separate count of books with a rating above and below 5
>>> from django.db.models import Q
>>> above_5 = Count('book', filter=Q(book__rating__gt=5))
>>> below_5 = Count('book', filter=Q(book__rating__lte=5))
>>> pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)
>>> pubs[0].above_5
23
>>> pubs[0].below_5
12
# The top 5 publishers, in order by number of books.
>>> pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]
>>> pubs[0].num_books
1323
```
### Annotate
ex) COUNT( )
```sql
SELECT country, COUNT(country) FROM countries;
```
* Manipulate necessary values from results fetched from DB
\
## 1:N (one to many)
* *1 has many N*
* *N must belong to 1*
* (Therefore cascading is possible)
* ex)
* Article has many Comments
* Comment belongs to Article
* `Foreign Key` is given to **N**
```python
from django.db import models
# 1
class Reporter(models.Model):
username = models.CharField(max_length=10)
# N
class Article(models.Model):
title = models.CharField(max_length=10)
content = models.TextField()
reporter = models.ForeignKey(Reporter, on_delete=models.CASCADE)
#reporter is a name we created, but it's best practice to match the model name!
```
* A `reporter_id` column is added to the `articles_article` table.
* In the case of `reporter`, you can fetch N items (QuerySet) with `article_set`.
* In the case of `article`, you can fetch 1 corresponding object with `reporter`.
* `on_delete` : When the referenced target is deleted
* `CASCADE`
* When the corresponding object(`reporter`) is deleted, all referencing objects(`article`) are also deleted
* `PROTECT`
* Deletion is prohibited if referencing objects(`article`) exist
* `SET_NULL`
* Replace with **NULL** value
* Cannot be used if **NOT NULL** option exists
* `SET_DEFAULT`
* Reference the default value(`article`)
```sql
-- sql
CREATE TABLE "aricles_article" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(10) NOT NULL,
"content" TEXT NOT NULL,
"reporter_id" INTEGER NOT NULL REFERENCES "artices_reporter" ("id") DEFERRABLE INITIALLY DEFERRED
);
```
## Basic Queries
### 1. Preparation
```python
Reporter.objects.create(username='요트맨')
Reporter.objects.create(username='chloe')
Reporter.objects.create(username='camila')
Reporter.objects.create(username='bella')
r1 = Reporter.objects.get(pk=1)
```
### 2. Create article (N)
```shell
In [3]: article = Article()
In [4]: article.title = '제목1'
In [5]: article.content = '내용1'
In [6]: r1 = Reporter.objects.get(pk=1)
In [7]: article.reporter = r1
# reporter_id stores numbers(INTEGER)
# article.reporter_id = 1
In [8]: article.save()
In [9]: article
Out[9]:
In [10]: article.reporter
Out[10]:
In [11]: article.reporter.username
Out[11]: '요트맨'
```
```python
a2 = Article.objects.create(title='제목2', conent='내용2', reporter=r1)
```
### 3. Using 1:N Relationship
```python
# 1. Article's author
a2 = Article.objects.get(pk=2)
a2.reporter
# 2. Article's author username
a2.reporter.username
# 3. Article's author id
a2.reporter.id
a2.reporter_id
# 4. Author(1)'s articles
r1 = Reporter.objects.get(pk=1)
r1.article_set.all()
# -> ]
# 5. All articles written by the author of article #1
a1 = Article.objects.get(pk=1)
a1.reporter.article_set.all()
```
* `article_set`
\
### Comment exercise
```shell
In [3]: comment = Comment()
In [4]: comment
Out[4]:
In [5]: comment.content = 'Comment for Post #1'
In [6]: comment
Out[6]:
In [7]: comment.article_id
In [8]: comment.article_id =1
In [9]: comment
Out[9]:
```
#### Important Note
```shell
In [11]: comment.article_pk
---------------------------------------------------------------------------
AttributeError Traceback (most recent call last)
in
----> 1 comment.article_pk
AttributeError: 'Comment' object has no attribute 'article_pk'
```
* It must be `article_id`
* Aliases cannot be used!
```shell
In [12]: comment.article_id
Out[12]: 1
In [13]: comment.article
SELECT "articles_article"."id",
"articles_article"."title",
"articles_article"."content"
FROM "articles_article"
WHERE "articles_article"."id" = 1
LIMIT 21
Execution time: 0.000417s [Database: default]
Out[13]:
```
```shell
In [16]: article = Article.objects.first()
SELECT "articles_article"."id",
"articles_article"."title",
"articles_article"."content"
FROM "articles_article"
ORDER BY "articles_article"."id" ASC
LIMIT 1
Execution time: 0.000373s [Database: default]
In [17]: article.comment_set
Out[17]: .RelatedManager at 0x7f9603eb1ac8>
In [18]: article.comment_set.all()
Out[18]: SELECT "articles_comment"."id",
"articles_comment"."content",
"articles_comment"."article_id"
FROM "articles_comment"
WHERE "articles_comment"."article_id" = 1
LIMIT 21
Execution time: 0.000308s [Database: default]
]>
```
* To avoid calling with the default **related\_name** `.comment_set`, modify `models.py`
```python
class Comment(models.Model):
content = models.TextField()
article = models.ForeignKey(Article, on_delete=models.CASCADE, related_name='comments')
# pointing to Article among models
def __str__(self):
return f'Comment #{self.pk} for Post #{self.article_id}'
```
* Set `related_name`= 'comments'
* However, since this is just changing an option, **migration** is not needed!
* After modification, can be called with `comments`
```shell
In [2]: comment = Comment.objects.first()
SELECT "articles_comment"."id",
"articles_comment"."content",
"articles_comment"."article_id"
FROM "articles_comment"
ORDER BY "articles_comment"."id" ASC
LIMIT 1
Execution time: 0.000873s [Database: default]
In [3]: article = comment.article
SELECT "articles_article"."id",
"articles_article"."title",
"articles_article"."content"
FROM "articles_article"
WHERE "articles_article"."id" = 1
LIMIT 21
Execution time: 0.000353s [Database: default]
In [4]: article
Out[4]:
In [5]: comment
Out[5]:
In [6]: comment.article
Out[6]:
In [7]: article.comments
Out[7]: .RelatedManager at 0x7f4a84996320>
In [8]: article.comments.all()
Out[8]: SELECT "articles_comment"."id",
"articles_comment"."content",
"articles_comment"."article_id"
FROM "articles_comment"
WHERE "articles_comment"."article_id" = 1
LIMIT 21
Execution time: 0.000374s [Database: default]
]>
```
\
## Data Seeding
### CSV -> DB
```sql
sqlite> .mode csv
sqlite> .import users.csv users_user
```
```sql
sqlite> select count(*) from users_user;
100
sqlite> .schema users_user
CREATE TABLE IF NOT EXISTS "users_user" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "first_name" varchar(20) NOT NULL, "last_name" varchar(20) NOT NULL, "age" integer NOT NULL, "country" varchar(20) NOT NULL, "phone" varchar(20) NOT NULL, "balance" integer NOT NULL);
sqlite> .headers on
sqlite> .schema users_user
CREATE TABLE IF NOT EXISTS "users_user" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "first_name" varchar(20) NOT NULL, "last_name" varchar(20) NOT NULL, "age" integer NOT NULL, "country" varchar(20) NOT NULL, "phone" varchar(20) NOT NULL, "balance" integer NOT NULL);
sqlite> select*from users_user limit 10;
id,first_name,last_name,age,country,phone,balance
1,"정호","유",40,"전라북도",016-7280-2855,370
2,"경희","이",36,"경상남도",011-9854-5133,5900
3,"정자","구",37,"전라남도",011-4177-8170,3100
4,"미경","장",40,"충청남도",011-9079-4419,250000
5,"영환","차",30,"충청북도",011-2921-4284,220
6,"서준","이",26,"충청북도",02-8601-7361,530
7,"주원","민",18,"경기도",011-2525-1976,390
8,"예진","김",33,"충청북도",010-5123-9107,3700
9,"서현","김",23,"제주특별자치도",016-6839-1106,43000
10,"서윤","오",22,"충청남도",011-9693-6452,49000
```
\
## Data Integrity
* the maintenance of, and the assurance of the accuracy and consistency of data over its entire life-cycle
* a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data
### Entity Integrity
* defines each row to be unique within its table.
* No two rows can be the same.
* To achieve this, a `primary key` can be defined.
* The primary key field contains a unique identifier – no two rows can contain the same unique identifier.
### Referential Integrity
* concerned with relationships.
* When two or more tables have a relationship, we have to ensure that the foreign key value matches the primary key value at all times.
* We don't want to have a situation where a foreign key value has no matching primary key value in the primary table.
* This would result in an orphaned record.
So referential integrity will prevent users from:
* Adding records to a related table if there is no associated record in the primary table.
* Changing values in a primary table that result in orphaned records in a related table.
* Deleting records from a primary table if there are matching related records.
### Domain Integrity
* concerns the validity of entries for a given column.
* Selecting the appropriate data type for a column is the first step in maintaining domain integrity.
* Other steps could include, setting up appropriate constraints and rules to define the data format and/or restricting the range of possible values.
### User-Defined Integrity
* allows the user to apply business rules to the database that aren't covered by any of the other three data integrity types.
\
### Django settings.py
For reference
\
*Update, delete can be applied to individual objects and querysets*
\
## Exercises
### Preparation
> Create `onetomany` app
```python
# models.py
class User(models.Model):
username = models.CharField(max_length=10)
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
user = models.ForeignKey(User, on_delete=models.CASCADE)
class Comment(models.Model):
content = models.TextField()
article = models.ForeignKey(Article, on_delete=models.CASCADE)
user = models.ForeignKey(User, on_delete=models.CASCADE)
```
```python
from onetomany.models import User, Article, Comment
# objects
u1 = User.objects.create(username='Kim')
u2 = User.objects.create(username='Lee')
a1 = Article.objects.create(title='1글', user=u1)
a2 = Article.objects.create(title='2글', user=u2)
a3 = Article.objects.create(title='3글', user=u2)
a4 = Article.objects.create(title='4글', user=u2)
c1 = Comment.objects.create(content='1글1댓', article=a1, user=u2)
c2 = Comment.objects.create(content='1글2댓', article=a1, user=u2)
c3 = Comment.objects.create(content='2글1댓', article=a2, user=u1)
c4 = Comment.objects.create(content='4글1댓', article=a4, user=u1)
c5 = Comment.objects.create(content='3글1댓', article=a3, user=u2)
c6 = Comment.objects.create(content='3글2댓', article=a3, user=u1)
```
### Problems
1. Articles written by user #1
```python
u1.article_set.all()
```
2. Print all content of comments written by user #2
```python
for comment in u2.comment_set.all():
print(comment.content)
```
3. Print all content of comments written on article #3
```python
for comment in a3.comment_set.all():
print(comment.content)
```
```html
{% for comment in article.comment_set.all %}
{{ comment.content }}
{% endfor %}
```
4. Posts with title "1글"
```python
Article.objects.filter(title='1글')
```
5. Posts containing the word "글"
```python
Article.objects.filter(title__contains='글')
```
6. Comments(N) where the corresponding article(1) title is "1글"
```python
Comment.objects.filter(article__title='1글')
print(Comment.objects.filter(article__title='1글').query)
```
* Filtering by 1's column in 1:N relationship
```sql
SELECT "onetomany_comment"."id", "onetomany_comment"."content", "onetomany_comment"."article_id", "onetomany_comment"."user_id" FROM "onetomany_comment" INNER JOIN "onetomany_article" ON ("onetomany_comment"."article_id" = "onetomany_article"."id") WHERE "onetomany_article"."title" = 1글
```
\
`+`
### Open `sqlite` with Django handling
```bash
python manage.py dbshell
```
### Option to directly show ORM query in Shell
```shell
python manage.py shell_plus --print-sql
```
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://chloe-codes1.gitbook.io/til/db/db-101/03_one_to_many_relationship.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.