iterate over two models as one view

Using Django Using the ORM
1

Hi,
I’m trying to create a page where users can see their databases privileges and can request more privileges
below are the models that are involved: (there are some more who are used as the foundations of these but are I dont thing are relevant to my question)
what I would like to have in my site is a table that contains the following

  • for the logged in user in the page of a spefic database of a specific Rds_Database
  • list of all schema
  • for each schema have two extra columns with READ and WRITE privileges of the logged-in user
  • I can get the data into two querysets but not sure how to use them as one. (join them when iterating)
  • but not sure how to generage one table with combined data from two models
  • simbolises a button that when pressed will generate a request for the relevent privs

somthing like this:

image
image789×220 11.7 KB

-------------

.models.py

-------------

class Schema
primary key (database/schema_name)

class Schema(models.Model):
  schema_name = models.TextField(max_length=50)
  rds_database = models.ForeignKey(Rds_Database, on_delete=models.CASCADE)
  owner = models.CharField(max_length=100)

  class Meta:
    unique_together = ('schema_name', 'rds_database')

  def __str__(self):
    return f'{self.schema_name}:{self.rds_database} '

class Rds_User
primary key (rds/db_user)

class Rds_User(models.Model):
  username = models.TextField(max_length=50)
  wings_user = models.ForeignKey(User, on_delete=models.CASCADE)
  rds = models.ForeignKey(Rds, on_delete=models.CASCADE,db_column='db_instance_arn',to_field='db_instance_arn')

  class Meta:
    unique_together = ('username', 'rds' )

  def __str__(self):
    return f'{self.username}@{self.rds} '

class user_schema_privs
primary key (database/schema_name)

class User_Schema_Privs(models.Model):
  PrivilegeType = models.TextChoices('PrivilegeType', 'READ WRITE')
  schema = models.ForeignKey(Schema, on_delete=models.CASCADE)
  user = models.ForeignKey(Rds_User, on_delete=models.CASCADE)
  privilege = models.CharField(blank=True,choices=PrivilegeType.choices, max_length=10)
  granted_via_team = models.CharField(max_length=20)
  granted_role = models.CharField(max_length=100)

  class Meta:
    unique_together = ('schema', 'user', 'privilege' )

  def __str__(self):
    return f'{self.privilege} for {self.user} on {self.schema} '

-------------

.views.py

-------------

class RdsDatabaseDetailView(DetailView):
  model = Rds_Database

  def get_context_data(self, **kwargs):
    context = super().get_context_data(**kwargs)
    user = self.request.user
    rds_users=Rds_User.objects.get(rds=the_database.rds,wings_user_id=user.id)

    db_pk = self.kwargs.get('pk', None)
    the_database=Rds_Database.objects.get(id=db_pk)
    context['schema_list']=Schema.objects.filter(rds_database=the_database)
    context['privs'] = User_Schema_Privs.objects.filter(schema__in = context['schemas'],user=rds_users.id)
    
    return context
2

The set of User_Schema_Privs are references from an instance of Schema is through the use of the related object manager.

This means that if you have an instance of Schema named schema, then you can access that set as schema.user_schema_privs_set. Since this is a manager and not a queryset itself, you then need to apply one of the queryset functions to it.

e.g.
schema.user_schema_privs_set.all() returns all members of that set. schema.user_schema_privs_set.filter(user=some_user) would only return those elements also related to a specific user (some_user).

Since this only returns rows that exist, it would be up to you to “merge” this information with your queryset in your view.

You could also do this by adding annotations to your original query, containing a subquery to check to see if a particular user has a privilege on a schema and set those values in the annotation using a conditional expression.
(This is probably how I would do it - I’d rather let the database do the work.)

3

@KenWhitesell
first of all thank you.
second, dont you ever sleep?
now for business.
I did some work with your guidence and came up with somthing that seems to work and appriciate your thoughts on it.

local_user='user@email.com'
local_db_instance_identifier='my_db'
local_database='my_database'

from db_helper.models import Rds,Rds_Database,Schema,Rds_User,User_Schema_Privs
from django.db.models import Count,Case,When,BooleanField,IntegerField,Q
from django.contrib.auth.models import User
user=User.objects.get(username=local_user)
my_rds=Rds.objects.get(db_instance_identifier=local_db_instance_identifier)
the_database=Rds_Database.objects.get(rds=my_rds,database=local_database)
schemas=Schema.objects.filter(rds_database=the_database)

rds_users=Rds_User.objects.get(rds=the_database.rds,wings_user_id=user.id)

data=Schema.objects.filter(id__in = schemas).annotate(
  has_read=Count(Case(
    When( Q(user_schema_privs__privilege='read') & Q(user_schema_privs__user=rds_users.id ) , then=True),
    output_field=IntegerField(),)),
  has_write=Count(Case(
    When( Q(user_schema_privs__privilege='write') & Q(user_schema_privs__user=rds_users.id ) , then=True),
    output_field=IntegerField(),)),
  )

for i in data:
  print(i.has_read,i.has_write,i.schema_name)
4

I’d only have one suggestion to make - add a default value to each of the Case statements to ensure you have a value for each of those annotations when the When clause isn’t True. (That’s instead of using the Count function if I’m understanding what you’re using it for.)

5

I later added try and except when user is missing some of the data and I’ll try your suggestion.
thanks again.