TIL66. Django ORM : OR, AND, NOT ์—ฐ์‚ฐ

ID์งฑ์žฌยท2021๋…„ 11์›” 1์ผ
0

Django ORM

๋ชฉ๋ก ๋ณด๊ธฐ
2/7
post-thumbnail

๐Ÿ“Œ ์ด ํฌ์ŠคํŒ…์—์„œ๋Š” Django ORM์œผ๋กœ OR, AND, NOT ์—ฐ์‚ฐํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ์ •๋ฆฌํ•˜์˜€์Šต๋‹ˆ๋‹ค.



๐ŸŒˆ OR, AND, NOT ์—ฐ์‚ฐ

๐Ÿ”ฅ OR ์—ฐ์‚ฐํ•˜๋Š” ๋ฐฉ๋ฒ•

๐Ÿ”ฅ AND ์—ฐ์‚ฐํ•˜๋Š” ๋ฐฉ๋ฒ•

๐Ÿ”ฅ NOT ์—ฐ์‚ฐํ•˜๋Š” ๋ฐฉ๋ฒ•



1. OR ์—ฐ์‚ฐํ•˜๋Š” ๋ฐฉ๋ฒ•

๐Ÿค” first_name์ด โ€˜์žฌโ€™๋กœ ์‹œ์ž‘ํ•˜๊ฑฐ๋‚˜ last_name์ด โ€˜์žฅโ€™์œผ๋กœ ์‹œ์ž‘ํ•˜๋Š” ๋ชจ๋“  ์‚ฌ์šฉ์ž๋ฅผ ๊ตฌํ•ด๋ณด์Ÿˆ.

โœ”๏ธ DB์—์„œ ์žฅ์žฌ์›, ๊น€์žฌ์ˆœ, ๋ฐ•์žฌ๋ฆฌ๋‚˜, ์žฅ์ˆœ์ผ, ๊ฐ•์žฌ์˜ค, ์ž„์žฌ์ธ ๋“ฑ์ด ์žˆ์Šต๋‹ˆ๋‹ค. ์„ฑ์ด ์žฅ์œผ๋กœ ์‹œ์ž‘ํ•˜๊ฑฐ๋‚˜ ์ด๋ฆ„์ด ์žฌ๋กœ ์‹œ์ž‘ํ•˜๋Š” ์‚ฌ๋žŒ์„ ๊ตฌํ•˜๋ ค๊ณ  ํ•œ๋‹ค.

โœ”๏ธ ์œ„ ์กฐ๊ฑด์„ SQL ์งˆ์˜๋ฌธ์œผ๋กœ ์ƒ์„ฑํ•˜๋ฉด ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

mysql>>> SELECT * FROM accounts WHERE first_name LIKE '์žฌ%' OR last_name LIKE '์žฅ%';

โœ”๏ธ Django ORM ๋ฌธ๋ฒ•์œผ๋กœ OR์—ฐ์‚ฐ์€ ์•„๋ž˜ 2๊ฐ€์ง€ ๋ฐฉ๋ฒ•์œผ๋กœ query๋ฅผ ์ž‘์„ฑํ•  ์ˆ˜ ์žˆ๋‹ค.

  • ๐Ÿš€ ๋ฐฉ๋ฒ•1: queryset_1 | queryset_2
  • ๐Ÿš€ ๋ฐฉ๋ฒ•2: filter( Q( ์กฐ๊ฑด ) | Q( ์กฐ๊ฑด) )

๐Ÿค” queryset_1 | queryset_2 ๋ฐฉ๋ฒ•

โœ”๏ธ ๋ฐฉ๋ฒ•1์„ ์‚ฌ์šฉํ•ด์„œ ORM์„ ์ž‘์„ฑํ•˜๋ฉด ์•„๋ž˜์™€ ๊ฐ™๋‹ค.

qs1 = User.objects.filter(first_name__startswith='์žฌ') |
      User.objects.filter(last_name__startswith='์žฅ')

๐Ÿค” filter( Q( ์กฐ๊ฑด ) | Q( ์กฐ๊ฑด) ) ๋ฐฉ๋ฒ•

