0

I currently have Django models like this

MyFirstObject(models.Model): 
    some_field = models.BooleanField(default=False)

MySecondObject(models.Model):
    first_object = models.ForeignKey(MyFirstObject, db_column='firstObjectId')

Because of various issues, our data integrity is corrupt. So, I need to find instances where MyFirstObject has been deleted, but MySecondObject still has a row w a foreign key to it.

The database would look similar to:

TABLE my_first_object
id   someField
1    a
2    a
3    b

TABLE my_second_object
id  firstObjectId
1    1
2    3
3    4

Notice row 3 of the TABLE my_second_object has an firstObjectID that does not have a corresponding record in the my_first_object table. I want to find all instances like that.

If I was doing raw SQL, I would do

SELECT my_second_object.id, my_second_object.firstObjectId 
FROM my_second_object 
LEFT JOIN  ON ( my_second_object.firstObjectId = my_first_object.id )
WHERE my_first_object.id IS NULL

In Djago, I am trying

MySecondObject.objects.filter(my_first_object__id__isnull=true)

But when I look at the query that results, it is doing an inner join instead of left join. Does anyone have suggestions? Thanks!

Herb Girl
  • 1
  • 1
  • 1
    Check http://stackoverflow.com/questions/21271835/left-join-django-orm – Ahmed Hosny Jan 05 '17 at 00:21
  • @AhmedHosny, yes, I actually saw that when I first tried to google an answer. But I am not seeing how the solution that is offered in that post is different from the filter I am trying?? – Herb Girl Jan 05 '17 at 00:31
  • It filters with the First model not the second. The answer there saying that Left Join will be done when you do the following: MyFirstObject.objects.filter(mysecondobject__isnull=True). – Ahmed Hosny Jan 05 '17 at 00:39
  • MyFirstObject.objects.filter(mysecondobject__isnull=True) would give me a set of answers that is different from what I am trying to query. I was researching more, and came across this. Behind the scenes, Django does a JOIN. Its type (INNER or LEFT OUTER) is decided based on whether the left-hand column (your ForeignKey field) is allowed to be NULL or not. https://groups.google.com/forum/#!topic/django-users/CjHVVmu-00c – Herb Girl Jan 05 '17 at 00:55

1 Answers1

0

Try like this: 

first_object_ids = MyFirstObject.objects.values_list('id')
get_second_objects = MySecondObject.objects.exclude(my_first_object_id__in = first_object_ids)
ruddra
  • 50,746
  • 7
  • 78
  • 101