Fetching data from parent table in Django in database hierarchy

Question

Following this answer, I tried to split my SQL Story table into parent/children - with the children holding the specific user data, the parent more generic data. Now I've run into a problem that betrays my lack of experience in Django. My user page attempts to show a list of all the stories that a user has written. Before, when my user page was only pulling data from the story table, it worked fine. Now I need to pull data from two tables with linked info and I just can't work out how to do it.

Here's my user_page view before attempts to pull data from the parent story table too:

def user_page(request, username):
    user = get_object_or_404(User, username=username)
    userstories = user.userstory_set.order_by('-id')
    variables = RequestContext(request, {
    'username': username,
    'userstories': userstories,
    'show_tags': True
    })
    return render_to_response('user_page.html', variables)

Here is my models.py:

class story(models.Model):
    title = models.CharField(max_length=400)
    thetext = models.TextField()

class userstory(models.Model):
    main = models.ForeignKey(story)
    date = models.DateTimeField()
    user = models.ForeignKey(User)

I don't really know where to start in terms of looking up the appropriate information in the parent table too and assinging it to a variable. What I need to do is follow the 'main' Key of the userstory table into the story table and assign the story table as a variable. But I just can't see how to implement that in the definition.

EDIT: I've tried story = userstory.objects.get(user=user) but I get 'userstory matching query does not exist.'

Answer 1

Reading through your previous question that you linked to, I've discovered where the confusion lies. I was under the impression that a Story may have many UserStorys associated with it. Note that I'm using Capital for the class name, which is common Python practise. I've made this assumption because your model structure is allowing this to happen with the use of a Foreign Key in your UserStory model. Your model structure should look like this instead:

class Story(models.Model):
    title = models.CharField(max_length=400)
    thetext = models.TextField()

class UserStory(models.Model):
    story = models.OneToOneField(Story) # renamed field to story as convention suggests
    date = models.DateTimeField()
    user = models.ForeignKey(User)

class ClassicStory(models.Model)
    story = models.OneToOneField(Story)
    date = models.DateTimeField()
    author = models.CharField(max_length=200)

See the use of OneToOne relationships here. A OneToOne field denotes a 1-to-1 relationship, meaning that a Story has one, and only one, UserStory. This also means that a UserStory is related to exactly one Story. This is the "parent-child" relationship, with the extra constraint that a parent has only a single child. Your use of a ForeignKey before means that a Story has multiple UserStories associated with it, which is wrong for your use case.

Now your queries (and attribute accessors) will behave like you expected.

# get all of the users UserStories:
user = request.user
stories = UserStory.objects.filter(user=user).select_related('story')

# print all of the stories:

for s in stories:
    print s.story.title
    print s.story.thetext

Note that select_related will create a SQL join, so you're not executing another query each time you print out the story text. Read up on this, it is very very very important!

Your previous question mentions that you have another table, ClassicStories. It should also have a OneToOneField, just like the UserStories. Using OneToOne fields in this way makes it very difficult to iterate over the Story model, as it may be a "ClassicStory" but it might be a "UserStory" instead:

# iterate over ALL stories

allstories = Story.objects.all()
for s in allstories:
    print s.title
    print s.thetext
    print s.userstory     # this might error!
    print s.classicstory  # this might error!

See the issue? You don't know what kind of story it is. You need to check the type of story it is before accessing the fields in the sub-table. There are projects that help manage this kind of inheritance around, an example is django-model-utils InheritanceManager, but that's a little advanved. If you never need to iterate over the Story model and access it's sub tables, you don't need to worry though. As long as you only access Story from ClassicStories or UserStories, you will be fine.