I can't create django queryset for this sql query

Question

sql query:

SELECT *
FROM "notification_notification" AS T0
  LEFT JOIN (SELECT *
             FROM "notification_usernotification"
             WHERE user_id = 1) AS T
    ON (T0.id = T.notification_id)

models:

class Notification(models.Model):
    subs_code = models.CharField()
    subs_name = models.CharField()
    users = models.ManyToManyField(settings.AUTH_USER_MODEL,
                                   through='UserNotification')

class UserNotification(models.Model):
    notification = models.ForeignKey(Notification)
    user = models.ForeignKey(settings.AUTH_USER_MODEL)
    push_message = models.BooleanField()

Is it possible?
i try various technique, but i can't create that simple sql under django ORM;

notification_notification table AS T0
|---id---|-----subs_code-----|-----subs_name-----|
|---1----|-----system----------|----system------------|
|---2----|-----broadcast------|-----broadcast-------|
|---3----|-----not_need-------|-----not_need-------|

notification_usernotification table AS T1
|---id---|-notification_id-|-user_id-|-push_message-|
|---11--|---------1----------|----1------|--------true---------|
|---12--|---------2----------|----1------|--------false--------|
|---22--|---------2----------|-----2-----|--------true---------|

i use left join for that result:
Result:
|-T1.id-|-subs_code-|-subs_name-|-T1.id-|-notification_id-|-user_id-|-push_message-|
|---1----|---system----|---system-----|---11--|------------1-------|---1-------|----true-------------|
|---2----|---broadcast|---broadcast--|--12---|------------2-------|---1-------|----false-----------|
|---3----|---not_need-|---not_need--|--null-|---------null--------|---null----|----null-------------|

INNER JOIN is not valid there are ((
sqlfiddle

i think it possible only for raw sql

[`select_related()`?](https://docs.djangoproject.com/en/1.8/ref/models/querysets/#select-related) — Ross Rogers, Nov 19 '15 at 16:53
i need left join with existing queryset. select_related not use, i now try this — madjardi, Nov 19 '15 at 17:12
Notification.objects.all().select_related('usernotification').filter(usernotification__user__id=1) — madjardi, Nov 19 '15 at 17:31
`user_nitification = UserNotification.objects.filter(user_id=1)` `notifications = Notification.objects.filter(users=user_nitification)` — Yuv, Nov 20 '15 at 07:17

score 0 · Answer 1 · edited May 23 '17 at 12:30

it write raw sql query.
it write by extra: see in here stackoverflow

Notification.objects.extra(  
    select={  
            'push_message':  
                'SELECT {tbl_1}.push_message::BOOLEAN FROM {tbl_1} '  
                'WHERE {tbl_1}.notification_id = {tbl_2}.id '  
                'and {tbl_1}.user_id = %s'.format(  
                    tbl_1=UserNotification._meta.db_table,  
                    tbl_2=Notification._meta.db_table)},  
        select_params=(user.id,))

I can't create django queryset for this sql query

1 Answers1