Issue: Filter does not behave like where with NULL ForeignKey
Author: yohanboniface
State: closed
Created: 2015-11-06 16:09:06

``` python
import peewee


db = peewee.SqliteDatabase(':memory:')


class One(peewee.Model):

    name = peewee.CharField()

    class Meta:
        database = db


class Two(peewee.Model):

    name = peewee.CharField()
    one = peewee.ForeignKeyField(One, null=True)

    class Meta:
        database = db


if __name__ == '__main__':

    One.create_table()
    Two.create_table()
    Two.create(name='no one')
    # This works.
    # SQL: SELECT "t1"."id", "t1"."name", "t1"."one_id" FROM "two" AS t1 WHERE ("t1"."one_id" IS ?) [None]
    assert Two.select().where(Two.one == None).count() == 1
    # This does not.
    # SQL: SELECT "t1"."id", "t1"."name", "t1"."one_id" FROM "two" AS t1 INNER JOIN "one" AS t2 ON ("t1"."one_id" = "t2"."id") WHERE ("t1"."one_id" = ?) [None]
    assert Two.select().filter(one=None).count() == 1
```

```
$ python test.py
Traceback (most recent call last):
  File "peewee_fk_none.py", line 31, in <module>
    assert Two.select().filter(one=None).count() == 1
AssertionError
```


You need to use `.filter(one__is=None)`


To be more specific, the filter API has been deprecated since 2.0.0. Why are you using it?


> You need to use .filter(one__is=None)

Ah, ok, thanks.

> To be more specific, the filter API has been deprecated since 2.0.0. Why are you using it?
- Easier to build dynamically (for example from query parameters (?xxx=yyy&aaa=bbb), or any situation where you need to map from key/value).
- More concise (we need to fit in 80 lines, no? ;) ).
- more consistant: `Class.field != value` vs `Class.field.contains(value)`
- feels pythonic at first view, but then we hit the "or", "not", "in"… limit, so a bit frustrating system at use _imho_
- my brain refuses to remember `<<` and `>>` own operation ;)

Can you explain (or point me to an explanation about) why it is deprecated? :)


Well, you don't have to use `<<` and `>>`. You can use:

``` python
Class.field.in_()
Class.field.is_null(True)
Class.field.is_null(False)
```

As to being concise, the way to format your code is up to you of course, but I never have issues with 80 character lines, e.g.

``` python
# Get blogs tagged with "python".
python_blogs = (BlogEntry
                .select()
                .join(BlogEntryTags)
                .join(Tag)
                .where(
                    (BlogEntry.is_published == True) &
                    (Tag.tag == 'python'))
                .order_by(BlogEntry.timestamp.desc()))
```

---

The APIs are deprecated because they are not good APIs. Keyword arguments do not preserve ordering, which can be important when constructing where clauses that will hit an index. Keyword arguments necessitate hacks like `DQ(foo__bar='whatev') | DQ(whatever='nug')`. The set of operators is not nearly as rich. You cannot easily do things like express SQL aggregations, function calls, etc.


> Keyword arguments do not preserve ordering, which can be important when constructing where clauses that will hit an index.

Sounds like a strong argument.
Thanks your answer. :)


What about adding a [DeprecationWarning](https://docs.python.org/2/library/exceptions.html#exceptions.DeprecationWarning) when the function is called then?


> To be more specific, the filter API has been deprecated since 2.0.0.

Does that means `get_or_create` is deprecated too, given that it relies on `filter`? If yes, is there a recommended way (bonus: that allows using HStore fields)?


`get_or_create()` is not recommended, but is left in place because a lot of people have come to rely on it. I never use it myself, preferring to code things up explicitly:

``` python
with db.atomic():
    try:
        user = User.create(username='whatever')  # unique constraint on username
    except IntegrityError:
        user = User.get(User.username == 'whatever')
```


Thanks for the suggestion, that's indeed a nice pattern, but that's a `create_or_get`, which works only when you have a DB constraint to rely on. Anyway, I'll will dig this way. Thanks!