Querying a model with a ForeignKey

[ofeyofey]

I have two tables in a sqlite3 DB, PostModel and TopicModel. The PostModel has fields id, post, author, pub_date and topicid. This last fields topicid has a foreignkey to the other table Postmodel. PostModel has the fields id, topic.

There is a one-to-many relation from the TopicModel to the PostModel. So a Topic can have many posts but a post can only have one topic.

I would like to create query the PostModel to get the latest post for each topicid.

So something like, SELECT PostModel.topicid WHERE date_pub is the latest. But i would like to use the Django Query API not SQL.

My models look like this,

class TopicModel(models.Model):

    topic = models.CharField(max_length=300)

    extra = models.CharField(max_length=100)

    def __str__(self):              # __unicode__ on Python 2

            return self.topic

class PostModel(models.Model):

    post = HTMLField(blank=True)

    pub_date = models.DateTimeField('date published')

    author = models.CharField(max_length=30)

    topicid = models.ForeignKey(TopicModel, related_name = 'topicThing')

    def __str__(self):              # __unicode__ on Python 2

            return self.post

Here is the view

def thread(request, id):

    if request.method == "POST":

        print 'we are inside POST'

        pk = id

        tform = get_object_or_404(TopicModel, pk=id)

        Pform = PostForm(request.POST)

        if Pform.is_valid():

            # tform = Tform.save(commit=False)

            pform = Pform.save(commit=False)

            pform.topicid = tform

            # pform.topicid = pk

            pform.author = request.user

            pform.pub_date = timezone.now()

            pform.save()

            # return redirect('post_detail', pk=post.pk)

            return redirect('thread', id)

    else:

        pk=id

        pModel = reversed(PostModel.objects.all().filter(topicid_id=pk))

        postform = PostForm()

    return render(request, 'thread.html', {'pModel': pModel, 'postform' : postform})

Thanks

[Aaron Cannon]

Does

pModel = PostModel.objects.filter(topicid_id=pk).order_by('-pub_date')[0]

work for you?

Also, I believe it would be more conventional if you named your
topicid field simply topic, your post field as body, and your topic
field as name.

Luck.

Aaron

> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users...@googlegroups.com.
> To post to this group, send email to django...@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/8dc3a17e-850e-4b5f-91d3-1909c45f97a8%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>

[ofeyofey]

Aaron thanks for looking at this.

I understand what you did now, and that is great.

But I actually realised that the views I showed are not the correct ones and the view I want to do this in called 'init' actually has no id.

So How might I get one instance of each topic where if there is more than one instance I get the latest one only?

Here is the view i should have posted.

def init(request):

    # tModel = reversed(TopicModel.objects.all())

    # pModel = reversed(PostModel.objects.all())

    pModel = reversed(PostModel.objects.filter(topicid_id=pk).order_by('-pub_date')[0])

    # context = {'tModel': tModel, 'pModel': pModel}

    context = {'pModel': pModel}

    return render(request, 'forum.html', context)

Thanks again,

[Aaron Cannon]

I fear doing this efficiently is beyond my limited abilities. Will
look forward to other answers you might get.

Good luck.

Aaron

On 5/3/16, ofeyofey <shane...@gmail.com> wrote:

> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users...@googlegroups.com.
> To post to this group, send email to django...@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/django-users/a0a43100-4172-4054-aaf2-0302a8a13cbe%40googlegroups.com.

[ofeyofey]

No problem. I will post if i figure it out.

Thanks very much for your help

On Tuesday, 3 May 2016 18:43:53 UTC+1, ofeyofey wrote:

[ofeyofey]

Hi,

Just in case it is useful to anyone, here is the solution,

pModel = PostModel.objects.raw('SELECT *, max(pub_date), count(topicid_id) AS freq FROM crudapp_postmodel GROUP BY topicid_id ORDER BY pub_date DESC LIMIT 0,20')

Thanks

On Tuesday, 3 May 2016 18:43:53 UTC+1, ofeyofey wrote:

[ofeyofey]

This also grabs the frequency or number of views per post.

On Tuesday, 3 May 2016 18:43:53 UTC+1, ofeyofey wrote: