Queryset difference

I have one app with a class Navbar in models.py:

class Navbar(models.Model):
    node = models.ForeignKey(Node, ...)
    nav_link = models.ForeignKey(NavLink, ...)

and a class NavLink:

class NavLink(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField()

I have a second app with a class Intro in models.py:

class Intro(models.Model):
    node = models.ForeignKey(Node, ...)
    nav_link = models.ForeignKey(NavLink, ...)

In forms.py of the second app I have the following form:

from headers.models import Navbar, NavLink

class IntroForm(ModelForm):
    nav_link = ModelChoiceField(queryset=None, empty_label='Intro')

    class Meta:
        model = models.Intro
        fields = (
            'nav_link',
            ...,
        )

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        qs1 = Navbar.objects.filter(node_id=user.node_id)
        qs2 = models.Intro.objects.filter(node_id=user.node_id)
        qs1.difference(qs2)
        self.fields['nav_link'].queryset = NavLink.objects.filter(id__in=???)

What I want is the ModelChoiceField to contain only the choices that user.node_id hasn’t chosen yet.

The first issue is that name 'user' is not defined. Is there a way to get access to user.node_id in forms.py or should this code go elsewhere?

The second issue is that for id__in= I need a list with the nav_link ids, how do I make a list out of qs1?

Kind regards,

Johanna

I’m assuming that user in this case is referring to the user that is issuing the request for the page that will render this form.

If that’s the case, then you have a couple of different options.

  • You can pass the user from the view to the form as a parameter.
  • You can create the form and then filter the queryset attribute for that field in the view.

It’s your choice.

(If this isn’t the case, if you’re not looking to use the requesting user, then what user are you looking to filter by?)

qs1.values_list('id', flat=True)

Hi Ken,

I’ve chosen the first option, in the view I have:

def get_form_kwargs(self):
        kwargs = super().get_form_kwargs()
        kwargs.update({
            'user': self.request.user,
        })
        return kwargs

And in the form:

def __init__(self, *args, **kwargs):
        self.user = kwargs.pop('user')
        super().__init__(*args, **kwargs)
        qs1 = Navbar.objects.filter(node_id=self.user.node_id).values('nav_link')
        qs2 = models.Intro.objects.filter(node_id=self.user.node_id).values('nav_link')
        qs1.difference(qs2)
        self.fields['nav_link'].queryset = NavLink.objects.filter(id__in=qs1.values_list('nav_link', flat=True))

First question is, why do I have to put self.user = kwargs.pop('user') before super().__init__(*args, **kwargs).

Second question is, I now have querysets qs1 and qs2 which contain the following data:

<QuerySet [
{'nav_link': UUID('d4364a50-8eb7-4688-a960-1da4561035f6')},
{'nav_link': UUID('dfad7877-133f-4e93-bb3a-7a7203c2738a')}, 
{'nav_link': UUID('80aa5d2c-7311-4f18-9589-3c26e9fda06e')},
{'nav_link': UUID('290efbcd-d141-4973-8eeb-1c0d6db3f074')}, 
{'nav_link': UUID('04631286-ce4f-4450-9cb7-398c444d10af')},
{'nav_link': UUID('024854f5-30fa-4a74-b8ec-981d9aff6e86')}, 
{'nav_link': UUID('ab881362-a58a-434e-a062-a81057be2f58')}, 
{'nav_link': UUID('ad2b3c37-01a7-4848-994c-cbaa2bba8649')}, 
{'nav_link': UUID('4be8bf6e-a876-4dd6-8315-cd0f3009ddf1')}, 
{'nav_link': UUID('fad8ac37-35ad-4056-a622-9ddf56746999')}, 
{'nav_link': UUID('9a76b8a1-6d48-4934-a868-86062ba2186c')}, 
{'nav_link': UUID('fbad3428-e689-4a4c-a960-e6c6fe37c641')}, 
{'nav_link': UUID('e9359c16-9197-4ea3-a808-0ae94be91f3f')}]> 

<QuerySet [
{'nav_link': UUID('dfad7877-133f-4e93-bb3a-7a7203c2738a')}]>

The code qs1.difference(qs2) to get qs1 minus qs2 doesn’t work qs1 still contains {'nav_link': UUID('dfad7877-133f-4e93-bb3a-7a7203c2738a')}

I guess the problem is the list of dictionaries, difference() expects objects. However, I need the ‘nav_links’ in Navbar minus the nav_links in Intro, not the ids.

Is that possible?

Because the superclass of your form class isn’t expecting to be passed a parameter named user. So you need to remove it from the parameters being passed up the line.

Yes, you can remove the values clauses from qs1 and qs2.

If qs1 and qs2 are querysets, then qs1.difference(qs2) means that qs1 is still a queryset.

This means you can still reference qs1.values_list(...) in your filter.

Actually, upon second look, this appears to be redundant.

qs1 is the queryset you’re looking for.

You should be able to do:
self.fields['nav_link'].queryset = qs1.values_list('nav_link', flat=True)

Or, perhaps more accurately, since you’re setting the queryset attribute -
self.fields['nav_link'].queryset = qs1

When I remove values, qs1 contains:

<QuerySet [
<Navbar: Navbar object (e95d0096-df64-4b45-9bdc-0b3929e8ad0a)>,
<Navbar: Navbar object (65cadc2e-fafc-42f1-a883-fde5496ec740)>,
<Navbar: Navbar object (bc515283-c3db-43ce-b361-bc379ed92059)>,
<Navbar: Navbar object (c20bd14c-43d3-438e-9b1a-06737192ca67)>,
<Navbar: Navbar object (966b85a5-b3c4-4078-8b24-902b29d3d61a)>,
<Navbar: Navbar object (7c08ca9a-5445-4b2b-876f-19bc32459da9)>,
<Navbar: Navbar object (ccdf1bad-8c90-4a59-a1c6-f99bca339c77)>,
<Navbar: Navbar object (d0dcd59a-0a63-4f84-9791-85257e425448)>,
<Navbar: Navbar object (043c793e-779c-4e27-8283-3f8f753bdeaf)>,
<Navbar: Navbar object (f6df24e2-fcaf-4155-8d46-9112d86e5ced)>,
<Navbar: Navbar object (a80a76a7-1148-4a40-a2dc-448f4563d52b)>, 
<Navbar: Navbar object (23b3619d-c43d-43e1-84f3-ccefd5d31a03)>, 
<Navbar: Navbar object (6265ec5f-044e-4b53-b50e-2221a486b24a)>]>

These are the primary keys of Navbar
qs2 contains:

<QuerySet [<Intro: The names and faces behind our work>]>

This is what __str__ returns

def __str__(self):
    return self.headline

What I want is to take the difference based on the nav_link field in both classes:

class Navbar(models.Model):
    node = models.ForeignKey(Node, ...)
    nav_link = models.ForeignKey(NavLink, ...)

class Intro(models.Model):
    node = models.ForeignKey(Node, ...)
    nav_link = models.ForeignKey(NavLink, ...)

I get the feeling this is not possible, is it?

First a question - is it only the nav_link field needing to be excluded? Or is it the combination of nav_link and node? My reading from earlier posts is that it’s actually both. If not there may be an easier solution than what I’m presenting here.

Under the assumption that is is both fields, there are a handful of issues in play here, let me see if I can break this down some.

First, you’re looking at the text representation of those querysets, which isn’t necessary representative of the internal structure of the data, and is not how the difference function works.
Internally, what the difference function effectively does is compare the values of the fields within the model. According to the docs at QuerySet API reference | Django documentation | Django, as long as the two models have the same data type for their fields in the same order, the comparison is “valid”.

Unfortunately, that comparison will include the primary key of the two tables, and that is what’s causing this comparison to not yield the desired results. If the id column in Navbar corresponded to the id column in Intro for a given (node, nav_link) pair, then this would work as expected. (Which gives me an idea - more below)

Second, and of a more minor point, the difference function does not alter a queryset in place - it returns an altered queryset.

In other words, qs1.difference(qs2) does not change qs1. The statement would need to be qs1 = qs1.difference(qs2).

Beyond these two issues, yes, I would be looking at structuring this query differently.

I’m not seeing a way to do this directly using the ORM. (I’ve tried a few things in my test environment using Subquery, but nothing is giving the desired results.) So I probably would resort to doing the difference in Python.
e.g.

# Construct the list of (node, nav_link) items to exclude from the NavBar list
exclusions = [
  (intro.node_id, intro.nav_link_id) 
  for intro in Intro.objects.filter(node_id=self.user.node_id)
]
# Construct the list of NavBar items not in the exclusion list
nav_items = [
  navbar 
  for navbar in Navbar.objects.filter(node_id=self.user.node_id)
  if (navbar.node_id, navbar.nav_link_id) not in exclusions
]

and now nav_items is a list (but not a queryset) of the items in Navbar that are associated with self.user and which do not have a corresponding entry in Intro.

Since I believe the end desired result is a list of 2-tuples for a Select field, you could replace that first “bare” navbar in the list comprehension with the specification of the 2-tuple desired.

I think this gets you closer.

Thanks for your extensive reply, it’s very helpful.

Navbar contains Navbar items for lots of nodes and Intro contains Intros for lots of nodes. When the logged in user manages node 6, I query the database for the Navbar items and Intros of node 6.

In case of the following nav_links

name           |  id
____________________________
Home           | 1
Who we are     | 2
What we do     | 3
Events         | 4
News           | 5

Let’s say for Navbar I get these ids [1, 2, 3, 4], and for Intros [1, 2]. This means node 6 added intros for home and who we are and has two options left to add intros for what we do and events ids [3, 4]

The ids and their names are stored in NavLink

class NavLink(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField()

In the IntroForm I want the select for nav_link to only show the available options:

<select>
  <option value="3">What we do</option>
  <option value="4">Events</option>
</select>

Navbar and Intro both have their own id, there is no correspondence. The only correspondence is that both nav_link in Navbar and nav_link in Intro reference the id in NavLink.

It does indeed get me closer, thank you.

Hi Ken,

Problem solved:

def __init__(self, *args, **kwargs):
    self.user = kwargs.pop('user')
    super().__init__(*args, **kwargs)
    exclusions = [intro.nav_link_id for intro in models.Intro.objects.filter(node_id=self.user.node_id)]
    nav_links = [navbar.nav_link_id for navbar in Navbar.objects.filter(node_id=self.user.node_id) if navbar.nav_link_id not in exclusions]
    self.fields['nav_link'].queryset = NavLink.objects.filter(id__in=nav_links)

Thanks for your help.

I’ve got a more complex use case. Every nav_link has one intro, the code above solves that.

However, a nav_link can have three features, is it possible to group and count the nav_links and in case there are three add the nav_link to exclusions.

I had a look at aggregation, but I’m not sure this solves the problem.

Kind regards,

Johanna

I don’t think I’m following you here.

It seems to me that you’re saying that this line:
exclusions = [intro.nav_link_id for intro in models.Intro.objects.filter(node_id=self.user.node_id)]
needs to be modified to provide other tests for the nav_link_id to be added to the exclusions list. Am I correct?

If so, the easiest way to extend/modify this is may be to convert this list comprehension back in to a normal Python loop:

exclusions = []
for intro in Intro.objects.filter(...):
    if ...:
         exclusions.append(intro.nav_link_id)

(Otherwise, I’m not sure what you’re asking here.)

In SQL this is what I want:

SELECT nav_link_id, COUNT (nav_link_id)
FROM elements_feature
GROUP BY nav_link_id
ORDER BY COUNT (nav_link_id);

Where elements is the app name and feature is the class name.

Is this possible in Django?

Ah, ok - “Feature” is a model with a field named “nav_link” being a ForeignKey to Navbar. That’s what I was missing before.

You can use annotations with a filter to identify which ones have three Feature relating to them using a query like:
three_features = Navbar.objects.annotate(feature_count=Count('feature')).filter(feature_count=3)

Hi,

That solves the problem. One more question. It appears that this:

nav_link = ModelChoiceField(queryset=None, empty_label='Page')

Results in an error:

Exception Value:	
'NoneType' object has no attribute '_prefetch_related_lookups'

in case the queryset I assign to the field `nav_link’ is empty:

self.fields['nav_link'].queryset = NavLink.objects.filter(id__in=nav_links).order_by('order_by')

This error occurs during template rendering:

{% else %}
   {% crispy_field field 'class' 'form-select' %}
{% endif %}

Is there a way to solve this issue?

What do you want to have happen if the queryset is empty?

Do you want to not render that field? Do you want to apply some “default”?

Good question, I didn’t look at the issue from that perspective.

When there are no options to render, I don’t need the field to be rendered. So, that’s what I want to happen.

You’ve got a couple different ways to handle this - and your choice may depend, in part, upon other factors such as whether that field is required.

For example, you showed a fragment of a template:

what’s the larger block in your template that include this portion?

This is the else clause of some if tag. What is being checked here?

The reason I’m asking is that one option would be for you to also check to see if that field’s queryset is empty, and if it is, not render that field.

That’s one approach.

Another approach would be to add the code in your view to determine if the queryset is empty, and if so, remove the field from the form. (This would actually be my choice, as I prefer having as little logic in the template as possible.)

That’s what I prefer to.

Since I already have the queryset in forms.py, is it possible to add/remove the field to/from the form there? This is the code in forms.py:

class FeatureForm(ModelForm):
    nav_link = ModelChoiceField(queryset=None, empty_label='Page')

    class Meta:
        model = models.Feature
        fields = (
            'nav_link',
            'title',
            'text',
            'order_by',
            'active',
        )

    def __init__(self, *args, **kwargs):
        self.user = kwargs.pop('user')
        super().__init__(*args, **kwargs)
        if kwargs['instance']:
            exclusions = [
                feature['nav_link_id'] for feature in
                models.Feature.objects.filter(node_id=self.user.node_id).values('nav_link_id').annotate(counter=Count('nav_link_id'))
                if (feature['counter'] >= 3) and (feature['nav_link_id'] != self.instance.nav_link_id)
            ]
        else:
            exclusions = [
                feature['nav_link_id'] for feature in
                models.Feature.objects.filter(node_id=self.user.node_id).values('nav_link_id').annotate(counter=Count('nav_link_id'))
                if feature['counter'] >= 3
            ]
        nav_links = [
            navbar.nav_link_id for navbar in
            Navbar.objects.filter(node_id=self.user.node_id)
            if navbar.nav_link_id not in exclusions
        ]
        self.fields['nav_link'].queryset = NavLink.objects.filter(id__in=nav_links).order_by('order_by')

What if I make fields a list instead of a tuple without the field nav_link:

fields = [
    'title',
    'text',
    'order_by',
    'active',
]

and then:

if nav_links:
    self.fields.insert(0, 'nav_link')
    self.fields['nav_link'].queryset = NavLink.objects.filter(id__in=nav_links).order_by('order_by')

That won’t quite work because there are other attributes of the fields that are initialized in __init__ that wouldn’t be initialized by adding the field later, if you don’t explicitly initialize them.

I think in general you would be better off by defining the field as normal, and deleting it if it turns out to be unnecessary.

What I was thinking of is more along the lines of:

if nav_links:
    self.fields['nav_link'].queryset = NavLink.objects.filter(id__in=nav_links).order_by('order_by')
else:
    del(self.fields['nav_link'])

Problem solved.

Thanks, I very much appreciate your help.