Django select rows with duplicate field values for specific foreign key

Question

again I would like to search for duplicates in my models, but now slightly different case.

Here are my models:

class Concept(models.Model):
    main_name = models.ForeignKey(Literal)
    ...
class Literal(models.Model):
    name = models.Charfield(...)
    concept = models.ForeignKey(Concept)
    ...

And now the task I'm trying to achieve: Select all literals that are NOT main_names, that have the same name for the same concept.

For example if I have literals:

[{id:1, name:'test', concept:1}, {id:2, name:'test', concept:1}]

and concepts:

[{id:1, main_name:1}]

Then in result I should get literal with the ID=2.

Gareth Rees · Accepted Answer · 2012-02-12 18:32:54Z

1

It sounds to me as though you want to execute a SQL query something like this:

SELECT l1.* FROM myapp_literal AS l1,
                 myapp_literal AS l2
WHERE l1.id <> l2.id
  AND l1.name = l2.name
  AND l1.concept = l2.concept
  AND l1.id NOT IN (SELECT main_name FROM myapp_concept)
GROUP BY l1.id

Well, in cases where the query is too complex to easily express in Django's query language, you can always ask Django to do a raw SQL query—and this may be one of those cases.

edited Feb 12, 2012 at 18:32

answered Feb 12, 2012 at 18:23

Gareth Rees

66.1k10 gold badges139 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dragoon Over a year ago

Yes, I think I gives exactly what I want, except that you need to specify l1.id instead of l1.*, otherwise it will be column must appear in the GROUP BY clause or be used in an aggregate function

dragoon Over a year ago

could you also tell me what should I write do delete everything selected from l1?

Gareth Rees Over a year ago

I guess I'm too used to MySQL, which allows non-aggregated columns in the SELECT part of GROUP BY queries. You're quite right to point out that in most databases this won't be allowed.

Gareth Rees Over a year ago

To delete the records matching a query, you can use Django's delete method on the query. Something like Literal.objects.raw('SELECT ...').delete() But take care—the query I wrote selects all records with duplicates—you probably want to delete all but one of each matching set.

Burhan Khalid · Accepted Answer · 2012-02-12 18:07:50Z

1

If I understand your question you want:

All Literal objects that are not ForeignKey'd to Concept.
From that set, select those where the name and the concept is the same.

If so, I think this should work:

For the first part:

q = Literal.objects.exclude(pk__in=Concept.objects.values_list('id', flat=True))

EDIT:

Based on excellent feedback from Jan, I think for #2 you would need to use raw SQL.

edited Feb 12, 2012 at 18:07

answered Feb 12, 2012 at 17:26

Burhan Khalid

175k20 gold badges255 silver badges292 bronze badges

6 Comments

Jan Pöschko Over a year ago

It should be values_list and I think your second part doesn't do anything. Do you mean q.filter(name=F('concept__main_name__name'))? I'm not sure if this is optimal in performance though.

Burhan Khalid Over a year ago

I'm not sure about the performance part to be honest; but I think the F expression is correct as per my understanding of the docs. Thanks for the values_list, edited.

Jan Pöschko Over a year ago

Does it not just test whether the literal's name equals its own name (same for concept), which is of course true for all literals?

Jan Pöschko Over a year ago

OK. I posted a possible solution in my comment and think that it does actually work, without using raw SQL but foreign-key lookups.

dragoon Over a year ago

Yep, I know have to select all not main literals, this is fairly simple, my insterest was how to perform the whole task. Thanks for the help anyway.

|

Collectives™ on Stack Overflow

Django select rows with duplicate field values for specific foreign key

2 Answers 2

4 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related