Malcolm Tredinnick wrote:
> On Sat, 2006-09-02 at 13:53 -0700, Anders Aagaard wrote:
> > Hi
> >
> > I found I could use x.extra(select) statements to add custom row's to
> > my objects,
>
> Just to get the terminology right: you mean custom attributes. A single
> object represents one row in the database table, so you are not adding
> any more rows to things.
Ah, well, I've always thought of every row returned by an sql query, as
a row. Even though that row may not be in the database.
>
> > and since this lowers the amounts of query I need immensly,
> > I've started to love it. (my old front page took about 2 seconds to
> > load fully, the new one is instant).
>
> That sort of speed-up suggests you were doing a lot of repetitive work
> in the first case -- just changing some things to nested selects
> shouldn't improve it that much. Or else your database server is really
> slow. My first guess would be that you were accidently evaluating some
> querysets more than once, but it's hard to say just from this
> description, of course.
The old query was a normal django like system, where I first got a list
of all "content", then for every content checked all files. Resulting
in a ton of queries (and more importantly, a ton of python parsing).
>
> >
> > Example:
> > query = query.extra(select={'files_total':'SELECT COUNT(*) FROM
> > content_filename WHERE content_id=content_content.id'},)
>
> So now it's doing one sub-select for each row selected from the table
> (i.e. for each object returned). If you are doing this for a lot of
> objects, you may want to tune a bit more at the SQL level, but for small
> numbers of rows this should be fine.
>
> > Now this works extreamly well, I can access object.files_total, and
> > I've made it one big query, instead of doing it in my template (meaning
> > one query per time I count files).
> >
> > The problem is, I can't use filters obviusly, as it looks in the model
> > and fails to resolve "files_total", so I use an extra like this:
> > query = query.extra(where='files_total > 50')
> >
> > And this works very well as well, except from when I try to use .count
> > (or, any other django object tries to, which is breaking paginator for
> > me right now), as django is "clever" and strips off the select query,
> > ending up with this:
> >
> > SELECT COUNT(*) FROM "content_content" where files_total > 1;
> >
> > Which obviusly isn't going to work. Is there any "offical" way of
> > adding custom row's?
> >
> > (note that the actual model is much more advanced, and so are the extra
> > row's, I'm actually pulling extra row's for "files_total",
> > "files_checked (which is per user)" and "percentage_checked",
> > calculated based on the two previus parameters.)
>
> The best way to do this very much depends on your typical use. Are you
> only pulling a single object and then passing that into the template? Or
> are you pulling a list of objects at once and passing them into a
> template? In the latter case, you could probably do better to use a
> custom manager method to get all the data at once and add in the extra
> attributes as part of the manager method. You end up writing out the
> whole SQL query by hand, but can then tune it to your heart's content.
I ended up just overloading the count function, and that works.
>
> The short version is that there isn't really a nice way to mix
> extra(...) clauses with other things in querysets. The extra(...) is
> real poking around under the covers and it would be very hard to make it
> interact completely smoothly with other queryset operations. Once you
> start to get too deep into wanting to tweak the SQL, you really have to
> switch to things like custom manager methods and custom SQL.
>
> I've written before about using custom SQL in [1] and there are some
> examples in the weblog/models.py file of my weblog code ([2]).
The problem is that the goal was to use it with django's paginator, and
that requires a queryset.
>
> [1] http://www.pointy-stick.com/blog/2006/06/14/custom-sql-django/
> [2] http://www.pointy-stick.com/software/website-latest.tar.gz
>
> Regards,
> Malcolm
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users
-~----------~----~----~----~------~----~------~--~---
