Prefetching a single item

[cool-RR]

Hi,

Say that I have a model Train and it has a ManyToMany to model Seat. I'm looking at a queryset of Train and I want to do a prefetch_related on it, but I don't want to get all the Seat objects; I want only the first Seat object for each Train object.  Is this possible with prefetch_related and/or Prefetch? How? 

To clarify: My goal here is to save on querysets while still being able to retrieve the first Seat object in each `train.seats`.

Thanks,

Ram.

[Simon Charette]

Can your seats be part of multiple trains?

Is there a reason you defined a ManyToMany from Train to Seat instead of a ForeignKey from Seat to Train?

If your schema was defined as the latter the following could probably work (given you only want the first Seat of each Train object):

trains = Train.objects.prefetch_related(first_seats=Seat.objects.order_by('train', 'order').distinct('train'))
for train in trains:
    first_seat = train.first_seats[0]

Else given you've explicitly defined a TrainSeat model to handle the through relationship to store the order:

class TrainSeat(models.Model):
    train = models.ForeignKey(Train, related_name='train_seats')
    seat = models.ForeignKey(Seat)
    order = models.PositiveIntegerField()

trains = Train.objects.prefetch_related(first_train_seats=Prefetch('train_seats', TrainSeat.objects.select_related('seat').order_by('train', 'order').distinct('train'))
for train in trains:
    first_seat = train.first_train_seats[0].seat

Hope it helps.

Simon

[Ram Rachum]

Thanks Simon! That worked.

I would say it's a bit devious though, and it was nice if there was a method that didn't use `distinct`, which has its own issues. (And can't be used for any number of items other than 1 in this case.) 

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/-wwfdhi0qXU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/b3fcf574-9024-46f9-9499-c6e67d2e3a4b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Simon Charette]

I agree it's a bit devious, it took me a couple of minutes to figure out it would work for this particular use case.

Ideally there would a kwarg to Prefect that does the limiting automatically, something like:

SELECT train.id, s.* FROM train LATERAL JOIN (
    SELECT * FROM seat WHERE seat.train_id = train.id LIMIT 5
) s ON TRUE
WHERE train.id IN (1,2,3)