I have 2 tables. They are joined by a foreign key relationship. In django, how do i do the equivalent of
select col1,col2,col3, table2.* from table1 join table2 on table1.table1id = table2.table2id
I am using serializers.serialize and as such values() does not work on the model
EDIT
Here are my 2 models i am using
class UserProfile(models.Model):
FbookID = models.CharField(
max_length=100,
blank=True,
verbose_name="facebookid",
primary_key=True
)
Fname = models.CharField(
max_length=100,
blank=True,
verbose_name="fname"
)
profilePic = models.CharField(
max_length=256,
blank=True,
verbose_name="picture"
)
Sname = models.CharField(
max_length=100,
blank=True,
)
faccesskey = models.CharField(
max_length=255,
blank=True,
)
userGender = models.CharField(
max_length=15,
default='MALE')
joined = models.DateTimeField(
auto_now_add=True,
verbose_name = "userjoinedsite"
)
last_active = models.DateTimeField(
auto_now=True,
verbose_name = "lastActiveOnSite"
)
#have to refer to other class in strings as this table is not created when this command is run
last_loc = models.ForeignKey('user_LocLat',null=True)
email = models.EmailField(verbose_name="EmailOfUser")
class user_LocLat(models.Model):
locationID = models.AutoField(
verbose_name="location",
null= False,
primary_key = True
)
FbookIDlat = models.ForeignKey('UserProfile')
Date_of_loc = models.DateTimeField(
auto_now_add=True
)
loc = models.PointField(
verbose_name="location",
blank=False,
null=False
)
speed = models.FloatField( verbose_name ='speedms',null=True)
I need to access Fname,Sname,Gender, last_active, email from UserProfile and loc and speed from User_latloc. The main thing is i can not include faccesskey in the return
Your sql don't have where clause, so to select all ocurrences:
user_loclat = user_LocLat.objects.all()
Then you can iterate and access to the fields, like:
for user_l in user_loclat:
loc = user_l.loc
Fname = user_l.FbookIDlat.Fname
...
Or you can use values():
users_data = user_LocLat.objects.all().values(loc, FbookDlat__Fname, ...)
when using django there is no need to use SQL directly (on some special cases you may need) but generally django orm is your best option:
for simplicity of your code change
FbookIDlat = models.ForeignKey('UserProfile')
to this:
FbookIDlat = models.ForeignKey('UserProfile', related_name='fbookidlats')
and remove this line:
last_loc = models.ForeignKey('user_LocLat',null=True)
when you create a foreign key django handles reverse call back
assume you want get info for a profile:
profile = UserProfile.objects.get(FbookID='fbid')
for fbookidlat in profile.fbookidlats.all():
print(fbooidlat.speed)
PS-1: please read pep8 it helps you write a cleaner code
PS-2: please read this link