I have the following classes in my models.py

class Users(models.Model):
    username = models.CharField('User Name', null=False, max_length = 50)
    password = models.CharField('User Password', max_length=50)

    def __str__(self):
        return self.username

class Photo(models.Model):
    name = models.CharField(max_length=100, default="Image")
    id = models.AutoField(primary_key=True)
    uploader = models.ForeignKey(Users, default=None, on_delete=models.CASCADE)

    def __str__(self):
        return self.name

class Photo_History(models.Model):
    image = models.ImageField(upload_to="media/results")
    upload_time = models.DateTimeField(auto_now_add=True, blank=True)
    photo_id = models.ForeignKey(Photo, null=True, blank=True, on_delete=models.CASCADE)

Table Structure is as follows,

For the Users Class,

For the Photo Class,

For the Photo History Class,

What I am trying to access is, a user is logged in. I need to get the Photo History Objects along with the "name" from the Photo model of the user who's currently logged in.

So, let's say if the user abc is logged in, then it should display the following Results,

I tried to search a way to write a command using filter function to get this but I couldn't find any results that showed how you can get data from two different tables in a single query. And instead, people mentioned I should be using raw() query.

So I used the raw query which I believe is totally fine, but I am getting a weird error (which you get if you haven't done in your migrations, but my migrations were done long ago and the column for which it is showing error, I can easily retrieve data from it in other queries)

This is the function that I have with the query,

def history(request):
    if request.session.has_key('is_logged'):
        current_user_id= request.session['user_id']
        q = "select p.name, h.image, h.upload_time from firstApp_Photo_History h, firstApp_Photo p where p.uploader=%s;"
        photos = Photo_History.objects.raw(q, [str(Users.objects.filter(username=current_user_id)[0])])
        
        print("Photos: ", photos)
        for p in photos:
            print(p.name)
            print(p.username)
            print(p.upload_time)
        return render(request, 'history.html', {'ID':current_user_id, 'photos':photos})
    else:
        return redirect('login')

When I try to run this, I get an error that

no such column: p.uploader

Can anyone tell me what's the issue and how can I resolve this?

Here's the Complete Error,

Quit the server with CTRL-BREAK.
Photos:  <RawQuerySet: select p.name, h.image, h.upload_time from firstApp_Photo_History h, firstApp_Photo p where p.uploader=hassan;>
Internal Server Error: /history
Traceback (most recent call last):
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\backends\utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\backends\sqlite3\base.py", line 416, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such column: p.uploader

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\core\handlers\exception.py", line 47, in inner
    response = get_response(request)
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\core\handlers\base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "E:\Hassan\Semester 8\Research\Example2\imageNetProject\firstApp\views.py", line 67, in history
    for p in photos:
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\models\query.py", line 1514, in __iter__
    self._fetch_all()
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\models\query.py", line 1501, in _fetch_all
    self._result_cache = list(self.iterator())
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\models\query.py", line 1524, in iterator
    query = iter(self.query)
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\models\sql\query.py", line 96, in __iter__
    self._execute_query()
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\models\sql\query.py", line 136, in _execute_query
    self.cursor.execute(self.sql, params)
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\backends\utils.py", line 99, in execute
    return super().execute(sql, params)
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\backends\utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\backends\utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\backends\utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\backends\utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "E:\Hassan\Semester 8\Research\Example2\venv\lib\site-packages\django\db\backends\sqlite3\base.py", line 416, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such column: p.uploader
[24/Apr/2022 04:14:04] "GET /history HTTP/1.1" 500 128502

CodePudding user response：

Going through the Django Documentation, I found that Django adds a _id at the end of foreign key name.

Hence, I was able to solve my issue by changing p.uploader to p.uploader_id. A weird issue but this is how the Django models work.

The Final Query was,

q = "select h.id, p.name, h.image, h.upload_time from firstApp_Photo_History h, firstApp_Photo p where p.uploader_id=%s;"