Django : Get specific columns from multiple tables

Question

I need to fetch columns from multiple tables, how do 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)

Spcifically 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 it says no such fields.

Is there something I need to change in my models class?

Can you post your django model code?

Greg
– Greg

2014-09-29 22:57:31 +00:00
Commented Sep 29, 2014 at 22:57 — Greg
– Greg, Commented Sep 29, 2014 at 22:57
Oops, that was supposed to be Events. Updated!

Rohit
– Rohit

2014-09-30 01:42:26 +00:00
Commented Sep 30, 2014 at 1:42 — Rohit
– Rohit, Commented Sep 30, 2014 at 1:42

Greg · Accepted Answer · 2014-09-30 01:47:00Z

10

Try this:

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

Alternately, you could use select_related:

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

then you'll get UserLog objects, with the event pre-fetched, so you can access the event without triggering another query.

(Also, standard naming convention would be to call the model Event, not Events)

edited Sep 30, 2014 at 1:47

answered Sep 30, 2014 at 0:59

Greg

10.4k5 gold badges47 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rohit Over a year ago

That worked! Dumb enough, I forgot to access the other tables fields by foreignkey_fields. Thanks :)

Paul Richter · Accepted Answer · 2017-08-15 20:28:10Z

3

use select_related() like this:

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

edited Aug 15, 2017 at 20:28

Paul Richter

11.1k10 gold badges56 silver badges89 bronze badges

answered Sep 29, 2014 at 23:02

Hasan Ramezani

5,21226 silver badges34 bronze badges

3 Comments

Rohit Over a year ago

That was quick! I tried this UserLog.objects.filter(event_id__event_id=1).select_related('description').values() And i got

[{'log_id': 4L, u'event_id': 1L, 'ip_address': u'10.100.32.142', u'user_id': 2L, 'time': datetime.datetime(2014, 9, 25, 21, 29, 28, tzinfo=<UTC>)}

'Description' field isn't being displayed!

Rohit Over a year ago

You are right, I was trying to incorrectly access fields from other tables.

M1rwen Over a year ago

Guys could you please help me resolve this stackoverflow.com/questions/49587819/…

Krishna Jangid · Accepted Answer · 2019-08-13 11:31:12Z

0

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

in the country__countryname country is the name of field when u define the relationship like follow

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 are from Studentinfo model

so that will give me the following result

edited Aug 13, 2019 at 11:31

answered Aug 13, 2019 at 9:16

Krishna Jangid

5,5066 gold badges33 silver badges37 bronze badges

Collectives™ on Stack Overflow

Django : Get specific columns from multiple tables

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related