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.
> 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.
>
> 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.
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]).
[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
-~----------~----~----~----~------~----~------~--~---
