Re: #7210 - F() Query Expressions

[koenb]

On 30 jan, 13:46, Russell Keith-Magee  wrote:
> If you are interested in implementing this, I'm happy to give you
> pointers. The first pointer I can suggest is that you will be looking
> at the SQLEvaluator class contained in
> django/db/models/sql/expressions.py. This class traverses the
> expression tree, turning it into raw SQL. The actual expression tree
> is a series of tree.Node objects defined in
> django/db/models/expressions.py.
>
> Also - anything you do here shouldn't be date-specific. Other data
> types - such as strings and GIS types - will require similar
> capabilities. Try to keep that in mind when you are looking to
> potential solutions for the date problem.
>
> > It seems though that the different backends will certainly need
> > different handlings:
>
> Indeed. The Postgres option should be fairly easy to implement - you
> just need to replace leaf nodes on the expression tree that are
> timedeltas with matching "interval X" statements. SQLite style
> expressions will be a little harder.
>
> Yours,
> Russ Magee %-)

I made a first attempt, see ticket #10154.

Idea is to use a specific node (and leaf) for combinations of an
expression with a timedelta.
I put the backend specifics into the relevant Operations classes (for
now only base (= sqlite implementation) and postgres).
sqlite and postgres pass the test.
The dispatching based on 'other' being a timedelta needs refining
though, since this is obviously not generic enough.

Koen


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: #7210 - F() Query Expressions

[Russell Keith-Magee]

On Fri, Jan 30, 2009 at 7:59 PM, koenb  wrote:
>
>> As committed, the expressions framework is fairly dumb - it is really
>> just a way to do two things:
>>  1) Expand Django-style field references into column names (and joins
>> if required)
>>  2) Turn Python expression trees into SQL expression trees.
>>
>> This means that the operations that are allowed depend somewhat on the
>> database in use. For example, on MySQL, you can perform mod operations
>> on floats; under Postgres, you can't.
>>
>> Constants in expressions are rendered as-is, so whether dates are
>> allowed depends on what your database allows. From some quick tests,
>> date + integer works on SQLite and MySQL -- or, at least, doesn't
>> throw an error. I haven't confirmed that the answer it gives is
>> actually meaningful. Postgres complains with an error "operator does
>> not exist: timestamp with time zone + integer".
>>
>> However, I'm certainly interested in entertaining any modification to
>> support more interesting expressions - for example, converting the
>> expression "F('date') + timedelta(days=3)" into meaningful SQL.
>> Patches welcome.
>
> I will have a look at how expressions are implemented (I am aware of
> the patches philosophy).

Glad to hear it :-)

If you are interested in implementing this, I'm happy to give you
pointers. The first pointer I can suggest is that you will be looking
at the SQLEvaluator class contained in
django/db/models/sql/expressions.py. This class traverses the
expression tree, turning it into raw SQL. The actual expression tree
is a series of tree.Node objects defined in
django/db/models/expressions.py.

Also - anything you do here shouldn't be date-specific. Other data
types - such as strings and GIS types - will require similar
capabilities. Try to keep that in mind when you are looking to
potential solutions for the date problem.

> It seems though that the different backends will certainly need
> different handlings:

Indeed. The Postgres option should be fairly easy to implement - you
just need to replace leaf nodes on the expression tree that are
timedeltas with matching "interval X" statements. SQLite style
expressions will be a little harder.

Yours,
Russ Magee %-)

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: #7210 - F() Query Expressions

[koenb]

>
> As committed, the expressions framework is fairly dumb - it is really
> just a way to do two things:
>  1) Expand Django-style field references into column names (and joins
> if required)
>  2) Turn Python expression trees into SQL expression trees.
>
> This means that the operations that are allowed depend somewhat on the
> database in use. For example, on MySQL, you can perform mod operations
> on floats; under Postgres, you can't.
>
> Constants in expressions are rendered as-is, so whether dates are
> allowed depends on what your database allows. From some quick tests,
> date + integer works on SQLite and MySQL -- or, at least, doesn't
> throw an error. I haven't confirmed that the answer it gives is
> actually meaningful. Postgres complains with an error "operator does
> not exist: timestamp with time zone + integer".
>
> However, I'm certainly interested in entertaining any modification to
> support more interesting expressions - for example, converting the
> expression "F('date') + timedelta(days=3)" into meaningful SQL.
> Patches welcome.