โœ”๏ธ ๋ฐฉ๋ฒ•2์„ ์‚ฌ์šฉํ•ด์„œ ORM์„ ์ž‘์„ฑํ•˜๋ฉด ์•„๋ž˜์™€ ๊ฐ™๋‹ค. ๋” ๊ฐ„๊ฒฐํ•ด์ง„๊ฑธ ๋ณผ ์ˆ˜ ์žˆ๋‹ค.

from django.db.models import Q
qs2 = User.objects.filter(Q(first_name__startswith='์žฌ')|Q(last_name__startswith='์žฅ'))

๐Ÿค” ์ •๋ง ๊ฐ™์€ query๋ฌธ์ธ์ง€ ํ™•์ธํ•˜๊ธฐ

โœ”๏ธ query ๋งค์†Œ๋“œ๋ฅผ ์ด์šฉํ•˜๋ฉด ์‹ค์ œ SQL๋ฌธ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค.

str(qs1.query) == str(qs2.query) # True


2. AND ์—ฐ์‚ฐํ•˜๋Š” ๋ฐฉ๋ฒ•

๐Ÿค” first_name์ด โ€˜์žฌโ€™๋กœ ์‹œ์ž‘ํ•˜๊ณ , last_name์ด โ€˜์žฅโ€™์œผ๋กœ ์‹œ์ž‘ํ•˜๋Š” ๋ชจ๋“  ์‚ฌ์šฉ์ž๋ฅผ ๊ตฌํ•ด๋ณด์Ÿˆ.

โœ”๏ธ AND ์—ฐ์‚ฐ์œผ๋กœ ์—ฌ๋Ÿฌ ์กฐ๊ฑด์„ ๋ชจ๋‘ ๋งŒ์กฑํ•˜๋Š” row์„ ๊ตฌํ•ด์•ผ ํ•˜๋Š” ๊ฒฝ์šฐ ์‚ฌ์šฉํ•œ๋‹ค.

โœ”๏ธ DB์—์„œ ์žฅ์žฌ์›, ์žฅ์žฌ์ˆœ, ์žฅ์žฌ๋ฆฌ๋‚˜, ์žฅ์žฌ์ผ, ์žฅ์žฌ์˜ค, ์žฅ์žฌ์ธ ๋“ฑ์ด ์žˆ์Šต๋‹ˆ๋‹ค. ์„ฑ์ด ์žฅ์œผ๋กœ ์‹œ์ž‘ํ•˜๊ณ  ์ด๋ฆ„์ด ์žฌ๋กœ ์‹œ์ž‘ํ•˜๋Š” ์‚ฌ๋žŒ์„ ๊ตฌํ•˜๋ ค๊ณ  ํ•œ๋‹ค.

SELECT * FROM accounts WHERE first_name LIKE '์žฌ%' AND last_name LIKE '์žฅ%';

โœ”๏ธ Django ORM ๋ฌธ๋ฒ•์œผ๋กœ AND์—ฐ์‚ฐ์€ ์•„๋ž˜ 3๊ฐ€์ง€ ๋ฐฉ๋ฒ•์œผ๋กœ query๋ฅผ ์ž‘์„ฑํ•  ์ˆ˜ ์žˆ๋‹ค.

  • ๐Ÿš€ ๋ฐฉ๋ฒ•1: queryset_1 & queryset_2
  • ๐Ÿš€ ๋ฐฉ๋ฒ•2: filter(<condition_1>, <condition_2>)
  • ๐Ÿš€ ๋ฐฉ๋ฒ•3: filter(Q(<condition_1>) & Q(<condition_2>))

๐Ÿค” queryset_1 & queryset_2 ๋ฐฉ๋ฒ•

โœ”๏ธ & ์—ฐ์‚ฐ์ž๋ฅผ ์ด์šฉํ•˜์—ฌ ์ฟผ๋ฆฌ์…‹์„ ๋ช…์‹œ์ ์œผ๋กœ ๊ฒฐํ•ฉํ•˜๋ฉด ์•„๋ž˜์™€ ๊ฐ™๋‹ค.

