Issue: How to query nodes without children with closure table extension
Author: trifle
State: closed
Created: 2022-10-16 18:36:20

Hi, thanks for making peewee!

This issue is a somewhat exotic question, so feel free to close if it doesn't fit in here.

I'm trying to model a DAG in peewee where children are created to contain the result of some processing of parents.

Let's take the example of video files: The root may be a raw video file, whose encoded versions are children, and each of those may have a thumbnail attached as lvl 2 children (grandchildren of the raw footage).
To find missing entries, I need to query for some nodes of arbitrary position (i.e.: raw footage at level 0, or encoded files at level 1 etc.) that have not yet had any children attached.

The documentation and [blog post](https://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/) nicely describe how to set up a closure table with the playhouse and sqlite extension.

One thing that isn't clear to me at all is how to query for nodes **without children**.

I'll use the example models for categories given in the docs:

```python
class Category(Model):
    name = CharField()
    parent = ForeignKeyField('self', null=True, backref='children')
```

With a normal self-join, I'd use an `outer join` like this:

```sql
select * FROM category AS cat 
LEFT OUTER JOIN category ON (cat.id = category.parent_id) 
WHERE category.id IS NULL;
```

Which translates to peewee as:

```python
cat = Category.alias()
(cat
    .select()
    .join(
        Category,
        on=(cat.id == Category.parent_id),
        join_type=JOIN.LEFT_OUTER)
    .where(Category.id >> None)
)
```


It would be nice to be able to use the tree-specific queries afforded by the closure table to do the same.
What I imagine the closure table syntax to look like is roughly:

```python
(Category
   .select()
   .join(
       CategoryClosure,
       on=(Category.id == CategoryClosure.id),
       join_type=JOIN.LEFT_OUTER)
   .where(Category.id >> None)
   )
```

But that doesn't yield any results (and using .where(CategoryClosure.id >> None) yields all records).

Please excuse if I'm on the entirely wrong track here, as I'm not usually writing SQL.

~~You don't even need the closure for this, you can find all categories with zero children:~~

Sorry, I see you already have a formulation for this:

```python
CA = Category.alias()
(CA
 .select()
 .join(
     Category,
     on=(CA.id == Category.parent_id),
     join_type=JOIN.LEFT_OUTER)
 .where(Category.id >> None))
```

Another:

```python
CA = Category.alias()
q = (Category
     .select()
     .join(CA, JOIN.LEFT_OUTER, on=(CA.parent == Category.id))
     .group_by(Category)
     .having(fn.COUNT(CA.id) == 0))
```

Or use a subquery:

```python
CA = Category.alias()
q = (Category
     .select()
     .where(~fn.EXISTS(
         CA.select().where(CA.parent == Category.id))))
```

To use the closure extension is not necessary here. But if you want to, you can modify the above types of queries to query the closure table to ensure there are no descendants:

```python
q = (Category
     .select()
     .join(CategoryClosure, JOIN.LEFT_OUTER, on=(
         (CategoryClosure.root == Category.id) &
         (CategoryClosure.depth > 0)))
     .where(CategoryClosure.id.is_null()))
```

Or use a subquery:

```python
subq = (CategoryClosure
        .select(SQL('1'))
        .where((CategoryClosure.root == Category.id) &
               (CategoryClosure.depth > 0)))
q = (Category
     .select()
     .where(~fn.EXISTS(subq)))
```

Apologies for not reading your comment more closely originally, I've edited my answer to include closure-table specific implementations of the query.

Thanks Charles, I didn't know about the `root` field!
That was exactly what I was looking for. And the alternative versions of the self-join query are also a useful refresher.

Just to add, it's almost surreal how much work you put into answering questions and issues here. Thanks for caring!