perfoming right join with django queryset

Question

I just need to apply right join with query set.

vdata = (VisVisitData.objects.
         .select_related('vpvalue','vparameter')
         .filter(vpvalue__vparameter=9,)
         .values(name=F('vpvalue__parameter_value'),
                 visit_day=F('visit__visit_day'),
                 question=F('vparameter'),
                 value_id=F('vpvalue'))
        .annotate(data=Count('vpvalue_id'))
        .order_by('visit__visit_day'))

above code generate following join statement.

FROM vis_visit_data INNER JOIN vis_visit_parameter_values ON (vis_visit_data.vpvalue_id = vis_visit_parameter_values.vpvalue_id) LEFT OUTER JOIN vis_visits ON (vis_visit_data.visit_id = vis_visits.visit_id)

But I need to do right join instead of Inner Join with vis_visit_parameter_values and vis_visit_data table. below is the snapshot of sql in which I want to make changes.

INNER JOIN vis_visit_parameter_values ON (vis_visit_data.vpvalue_id = vis_visit_parameter_values.vpvalue_id)

Model classes (using these 3 models in query set

class VisVisitParameterValues(models.Model):
    vpvalue_id = models.IntegerField(primary_key=True)
    vparameter = models.ForeignKey('VisVisitParameters', models.DO_NOTHING,related_name='values')
    parameter_value = models.TextField(blank=True, null=True)

class VisVisits(models.Model):
    visit_id = models.IntegerField(primary_key=True,auto_created=True)
    app_local_id = models.IntegerField(blank=True, null=True)
    visit_day = models.IntegerField(blank=True, null=True,db_column='visit_no')

class VisVisitData(models.Model):
    vdata_id = models.IntegerField(primary_key=True,auto_created=True)
    app_local_id = models.IntegerField(blank=True, null=True)
    visit = models.ForeignKey('VisVisits', models.DO_NOTHING, blank=True, null=True, related_name='data')
    vparameter = models.ForeignKey('VisVisitParameters', models.DO_NOTHING, blank=True, null=True,related_name='parameters')
    vpvalue = models.ForeignKey('VisVisitParameterValues', models.DO_NOTHING, blank=True, null=True,related_name='parameters_values')

Not a proper answer but in the worst case you can always make a raw SQL query (https://docs.djangoproject.com/en/2.1/topics/db/sql/) — bruno desthuilliers, Mar 14 '19 at 09:38
Possible duplicate of [Performing a right join in django](https://stackoverflow.com/questions/27510177/performing-a-right-join-in-django) — webbyfox, Mar 14 '19 at 09:44
@webbyfox please re-read my comment - I'm not advising to use raw SQL if there's a way to get the same result with ORM features, I just mentions that _at_ worst_ (=> "if there's no other way") there's _at least_ this option - which actually exists for the very reason that Django ORM has not be designed to be a complete SQL reimplementation but to make the most common 80% stuff dead simple. — bruno desthuilliers, Mar 14 '19 at 09:46
@webbyfox Have added the model class as well. and I already have gone through the link that you shared. That does not fix my issues. — Raju Singh, Mar 14 '19 at 09:51
`VisVisitParameters` model is missing. Also your final goal is unclear. What do you want to do with what data? A right join cannot work in Django, where joins are implemented by adding fields (=right-hand side) to objects (=left-hand side). If the left-hand side is None, you have nowhere to attach the right-hand side objects. But right joins being mirrored left joins, you just need to swap left and right for your query. How this can be done in detail depends on what fields you need and what you would use them for. — Endre Both, Mar 14 '19 at 11:04

score 0 · Answer 1 · answered Mar 14 '19 at 13:15

0

Have you tried using:

.filter(Q(vpvalue__isnull=True) | Q(vpvalue__vparameter=9))

answered Mar 14 '19 at 13:15

schillingt

13,493
2
32
34

perfoming right join with django queryset

1 Answers1