CODEWITHSUNDEEP
pythondjangosqlite
alicen
alicen

Reputation: 87

Nested SQL query in Django Views

So I searched and couldn't find any answer on this so far. I have a database where when I've put in:

SELECT event_name, team_number
FROM teams_by_event
WHERE team_number IN (SELECT team_number 
                    FROM teams_by_event 
                    WHERE event_name = '[user inputs event name]'
                    ) ORDER BY team_number;

It gives me exactly what I'm looking for in return. My problem is that I can't think of a way to do this in Django views, and what I've tried, just using the raw SQL in Django is not working either, I get a syntax error saying the last parenthesis on the SQL query is invalid.

views.py:

def teams_by_event(request, shorthand):
    code = TeamsByEvent.objects.filter(shorthand=shorthand)
    for event in TeamsByEvent.objects.raw('SELECT event_name, team_number \
                               FROM teams_by_event \
                               WHERE team_number IN \
                               (SELECT team_number \
                                FROM teams_by_event) \
                                WHERE shorthand= % s', [code])
    print (event.team_number, event.event_name)
    return render(request, 'event-info.html', {'info': info})

models.py

class TeamsByEvent(models.Model):
    team_number = models.IntegerField()
    event_name = models.CharField(max_length=50)
    shorthand = models.CharField(max_length=12)

    class Meta:
        db_table = 'teams_by_event'
        app_label = 'frcstats'

edit:: traceback:

Traceback (most recent call last):
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/core/handlers/base.py", line 149, in get_response
    response = self.process_exception_by_middleware(e, request)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/core/handlers/base.py", line 147, in get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/Users/alicen/git/first_robotics/frcstats/views.py", line 1034, in teams_by_event
    for event in TeamsByEvent.objects.raw(raw_query):
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/models/query.py", line 1219, in __iter__
    query = iter(self.query)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/models/sql/query.py", line 79, in __iter__
    self._execute_query()
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/models/sql/query.py", line 113, in _execute_query
    self.cursor.execute(self.sql, params)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 322, in execute
    query = self.convert_query(query)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 330, in convert_query
    return FORMAT_QMARK_REGEX.sub('?', query).replace('%%', '%')
TypeError: expected string or buffer

Upvotes: 1

Views: 1094

Answers (3)

Ankit Jaiswal
Ankit Jaiswal

Reputation: 23427

Seeing your table structure (i.e. model) and query, I'm pretty sure you do not need nested query at all. Your query:

SELECT event_name, team_number
FROM teams_by_event
WHERE team_number IN (SELECT team_number 
                    FROM teams_by_event 
                    WHERE event_name = '[user inputs event name]'
                    ) ORDER BY team_number;

should return exactly what the below query results:

SELECT event_name, team_number
FROM teams_by_event
WHERE event_name = '[user inputs event name]' ORDER BY team_number;

and equivalent Django query would be something like:

TeamsByEvent.objects.filter(event_name='your_event_name')

Upvotes: 1

jbacker
jbacker

Reputation: 150

It seems that you put ) at wrong position. Shouldn't it be like following?

def teams_by_event(request, shorthand):
    code = TeamsByEvent.objects.filter(shorthand=shorthand)
    raw_query = '''SELECT event_name, team_number
                   FROM teams_by_event
                   WHERE team_number IN (
                   SELECT team_number FROM teams_by_event
                   WHERE shorthand = % s)''' % [code]

    for event in TeamsByEvent.objects.raw(raw_query):
        print (event.team_number, event.event_name)

    return render(request, 'event-info.html', {'info': info})

Upvotes: 0

arcegk
arcegk

Reputation: 1480

Try to do two separated queries:

 subquery = TeamByEvent.objects.filter(event_name__icontains='input').\
values_list('team_number' , flat=True)

query = TeamByEvent.objects.filter(team_number__in=subquery)

Upvotes: 0

Related Questions