qs1 = User.objects.filter(first_name__startswith='์žฌ') & 
       User.objects.filter(last_name__startswith='์žฅ')

๐Ÿค” filter(<condition_1>, <condition_2>) ๋ฐฉ๋ฒ•

โœ”๏ธ filter ๋งค์„œ๋“œ์—์„œ ์—ฌ๋Ÿฌ ์กฐ๊ฑด์„ ๊ฒฐํ•ฉํ•˜๋Š” ๋ฐฉ๋ฒ•์€ and ์กฐ๊ฑด์ด ์ ์šฉ๋œ๋‹ค.

qs2 = User.objects.filter(first_name__startswith='์žฌ', last_name__startswith='์žฅ')

๐Ÿค” filter(Q(<condition_1>) & Q(<condition_2>)) ๋ฐฉ๋ฒ•

โœ”๏ธ ๋ณต์žกํ•  ์งˆ์˜๋ฅผ ์ˆ˜ํ–‰ํ•  ์ˆ˜ ์žˆ๋„๋ก ๋„์™€์ฃผ๋Š” Q ๊ฐ์ฒด๋ฅผ ์ด์šฉํ•ด์—ฌ ์กฐ๊ฑด์„ ๋ช…์‹œํ• ์ˆ˜๋„ ์žˆ๋‹ค.

from django.db.models import Q
qs3 = User.objects.filter(Q(first_name__startswith='์žฌ') &Q(last_name__startswith='์žฅ'))


3. NOT ์—ฐ์‚ฐํ•˜๋Š” ๋ฐฉ๋ฒ•

๐Ÿค” id < 5 ๋ผ๋Š” ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜์ง€ ์•Š๋Š” ๋ชจ๋“  ์‚ฌ์šฉ์ž๋ฅผ ๊ตฌํ•ด๋ณด์ž.

โœ”๏ธ ์ด๋ฅผ ์ˆ˜ํ–‰ํ•˜๋ ค๋ฉด ์กฐ๊ฑด์„ ๋ถ€์ •ํ•˜๋Š” NOT ์—ฐ์‚ฐ์ด ํ•„์š”ํ•˜๋‹ค.

SELECT * FROM accounts WHERE NOT id < 5;

โœ”๏ธ NOT์—ฐ์‚ฐ์„ Django ORM ๋ฌธ๋ฒ•์œผ๋กœ๋Š” ์‚ฌ์šฉํ•˜๋ ค๋ฉด ์•„๋ž˜ 2๊ฐ€์ง€ query๋กœ ์ž‘์„ฑํ•  ์ˆ˜ ์žˆ๋‹ค.

  • ๐Ÿš€ ๋ฐฉ๋ฒ•1: exclude(<condition>)
  • ๐Ÿš€ ๋ฐฉ๋ฒ•2: filter(~Q(<condition>))

๐Ÿค” exclude(<condition>) ๋ฐฉ๋ฒ•

โœ”๏ธ exclude๋Š” ~์„ ์ œ์™ธํ•˜๊ณ ์˜ ์˜๋ฏธ๋กœ, NOT ์—ฐ์‚ฐ์ž์™€ ๊ฐ™์€ ๊ฒฐ๊ณผ๋ฅผ ๋ฐ˜ํ™˜ํ•œ๋‹ค.

qs1 = User.objects.exclude(id__lt=5)

๐Ÿค” filter(~Q(<condition>)) ๋ฐฉ๋ฒ•

โœ”๏ธ Q ๊ฐ์ฒด๋ฅผ ์ด์šฉํ•  ๋•Œ๋Š” ~ ํ‘œ์‹œ๋ฅผ ํ†ตํ•ด NOT์„ ๋ช…์‹œํ•  ์ˆ˜ ์žˆ๋‹ค.

from django.db.models import Q
qs2 = User.objects.filter(~Q(id__lt=5))
profile
Keep Going, Keep Coding!

0๊ฐœ์˜ ๋Œ“๊ธ€