ForeignKey with m2m filter can duplicate foreign model entries in ModelForm/ModelChoiceField

[mattimustang@…]

My app has the following models similar to:

PROXY_GROUPS = ('Terminal Server', 'Socks Proxy', 'Web Proxy')

class Group(models.Model):
    name = models.CharField(maxlength=32)

class Asset(models.Model):
    name = models.CharField(maxlength=32)
    groups = models.ManyToManyField(Group)

class Proxy(models.Model):
    asset = models.ForeignKey(Asset, limit_choices_to={'groups__name__in': PROXY_GROUPS, 'distinct':True})
    port = models.PositiveIntegerField()

Which spews an error when I try to add a Proxy object in the admin. The error is something to do with 'distinct' not being a valid field (Sorry i can post the exact error later).

How can I achieve the equivalent to this in the current post-mr trunk?

From #django earlier today:

malcolmt	mattimustang_: I think you've found a bug. You're right, distinct has become a method on QuerySets, not a filter parameter now. So you may be temporarily stuck. Can you file a ticket so we don't forget to fix this somehow, please?

[anonymous]

Sorry, but sqlite does not enforce foreign keys at all, see ​http://www.sqlite.org/omitted.html

quoting:

SQL Features That SQLite Does Not Implement

Rather than try to list all the features of SQL92 that SQLite does support, it is much easier to list those that it does not. Unsupported features of SQL92 are shown below.
The order of this list gives some hint as to when a feature might be added to SQLite. Those features near the top of the list are likely to be added in the near future. There are no immediate plans to add features near the bottom of the list.

FOREIGN KEY constraints FOREIGN KEY constraints are parsed but are not enforced.

[anonymous]

Sorry, the previous text was meant for a different bug. Please ignore it ...

[jkocherhans]

I don't see how distinct=True makes any sense in the context of limit_choices_to. The result of limit_choices_to will have to include the primary key (that's the whole point of ForeignKey) so a SELECT DISTINCT will never return fewer rows than a plain old SELECT. I'm closing this for now. Please re-open it if you think I'm missing something.

[mattimustang@…]

Try this:

Create an Asset in admin (in the example models above) called proxy1.

Add both Socks Proxy and Web Proxy groups to it.

Then without distinct=True, when you go to create a Proxy object you see the proxy1 Asset twice in the dropdown in the admin. Using distinct=True in Pre-MR django reduced this to 1 proxy1 item in the dropdown.

[jkocherhans]

Ahhh I see now. groups__name__in is causing a join between the Proxy table and the Asset <-> Group many to many table. The extra Group objects are making extra distinct rows.

Trying to shoehorn all the new manager methods into kwargs is not going to be pretty. What if limit_choices_to was deprecated in favor of passing a QuerySet as the choices argument?

For example:

PROXY_GROUPS = ('Terminal Server', 'Socks Proxy', 'Web Proxy')

class Group(models.Model):
    name = models.CharField(maxlength=32)

class Asset(models.Model):
    name = models.CharField(maxlength=32)
    groups = models.ManyToManyField(Group)

class Proxy(models.Model):
    asset = models.ForeignKey(Asset, choices=Asset.objects.filter('groups__name__in': PROXY_GROUPS).distinct())
    port = models.PositiveIntegerField()

[mattimustang@…]

Using a a QuerySet is what I discussed with malcomt on #django but we didn't get too far with it. As long as it could handle the choices changing that would be great, ie. adding/removing assets from the PROXY_GROUPS. Would it only take a QuerySet or any callable?

[Malcolm Tredinnick <malcolm@…>]

I had a "doh!" moment about this bug in the shower this morning: we don't need to make "distinct" something that is specified by the user. We can work out precisely when it is required and just Do The Right Thing(tm).

If the limit_choices_to attribute adds a ForeignKey field to the query that does not have unique=True, then we should be using "select distinct". Otherwise, a simple "select" does the trick. There are some subtleties such as if we include a combination of fields that together comprise a "unique_together" block, then we don't need "distinct" any longer (because it is back to being unique by default).

Although the explanation looks complex, the code is probably not going to be too bad (although adding anything to django/db/models/query.py makes my head hurt). I'll look at writing a patch for this when I get some time (probably on the weekend, since it's queued up behind some other Django work).

[mattimustang@…]

Would it make sense to combine 'choices' and 'limit_choices_to' into one field option?

[Gary Wilson <gary.wilson@…>]

Replying to jkocherhans:

What if limit_choices_to was deprecated in favor of passing a QuerySet as the choices argument?

This would be cool.

[Chris Beaven]

Tagging for queryset refactor - maybe something that Malcolm wants to look at. I also like the idea of deprecating limit_choices_to and adding the option of passing a QuerySet to choices

[Alex Gaynor]

Patch against qs-rf

[Alex Gaynor]

I have attached a patch which should allow you to pass either a callable or a QuerySet to the choices parameter of a relational field, my one concern however is that it is not possible to use the limit/offset syntax with this because the QuerySet is evaluated and that would cause the queryset to be evauated at load time, rather than each time the choices need to be generated.

[Gary Wilson]

On newforms-admin, you can use ModelAdmin.get_form to return a custom from with a custom model choice field that makes use of the queryset argument.

[Malcolm Tredinnick]

That solution only applied to admin. limit-choices-to is used for more than just the admin interface... it's useful wherever you extract the list of options for a ForeignKey.

[Łukasz Rekucki]

#11707 is a bit related.

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Chris Wilson]

Created a patch and test in a ​pull request.

[Tim Graham]

I don't think the overhead of applying distinct in cases where it's not needed is ideal. We should either add logic so that it's not called when it's not needed (as suggested by Malcolm above), or implement a solution that will solve this by allowing a custom queryset to be used for choices (e.g. #13181 or #2445).

[Josh]

Can we add an optional keyword argument to conditionally apply the DISTINCT select? This just bit me on a production app.

Maybe something like:

account_manager = models.ForeignKey(
    User,
    limit_choices_to=Q(groups__name='Sales') | Q(groups__name='Admin'),
    limit_choices_distinct=True,  # default False
)

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 556fa4bb:

Fixed #1891, Fixed #11707 -- Prevented duplicates with limit_choices_to on multi-value relations.