Oleksandr Dashkov Oleksandr Dashkov - 8 months ago 39
SQL Question

How to use @property in raw sql or add alias for the django ORM query

I used SQL query like this:

SELECT ns.id, ns.title, ns.image,
ns.briefDescr as description, ns.url,
um.team_id as team
FROM app_news ns
INNER JOIN app_news_teams nsts
ON ns.id = nsts.news_id
INNER JOIN app_userteam um
ON nsts.team_id = um.team_id
WHERE um.user_id = %s AND ns.isActive = 1 GROUP BY ns.id

But I need to add some more logic for the field 'image', I decided to use decorator @property to the model:

def image(self):
'''some logic here'''
return self._image

But now sql code doesn't work. Also I tried annotation to add 'um.team_id as team'. Could you please give me some idea have can I use @property and save the current structure of sql response (id, title, image, url, description and team)?

1)I run query in the view as raw query.

articles = News.objects.raw(sql, [sid, ])

As the result I want to execute sql query or orm query and save current structure of response but with the function field 'image'

2)When I run this sql I have an exception "ns.image" doesn't exist

# model before
class News(models.Model):
"""other fields """
image = models.ImageField(upload_to='images/news', blank=True)

# model now
class News(models.Model):
"""other fields """
_image = models.ImageField(upload_to='images/news', blank=True)

def image(self):
"""some logic here"""
return self._image


So it's not possible to use sql and not orm query with the @property. TO save the same structure of response I changed the model by adding needed values using @property for e.g.:

def team(self):
    # get team...
    return team