外部キーで参照されていない末端のカテゴリーのみSELECTしたい

Question

カテゴリーとサブカテゴリーを以下のようにして定義しています。
末端のカテゴリーのみSelectで全選択したいのですが、どのようなクエリをかけば実現可能でしょうか。

例

ID	カテゴリー名	親カテゴリーID
1	動物	null
2	哺乳類	1
3	ネコ科	2
4	犬科	2
5	チワワ	4
6	ロシアンブルー	3
7	惑星	null
8	太陽系	7
9	地球	8

↑の場合、外部キーとして参照されていない末端のチワワ、ロシアンブルー、地球だけをSELECTしたいです。

models.py

class Category(models.Model):
    category = models.CharField(verbose_name="Category", max_length=200, unique=True)
    parent_id = models.ForeignKey('self', blank=True, null=True, on_delete=models.CASCADE)
    register_time = models.DateTimeField(auto_now_add=True)

環境はDjango2.1

Answer 1

create table tbl(id int,cate varchar(20),p_cate int null);
insert into tbl values
(1,'動物',null),
(2,'哺乳類',1),
(3,'ネコ科',2),
(4,'犬科',2),
(5,'チワワ',4),
(6,'ロシアンブルー',3),
(7,'惑星',null),
(8,'太陽系',7),
(9,'地球',8);

• 抽出

select t1.cate from tbl as t1
left join tbl as t2 on t1.id=t2.p_cate
where t2.id is null

Comments

[sazi]

こっちの方がコスト低そうですね。

[alpaka]

ご回答ありがとうございます。
いま、DjangoのORMでどのように実現するか検討してます。実現できましたら追記してクローズします。

Answer 2

親カテゴリとして使用されていないデータを抽出すれば良いかと。

select * from テーブル 
where id not in(select 親カテゴリーID from テーブル)
  and 親カテゴリーID is not null

Answer 3

頂いた回答をもとに試行錯誤しておりましたが、クエリセットオブジェクトを取得できずなかなかうまく行きませんでした。(SQL自体はあっていますが、Djangoで応用しようとすると引っかかりました。)

スタックオーバーフローにも似た回答があり、以下のSQLでも良いそうです。
https://stackoverflow.com/questions/21943088/find-lowest-level-in-hierarchy

SELECT id
FROM Category
EXCEPT
SELECT parent_id
FROM Category

上記SQLをDjangoのORM記述にしたところ、実現できました。

models.py

class Category(models.Model):
    category = models.CharField(verbose_name="Category", max_length=200, unique=True)
    parent_id = models.ForeignKey('self', blank=True, null=True, on_delete=models.CASCADE)
    register_time = models.DateTimeField(auto_now_add=True)

    def select_lowest_level(self):
        qs1 = Category.objects.values_list('id')　#すべてのidを取得 <QuerySet [(1,), (2,), (3,), (4,), (5,), (6,), (7,), (8,), (9,)]>
        qs2 = Category.objects.values_list('parent') #使われている親idを取得 <QuerySet [(None,), (1,), (2,), (2,), (4,), (3,),(None,), (7,), (8,)]>
        qs3 = qs1.difference(qs2) #EXCEPTで差分のみ取得 <QuerySet [(3,), (4,), (9,)]>
        id_list = [int(x[0]) for x in qs3] # idのリストに変換　[3, 4, 9]
        cat = Category.objects.filter(id__in=id_list)　#リストのIDに一致するものをSELECT
        return cat # <QuerySet [<Category: ネコ科>, <Category: 犬科>, <Category: 地球>]>