get_or_create has unpredictable behavior with _id fields

[oggie rob]

You can use relfield_id (one underscore) as an argument to create an object, and relfield__id (two underscores) as an argument to filter for an object. Unfortunately, with get_or_create, you don't know which one you will need. This means you can't safely specify the id field directly, and have to look up objects in order to use get_or_create.

[oggie rob]

That should read:

 relfield_id (one underscore) and relfield__id (two underscores)

[Adrian Holovaty]

(Fixed formatting in description.)

[Adrian Holovaty]

Could you explain the problem a bit more? I don't understand... Perhaps include some code examples?

[oggie rob]

Sure, sorry for not adding to begin with.

The problem really is apparent when you have an id for a lookup parameter (e.g. passed as an argument in a view).

>>> from syb.clientdb.models import *
>>> person, created = Person.objects.get_or_create(first_name='Joe', last_name='Blow')
>>> # Imagine we have not looked up the person object yet, and just have the id (as in a view)
>>> pid = person.id
>>> client, created = Client.objects.get_or_create(person__id=pid)
Traceback (most recent call last):
  File "<console>", line 1, in ?
  File "/usr/local/djangolibs/django_trunk/django/db/models/manager.py", line 76, in get_or_create
    return self.get_query_set().get_or_create(**kwargs)
  File "/usr/local/djangolibs/django_trunk/django/db/models/query.py", line 286, in get_or_create
    obj.save()
  File "/usr/local/djangolibs/django_trunk/django/db/models/base.py", line 244, in save
    ','.join(placeholders)), db_values)
  File "/usr/local/djangolibs/django_trunk/django/db/backends/util.py", line 18, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/djangolibs/django_trunk/django/db/backends/postgresql/base.py", line 44, in execute
    return self.cursor.execute(sql, [smart_basestring(p, self.charset) for p in params])
IntegrityError: ERROR:  null value in column "person_id" violates not-null constraint
   ...
>>> client, created = Client.objects.get_or_create(person_id=pid)
Traceback (most recent call last):
  File "<console>", line 1, in ?
  File "/usr/local/djangolibs/django_trunk/django/db/models/manager.py", line 76, in get_or_create
    return self.get_query_set().get_or_create(**kwargs)
  File "/usr/local/djangolibs/django_trunk/django/db/models/query.py", line 281, in get_or_create
    return self.get(**kwargs), False
  File "/usr/local/djangolibs/django_trunk/django/db/models/query.py", line 257, in get
    obj_list = list(clone)
  File "/usr/local/djangolibs/django_trunk/django/db/models/query.py", line 110, in __iter__
    return iter(self._get_data())
  File "/usr/local/djangolibs/django_trunk/django/db/models/query.py", line 477, in _get_data
    self._result_cache = list(self.iterator())
  File "/usr/local/djangolibs/django_trunk/django/db/models/query.py", line 176, in iterator
    select, sql, params = self._get_sql_clause()
  File "/usr/local/djangolibs/django_trunk/django/db/models/query.py", line 491, in _get_sql_clause
    joins2, where2, params2 = self._filters.get_sql(opts)
  File "/usr/local/djangolibs/django_trunk/django/db/models/query.py", line 702, in get_sql
    joins2, where2, params2 = val.get_sql(opts)
  File "/usr/local/djangolibs/django_trunk/django/db/models/query.py", line 753, in get_sql
    return parse_lookup(self.kwargs.items(), opts)
  File "/usr/local/djangolibs/django_trunk/django/db/models/query.py", line 896, in parse_lookup
    joins2, where2, params2 = lookup_inner(path, lookup_type, value, opts, opts.db_table, None)
  File "/usr/local/djangolibs/django_trunk/django/db/models/query.py", line 1014, in lookup_inner
    raise TypeError, "Cannot resolve keyword '%s' into field. Choices are: %s" % (name, ", ".join(choices))
TypeError: Cannot resolve keyword 'person_id' into field. Choices are: person, notes,
   ...

>>> # after restarting shell & getting pid
>>> client = Client.objects.create(person_id=pid)
>>> client2 = Client.objects.get_or_create(person__id=pid)
>>> # so field__id lookup *only* works after you have actually created the row

[oggie rob]

It finally dawned on how this can be done (I guess I had some coders' block earlier). Not concise but it works and doesn't require that lookup.

>>> from syb.clientdb.models import *
>>> person, created = Person.objects.get_or_create(first_name='Jane', last_name='Doe')
>>> pid = person.id
>>> client, created = Client.objects.get_or_create(person__id=pid, defaults={'person_id':pid})
>>> created
True
>>> client, created = Client.objects.get_or_create(person__id=pid, defaults={'person_id':pid})
>>> created
False

[Jacob]

Yeah, there's an important difference between a single underscore and a double; not much we can do about that without adding a bunch of "magic" behavior.