SQL Syntax error, while formulating Django queryset using extra

Question

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?

Group model has the attribute owner = models.ForeignKey(User) and grouptraffic model has the attribute visitor = models.ForeignKey(User) — Hassan Baig
– Hassan Baig, Commented Nov 19, 2015 at 14:33

Tomasz Jakub Rup · Accepted Answer · 2015-11-19 14:34:46Z

1

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

answered Nov 19, 2015 at 14:34

Tomasz Jakub Rup

10.7k7 gold badges52 silver badges49 bronze badges

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

1 Comment

Tomasz Jakub Rup Over a year ago

Don't forget to vote. Voting promotes good answers.

Collectives™ on Stack Overflow

SQL Syntax error, while formulating Django queryset using extra

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related