I will have a look at how expressions are implemented (I am aware of
the patches philosophy).

It seems though that the different backends will certainly need
different handlings:

I think

filter(enddate__gt=F('startdate')+timedelta(days=3))

will need to become in SQLITE:

where enddate > date(startdate, "3 days")

and in Postgres:

where enddate > (startdate + interval '3 days')

Koen

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: #7210 - F() Query Expressions

[Russell Keith-Magee]

On Fri, Jan 30, 2009 at 2:47 AM, koenb  wrote:
>
> This is really great! Thanks all for the good work.
>
> Just curious, has there been any work done to make this also work for
> dates ?
>
> like in
>
> longevents = Event.objects.filter(enddate__gte=F('startdate')+10)

As committed, the expressions framework is fairly dumb - it is really
just a way to do two things:
 1) Expand Django-style field references into column names (and joins
if required)
 2) Turn Python expression trees into SQL expression trees.

This means that the operations that are allowed depend somewhat on the
database in use. For example, on MySQL, you can perform mod operations
on floats; under Postgres, you can't.

Constants in expressions are rendered as-is, so whether dates are
allowed depends on what your database allows. From some quick tests,
date + integer works on SQLite and MySQL -- or, at least, doesn't
throw an error. I haven't confirmed that the answer it gives is
actually meaningful. Postgres complains with an error "operator does
not exist: timestamp with time zone + integer".

However, I'm certainly interested in entertaining any modification to
support more interesting expressions - for example, converting the
expression "F('date') + timedelta(days=3)" into meaningful SQL.
Patches welcome.

Yours,
Russ Magee %-)

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: #7210 - F() Query Expressions

[koenb]

This is really great! Thanks all for the good work.

Just curious, has there been any work done to make this also work for
dates ?

like in

longevents = Event.objects.filter(enddate__gte=F('startdate')+10)

or something like that ?

Koen

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

#7210 - F() Query Expressions

[Russell Keith-Magee]

Hi all,

With [9792], I've committed F() query expressions to trunk. For
details, see the docs:

http://docs.djangoproject.com/en/dev/topics/db/queries/#filters-can-reference-fields-on-the-model

There are two caveats worth knowing about:

1) This patch reveals a bug in the SQLite package that ships with
Ubuntu Intrepid Ibex (SQLite 3.5.9-3). Versions of SQLite 3.5.9 on
other operating systems (including Debian) don't appear to be
affected, and other versions of SQLite on Ubuntu don't appear to be
affected either.

2) F() expressions have not been updated to work with GIS fields.
However, based on some initial discussions with Justin, it should be
possible to make these changes without affecting any public API. If
anyone with access and experience with contrib.GIS cares to help out
on this front, assistance would be gratefully accepted; otherwise,
we'll just have to wait until Justin surfaces from his Bar exams.

Finally, some thank yous:

Sebastian Noack for getting the ball rolling. Sebastian raised the
original ticket and wrote the original patch. Not much of that patch
survived into the final implementation, but his contribution is very
much appreciated. Unfortunately, I forgot about Sebastian's original
involvement when I wrote the commit message - for that, I apologize.

Nicolas Lara for doing the heavy lifting as part of the 2008 Google
Summer of Code. F() expressions were an optional item in his GSoC
proposal, but he made such good progress on aggregates that we got
two-for-one. Nicolas, take a bow - you did some excellent work here.

Alex Gaynor provided some excellent assistance debugging and fixing a
number of problems as we neared completion of the project (including
narrowing down the mutant SQLite problem). Thanks Alex.

Malcolm Tredinnick did his usual stunning job at reviewing code and
picking holes in designs. Thanks Malcolm.

Thanks also to the many people that contributed ideas during the
design phase, and to those who tested the code as it was developing.
Your efforts may not have ended up in the final design or turned into
a bug report, but thanks for taking the time to look at an
experimental feature.

Yours,
Russ Magee %-)

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---