Django - Getting records from first table based on Second table' field value

Question

I am developing a Django App, in which I have used PostgreSQL as a Database. The models in the Application are as follow.

class topics(models.Model):
    topicid = models.IntegerField()
    topicname = models.CharField(max_length=512)
    topicdescription = models.CharField(max_length=512)

class Video(models.Model):
   video_file_name = models.CharField(max_length=100)
   video_url = models.CharField(max_length=100, default='default.mp4')
   video_description = models.CharField(max_length=2000, default='Video Description') 
   video_topic_id = models.IntegerField(default=1)

Here, one topic will have 0 or many videos under it.

The query condition is, I want the topic list uniquley, which will have atleast one video(more than zero). Means I have to ignore the topics in results which are not having any video under that topic.

Currently I use a single get all function.

all_topics = topics.objects.all();

"..... **topic list** uniquley, **which will have atleast one video**......" If topic table and video table do not have any relationship, then how could topic list can have any video? There must be some kind of field or constraint or relationship in the video table or topic table inorder to associate a particular video to a certain topic? Please make yourself clear? — zaidfazil, May 22 '17 at 17:26
@FazilZaid, you mean I have to create the relationship inbetween of these tables, then its possible right? — Hemal Pandya, May 22 '17 at 17:28

score 0 · Accepted Answer · answered May 22 '17 at 17:29

You should have a ForeignKey to the topics model, instead of that topic_id field:

video_topic = models.ForeignKey('topics')

This will use the same underlying video_topic_id database field, so no data needs to change.

Now you can query for topics which have no videos:

topics = topics.objects.filter(video=None)

(Note, Python and Django style is to use initial capitals for class names, and singular model names. So your topics model should be called Topic.)

Thanks for your answer. I have taken it in considerations and now it works.! — Hemal Pandya, May 22 '17 at 18:06

zaidfazil · Answer 2 · 2017-05-22T17:36:47.710

Your query condition is this,

The query condition is, I want the topic list uniquley, which will have atleast one video(more than zero). Means I have to ignore the topics in results which are not having any video under that topic.

If topic table and video table do not have any relationship, then how could topic list can have any video?

A ForeignKey from Video table to Topic represents a ManyToOne relation between the tables. A Topic instance can have multiple Video instances, but each video belong to only a single Topic. The kind of relation you require, I suppose.

A relationship can be done something like this,

class Topic(models.Model):
    topicid = models.IntegerField()
    topicname = models.CharField(max_length=512)
    topicdescription = models.CharField(max_length=512)

class Video(models.Model):
    video_file_name = models.CharField(max_length=100)
    video_url = models.CharField(max_length=100, default='default.mp4')
    video_description = models.CharField(max_length=2000, default='Video Description') 
    video_topic_id = models.ForeignKey(Topic, related_name='videos')

Then you could filter the topics with at least one video like this,

Topic.objects.all().exclude(videos=None)

Thanks for the response. It worked after doing like this. – Hemal Pandya May 22 '17 at 18:06 — Hemal Pandya, May 22 '17 at 18:06

Django - Getting records from first table based on Second table' field value

2 Answers2