Proposal: let user choose individual fields to select

[Robin Munn]

Someone's been asking on IRC how to do "SELECT DISTINCT field FROM
table", and I was quite surprised to find no easy way to do it, since
function_get_sql_clause() by default returns all fields from an
object.

After thinking it over for a few minutes, I propose the following
change: let function_get_sql_clause() grow a "fields" kwarg that would
be a list of the fields to return in the SELECT. Obviously since we're
not returning all the table's fields, we won't be able to populate all
the fields of the resulting model instances; but the user will know
this and be warned that if he attempts to read a field that wasn't
populated, he'll get None. And the save() methods should probably be
disabled on such instances, maybe by setting _pre_save() to a function
that throws an appropriate exception. Any other update methods we'd
have to disable?

Examples are good. Say you want to fetch a list of years with blog
entries to write your archive pages. Here's what you'd currently have
to do:

from django.core import db
cursor = db.db.cursor()
cursor.execute('SELECT DISTINCT year FROM blog ORDER BY year ASC')
year_list = cursor.fetchall()
cursor.close()

And I'm proposing that the following syntax should be possible instead:

year_list = blog.get_list(fields=['year'], distinct=True, order_by='year')

Likewise, to get years and months, instead of

... setup ...
cursor.execute('SELECT DISTINCT year, month FROM blog ORDER BY year
ASC, month ASC')
archive_titles = cursor.fetchall()
... teardown ...

One would be able to do:

archive_titles = blog.get_list(fields=['year', 'month'],
distinct=True, order_by=('year', 'month'))


The advantage of this syntax is the obvious clarity. The disadvantage
is that now we have model instances that don't have all their fields
populated, and the end user needs to be aware that what he's been
handed back are READ-ONLY instances. Would that result in confusion?
Is the clarity of the syntax for getting distinct values worth the
confusion? I'm not sure; that's why I'm making this proposal on the
list instead of spending the time to write a patch. I think a patch
would be fairly simple, but it could potentially have far-reaching
effects. What do y'all think of this one?

[Adrian Holovaty]

On 7/26/05, Robin Munn <robin...@gmail.com> wrote:
> from django.core import db
> cursor = db.db.cursor()
> cursor.execute('SELECT DISTINCT year FROM blog ORDER BY year ASC')
> year_list = cursor.fetchall()
> cursor.close()

Actually, for that, you could do this:

blogs.get_pub_date_list('year')

Each DateTimeField FOO automatically creates a module-level
get_FOO_list() function, which is passed either "year", "month" or
"day". This is a convenience thing, because it's a common case.

But, aside from that special case, I agree that there should be a way
of executing SQL on particular fields. Jacob and I have tossed around
the following solution in the past, but it never got past the idea
stage because we were busy with other stuff:

>>> blogs.get_dicts(fields=['headline', 'author'])
[{'headline': 'Foo', 'author': 'Adrian'}, {'headline': 'Bar',
'author': 'Jacob'}]
>>> blogs.get_dicts(fields=['headline'], order_by=['headline'])
[{'headline': 'Bar'}, {'headline': 'Foo'}]
>>> blogs.get_dicts(fields=['headline'], order_by=['headline'],
headline__startswith='Foo')
[{'headline': 'Foo'}]

Basically, get_dicts() would be a module-level function that returned
a list of dictionaries instead of object instances.

If you wanted to do the equivalent of SELECT DISTINCT, you'd do this:

>>> blogs.get_dicts(fields=['headline'], distinct=True)

I'm not sure whether "get_dicts" is the best name for this, but,
whatever we do, it shouldn't be an option to get_list() -- it should
be a separate function, because it would return dictionaries, not
instances.

Adrian

[Robin Munn]

On 7/26/05, Adrian Holovaty <holo...@gmail.com> wrote:
>
> But, aside from that special case, I agree that there should be a way
> of executing SQL on particular fields. Jacob and I have tossed around
> the following solution in the past, but it never got past the idea
> stage because we were busy with other stuff:
>
> >>> blogs.get_dicts(fields=['headline', 'author'])
> [{'headline': 'Foo', 'author': 'Adrian'}, {'headline': 'Bar',
> 'author': 'Jacob'}]
> >>> blogs.get_dicts(fields=['headline'], order_by=['headline'])
> [{'headline': 'Bar'}, {'headline': 'Foo'}]
> >>> blogs.get_dicts(fields=['headline'], order_by=['headline'],
> headline__startswith='Foo')
> [{'headline': 'Foo'}]
>
> Basically, get_dicts() would be a module-level function that returned
> a list of dictionaries instead of object instances.
>
> If you wanted to do the equivalent of SELECT DISTINCT, you'd do this:
>
> >>> blogs.get_dicts(fields=['headline'], distinct=True)
>
> I'm not sure whether "get_dicts" is the best name for this, but,
> whatever we do, it shouldn't be an option to get_list() -- it should
> be a separate function, because it would return dictionaries, not
> instances.

Agreed that get_list() is not really the right place for it;
get_dicts(), or some such name, is much better. get_values()?
get_values_list()?

Anyway, whatever the name of the function, that removes the only
serious objection I could come up with to the idea. I'll work on a
patch.

--
Robin Munn
Robin...@gmail.com
GPG key 0xD6497014

[Robin Munn]

Done. The first version of this patch didn't have the "Be forgiving of
mistakes" section; but I noticed that when I tried to use it, my first
attempt at calling the function that *I myself had written* used
"fields='question'" where I had meant "fields=['question']". Since I
had written the code not ten minutes ago, and I'd already made that
mistake, it must be a very natural mistake to make. And the meaning is
unambiguous, so might as well be forgiving.

Patch below. Comments appreciated. If it looks good, I'll open a ticket for it.

Index: django/core/meta.py
===================================================================
--- django/core/meta.py (revision 331)
+++ django/core/meta.py (working copy)
@@ -562,6 +562,12 @@
new_mod.get_iterator = curry(function_get_iterator, opts, new_class)
new_mod.get_iterator.__doc__ = "Returns an iterator of %s
objects matching the given parameters." % name

+ new_mod.get_values = curry(function_get_values_list, opts, new_class)
+ new_mod.get_values.__doc__ = "Returns a list of dicts
matching the given parameters."
+
+ new_mod.get_values_iterator =
curry(function_get_values_iterator, opts, new_class)
+ new_mod.get_values_iterator.__doc__ = "Returns an iterator of
dicts matching the given parameters."
+
new_mod.get_count = curry(function_get_count, opts)
new_mod.get_count.__doc__ = "Returns the number of %s objects
matching the given parameters." % name

@@ -1047,6 +1053,37 @@
setattr(obj, f.rel.get_cache_name(), rel_obj)
return obj, index_end

+def function_get_values_iterator(opts, klass, **kwargs):
+ """
+ Return SELECT results as a list of dicts rather than as objects.
+ Example use: "SELECT DISTINCT year, month FROM blog ORDER BY year, month"
+ """
+ # kwargs['select'] is a dictionary, and dictionaries' key order is
+ # undefined, so we convert it to a list of tuples internally.
+ kwargs['select'] = kwargs.get('select', {}).items()
+
+ cursor = db.db.cursor()
+ _, sql, params = function_get_sql_clause(opts, **kwargs)
+ # Allow user to specify a specific list of fields to fetch
+ fields = kwargs.get('fields')
+ if isinstance(fields, basestring):
+ # Be forgiving of mistakes: convert to tuple
+ fields = (fields,)
+ if not fields:
+ # Default to all fields
+ fields = [f.name for f in opts.fields]
+ select = ['%s.%s' % (opts.db_table, f) for f in fields]
+ cursor.execute("SELECT " + (kwargs.get('distinct') and "DISTINCT
" or "") + ",".join(select) + sql, params)
+ while 1:
+ rows = cursor.fetchmany(GET_ITERATOR_CHUNK_SIZE)
+ if not rows:
+ raise StopIteration
+ for row in rows:
+ yield dict(zip(fields, row))
+
+def function_get_values_list(opts, klass, **kwargs):
+ return list(function_get_values_iterator(opts, klass, **kwargs))
+
def function_get_iterator(opts, klass, **kwargs):
# kwargs['select'] is a dictionary, and dictionaries' key order is
# undefined, so we convert it to a list of tuples internally.
@@ -1117,7 +1154,7 @@
# table_count is used to ensure table aliases are unique.
tables, join_where, where, params = [], [], [], []
for kwarg, kwarg_value in kwarg_items:
- if kwarg in ('order_by', 'limit', 'offset', 'select_related',
'distinct', 'select', 'tables', 'where', 'params'):
+ if kwarg in ('order_by', 'limit', 'offset', 'select_related',
'distinct', 'select', 'tables', 'where', 'params', 'fields'):
continue
if kwarg_value is None:
continue

[Robin Munn]

What, no comments? Guess the patch must look good then. :-)

I've created a ticket for this patch: http://code.djangoproject.com/ticket/214