Django: getting specific columns from multiple tables

Question

I need to get columns from multiple tables, how can I achieve this?

UserLog +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | log_id | int(11) | NO | PRI | NULL | auto_increment | | time | datetime | NO | | NULL | | | ip_address | char(15) | YES | | NULL | | | event_id | int(11) | NO | MUL | NULL | | | user_id | int(11) | NO | MUL | NULL | | +------------+----------+------+-----+---------+----------------+ Events +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | event_id | int(11) | NO | PRI | NULL | auto_increment | | description | varchar(200) | NO | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 

This is my models.py

 class Events(models.Model): event_id = models.AutoField(primary_key=True,verbose_name="Event id") description = models.CharField(max_length=200,verbose_name="Description") def __unicode__(self): return u"%d : %s" % (self.event_id, self.description) class UserLog(models.Model): log_id = models.AutoField(primary_key = True) user = models.ForeignKey(User) event = models.ForeignKey(Events,verbose_name = "event") time = models.DateTimeField(default=datetime.now,blank=True) ip_address = models.IPAddressField(null=True) def __unicode__(self): return u"%s (%s): %s" % (self.log_id, self.user, self.event) 

I want to do SELECT log_id,time,user_id,event_id,description from UserLog,Events where UserLog.event_id = Events.event_id

I tried UserLog.objects.filter(event_id__event_id).values('log_id','time','user_id','event_id','description') . But there are no such fields in it.

Is there something I need to change in the model class?

Answer 1

Try it:

 UserLog.objects.filter(event_id=1).values('log_id', 'time', 'user_id', 'event_id', 'event__description') 

Alternatively, you can use select_related :

 UserLog.objects.filter(event__id=1).select_related('event') 

then you will get UserLog objects with a pre-extracted event so that you can access the event without calling another request.

(In addition, the standard naming convention will call the Event model, not Events )

Answer 2

use select_related () as follows:

 UserLog.objects.filter(event_id__event_id=1).select_related('event').values('log_id','time','user_id','event_id', 'event__description') 

Answer 3

 dataStudent = Studentinfo.objects.select_related().values( 'country__countryname', 'state__statename', 'city__cityname', 'id','studentname', 'rollno', 'email', 'phoneno') 

in country__countryname country is the name field when you define the relationship as follows:

 city = models.ForeignKey(City, on_delete=models.CASCADE) state = models.ForeignKey(State, on_delete=models.CASCADE) country = models.ForeignKey(Country, on_delete=models.CASCADE) 

and countryname is the table field inside the country table and
same as state__statename and city__cityname

and other fields from the Studentinfo model

so that will give me the following result