Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django : Get specific columns from multiple tables

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?

like image 289
theblackpearl Avatar asked Sep 29 '14 22:09

theblackpearl


2 Answers

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)

like image 133
Greg Avatar answered Nov 20 '22 21:11

Greg


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')
like image 34
Hasan Ramezani Avatar answered Nov 20 '22 22:11

Hasan Ramezani