CODEWITHSUNDEEP
pythonsqldjangopostgresql
Hassan Baig
Hassan Baig

Reputation: 15844

SQL Syntax error, while formulating Django queryset using extra

In a Django app named links, at one point I run the following code on a queryset, and getting the error: column user_id does not exist:

date = datetime.now()-timedelta(hours=1)
groups = Group.objects.filter(private='0').extra(select = {
  "views" : """
  SELECT COUNT(*)
  FROM links_grouptraffic
    JOIN links_group on links_grouptraffic.which_group_id = links_group.id
  WHERE links_grouptraffic.visitor_id = user_id
  AND links_grouptraffic.time > %s """}, select_params=(date,),
).order_by("-views")

The user_id in this code is a reference to a Django.contrib.auth user. I have not overridden this in any way.

Other things I've tried:

If I change user_id to user.id, I get the error: syntax error at or near "." This is thrown in reference to the line WHERE links_grouptraffic.visitor_id = user_id.

If I change user_id to links_user.id, I get the error: missing FROM-clause entry for table "links_user"

If I change user_id to user (and correspondingly, visitor_id to visitor), I get the error: column links_grouptraffic.visitor does not exist (visitor is a foreign key to user, hence ought to be visitor_id).

Related models are:

class Group(models.Model):
    topic = models.TextField(validators=[MaxLengthValidator(200)])
    owner = models.ForeignKey(User)
    private = models.CharField(max_length=5, default=0)
    created_at = models.DateTimeField(auto_now_add=True)

class GroupTraffic(models.Model):
    visitor = models.ForeignKey(User)
    which_group = models.ForeignKey(Group)
    time = models.DateTimeField(db_index=True, auto_now_add=True)

How do I fix this?

Upvotes: 0

Views: 140

Answers (1)

Tomasz Jakub Rup
Tomasz Jakub Rup

Reputation: 10680

owner_id not user_id:

  SELECT COUNT(*)
  FROM links_grouptraffic
    JOIN links_group on links_grouptraffic.which_group_id = links_group.id
  WHERE links_grouptraffic.visitor_id = owner_id
  AND links_grouptraffic.time > %s

Upvotes: 1

Related Questions