single queryset from multiple tables

Showing 1-9 of 9 messages
single queryset from multiple tables veearrsix 4/10/09 1:40 PM
This question seems to have been asked a few times, but never answered
fully.

I am looking to query more than one table, but return the results to a
template as one queryset ordered by date.

The reason i'm doing this is to create a single queryset including
information from a blog table, twitter table and delicious table to be
displayed as a sort of log of my online activity sorted by date.

the blog post model:

class Post(models.Model):
    slug = models.SlugField(unique=True)
    tags = TagField()
    title = models.CharField(max_length=80)
    pubdate = models.DateTimeField(default=datetime.datetime.now)
    modifieddate = models.DateTimeField(auto_now=True)
    body = models.TextField()
    draft = models.BooleanField(default=True)
    closed = models.BooleanField()

the delicious model (taken from syncr app)

class Bookmark(models.Model):
    # description, href, tags, extended, dt
    description = models.CharField(max_length=250, blank=True)
    url = models.URLField(unique=True)
    tags = TagField()
    extended_info = models.TextField(blank=True)
    post_hash = models.CharField(max_length=100)
    saved_date = models.DateTimeField()

the twitter model (taken from syncr app)

class Tweet(models.Model):
    pub_time    = models.DateTimeField()
    twitter_id  = models.PositiveIntegerField()
    text        = models.TextField()
    user        = models.ForeignKey('TwitterUser')


I'd like to get these into one queryset, possibly including a field
that indicates what type of listing is being displayed. Obviously I
dont need all of the fields from each of the models, just enough to
display the title and a possible link.

Thanks in adance, hope someone can help me out, I don't know where to
start with this.
Re: single queryset from multiple tables Alex_Gaynor 4/10/09 1:44 PM
There is no way to get a single QuerySet with results from multiple models.  What you want to do is best approximated like this:

from itertools import chain

sorted(chain(Model1.obejcts.all(), Model2.objects.all()), key=lambda o: o.pub_date)


Alex
--
"I disapprove of what you say, but I will defend to the death your right to say it." --Voltaire
"The people's good is the highest law."--Cicero
Re: single queryset from multiple tables Malcolm Tredinnick 4/10/09 4:40 PM
On Fri, 2009-04-10 at 16:44 -0400, Alex Gaynor wrote:
>
>
> On Fri, Apr 10, 2009 at 4:40 PM, veearrsix <stu...@googlemail.com>
> wrote:
>        
>         This question seems to have been asked a few times, but never
>         answered
>         fully.

Not quite true. It's been answered fully a lot of times: querysets are
iterators, so you have multiple sorted iterators and combining them into
a single sorted iterator is just a merge sort.

Creating a single queryset is almost always not the real problem people
are trying to solve. Which is fortunate, since it doesn't make sense on
a definitional level: a queryset is the result of filtering a set of
model results, not combining arbitrary SQL. Combining multiple querysets
into a single result that can be iterated through is the typical problem
trying to be solved.

[...]


>
> There is no way to get a single QuerySet with results from multiple
> models.  What you want to do is best approximated like this:
>
> from itertools import chain
>
> sorted(chain(Model1.obejcts.all(), Model2.objects.all()), key=lambda
> o: o.pub_date)

That's a  bit crufty, since it pulls everything into memory immediately.
Not cool with a few tens of thousands items in the result set (creating
all those Python objects is noticeable).

Merge or heap sort is your friend here. Sure, us old guys have the
advantage here, because that stuff was very standard back in the age of
dinosaurs when RAM was scarcer and hard- and tape-drives were slower, so
random access wasn't always an option.

Particularly with iterators, storing the (next head item, rest of
iterator) pair in a heap leads to a very neat and fast way to create a
combined iterator. Here's one professional-quality implementation that I
would recommend: http://code.activestate.com/recipes/491285/

Regards,
Malcolm

Re: single queryset from multiple tables Alex Gaynor 4/10/09 4:44 PM



It has one unfortunate failing(which is the fault of the heapq library), it doesn't take a key/cmp function like sorted does.  It is otherwise completely excellent though.
 

Regards,
Malcolm



Re: single queryset from multiple tables Malcolm Tredinnick 4/10/09 4:52 PM
On Fri, 2009-04-10 at 19:44 -0400, Alex Gaynor wrote:
>
>
> On Fri, Apr 10, 2009 at 7:40 PM, Malcolm Tredinnick
> <mal...@pointy-stick.com> wrote:
[...]

>        
>         Particularly with iterators, storing the (next head item, rest
>         of
>         iterator) pair in a heap leads to a very neat and fast way to
>         create a
>         combined iterator. Here's one professional-quality
>         implementation that I
>         would recommend: http://code.activestate.com/recipes/491285/
>
> It has one unfortunate failing(which is the fault of the heapq
> library), it doesn't take a key/cmp function like sorted does.  It is
> otherwise completely excellent though.

It's Python and so is the heap module! Modifying things is easy. I've
used the same solution with custom comparison functions one a number of
occasions. Even writing a small heap implementaiton with a custom
comparison is only about two dozen lines. Come on, think a little bit
outside the box!

Regards,
Malcolm

Re: single queryset from multiple tables Alex Gaynor 4/10/09 4:55 PM



I'm not!  A heap sort is a great solution to this problem(but you already knew that), it's just that the implementation in the python stdlib doesn't work perfectly for this usecase(indeed I filed a ticket about this upsteam several months ago, for reference it was rejected).  I did the same thing you suggested for a blog post on this topic a number of months ago, however it wasn't nearly as optimial as this(I believe you even commented to that effect on the post).
 

Regards,
Malcolm





Re: single queryset from multiple tables veearrsix 4/14/09 7:50 AM
Thanks for the help so far guys, I've used that recipe suggested by
Malcolm, where by I pass a number of querysets into the method. It
works as expected I guess, BUT, I would like to be able to sort by the
datetime field from each queryset, however the fieldnames for each of
the date time fields from each queryset are different. Is there a way
around this, should I actually be looking at a custom sql query?

On Apr 11, 12:55 am, Alex Gaynor <alex.gay...@gmail.com> wrote:
> On Fri, Apr 10, 2009 at 7:52 PM, Malcolm Tredinnick <
>
>
>
> malc...@pointy-stick.com> wrote:
>
> > On Fri, 2009-04-10 at 19:44 -0400, Alex Gaynor wrote:
>
> > > On Fri, Apr 10, 2009 at 7:40 PM, Malcolm Tredinnick
Re: single queryset from multiple tables Alex Gaynor 4/14/09 7:53 AM
Well, if you use some sorting mechanism that takes a key function(like the one sorted takes) you could write one up that's something like:

DATE_FIELD_MAPPING = {
    Model1: 'date',
    Model2: 'pubdate',
}

def my_key_func(obj):
    return getattr(obj, DATE_FIELD_MAPPING[type(obj)])

And then sorted(chain(Model1.objects.all(), Model2.objects.all()), key=my_key_func)

Or something similar using the heap sort method malcolm and I discussed.
Re: single queryset from multiple tables veearrsix 4/15/09 5:49 AM
Thanks guys that was perfect

On Apr 14, 3:53 pm, Alex Gaynor <alex.gay...@gmail.com> wrote: