Thanks for the help.
I think you are on the right track however i could not get this to work.
Maybe an example query using cursor.execute would help so you can see what i
am trying to do.
def sqlAllEventsMenu():
cursor = connection.cursor()
cursor.execute("""
SELECT events_event.id, events_event.start, events_event.title,
events_location.state_id, events_location.name
FROM events_event LEFT JOIN events_location
ON events_event.id = events_location.id
""")
return convertToDictionary(cursor)
On Fri, Jun 19, 2009 at 4:11 PM, BenW <[email protected]> wrote:
>
> I'm not sure I completely understand the problem, but I'll give it a
> stab:
>
> events = Event.objects.filter(start__gte=datetime.today())
> locations = Location.objects.filter(location__in=events)
>
> results = [
> {'start': loc.location.start, 'title': loc.location.title, 'id':
> loc.location.id, 'state_id': loc.state.id, 'name': loc.name} \
> for loc in locations.iterator()
> ]
>
> render_to_response('template.htm', {'results': results})
>
> I run a query similar to this one on a 50,000 record table and it only
> takes ~0.30 seconds, and that includes Json serialization!
>
> On Jun 17, 5:00 pm, Sam Walters <[email protected]> wrote:
> > Here is the essentials from my models.py file:
> >
> --------------------------------------------------------------------------------------------------------
> > class Event (models.Model):
> > contact = models.CharField(max_length=100)
> > email = models.CharField(max_length=100)
> > url = models.CharField(max_length=100)
> > title = models.CharField(max_length=100)
> > start = models.DateTimeField('date time when')
> > end = models.DateTimeField('date till')
> >
> > class Location (models.Model):
> > name = models.CharField(max_length=50)
> > state = models.ForeignKey(State)
> > location = models.ForeignKey(Event)
> >
> --------------------------------------------------------------------------------------------------------
> > This is a One-to-Many Relationship where an Event can have one or more
> > Locations.
> >
> > The problem i need to solve is in the 'views.py' I want to be able to
> > query 'Event' such that i retrieve an arbitrary subset of events AND
> > any associated Locations and return only the relevant fields to the
> > template layer.
> >
> > The most standard use for this database will be to get all events from
> > today into the future and their associated locations. On average there
> > would be say 25 event rows returned and perhaps 27 locations as some
> > events have more than one location.
> >
> --------------------------------------------------------------------------------------------------------
> > e_future = Event.objects.filter(start__gte = datetime.today())
> >
> --------------------------------------------------------------------------------------------------------
> > having readhttp://docs.djangoproject.com/en/dev/topics/db/queries/
> >
> > i realise i can use a reverse foreign key to access an item in the
> > Location table:
> >
> --------------------------------------------------------------------------------------------------------
> > for e in e_future:
> > e_location=e.location_set.all()
> >
> --------------------------------------------------------------------------------------------------------
> > From here it gets messy as i have to deal with the individual event
> > elements 'e' out of a queryset 'e_future' and then reassemble the
> > elements back into a queryset *i dont know how to do this* so my
> > method can return the selected columns:
> >
> --------------------------------------------------------------------------------------------------------
> > e_future_subset = e_future.values('start', 'title','id',)
> > e_location_subset = e_location.values('state_id', 'name',)
> >
> --------------------------------------------------------------------------------------------------------
> > Then i have to combine the two objects 'e_future_subset' and
> > 'e_location_subset' (which i believe are dictionaries) and return the
> > result.
> >
> --------------------------------------------------------------------------------------------------------
> > e_all = e_future_subset + e_location_subset
> > return render_to_response('events.html', { 'e_all': e_all,})
> >
> --------------------------------------------------------------------------------------------------------
> >
> > As you can see I have been researching the solution and i'm stuck only
> > on a couple of points.
> > Surely this is one of the most common requirements for a django setup
> > where the 'model.py' describes a number of related tables 'one-to-
> > many' 'one-to-one' or 'many-to-many' relationships, a query (or number
> > of queries) are made and results are returned from multiple tables
> > based on an entry's 'id' and accessed through foreign-key pointers.
> >
> > Any help would be greatly appreciated as I seem to have a handle on
> > everything else in my django project.
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
