ORM help with INNER JOIN and GROUP BY

[b...@tanners.org]

I've inherited an application written django 1.11. The old application uses raw() with INNER JOIN and GROUP BY. 

 I cannot seem to figure out how to do inner join and group by properly the ORM way.

The raw() query is below.

SELECT * FROM items_monolithic

INNER JOIN items_facepng

ON items_monolithic.object == items_facepng.obj

GROUP BY items_monolithic.object

ORDER BY object ASC

Things kind of work with raw() but that doesn't feel right. And I get nasty warnings about RawQuerySet not supporting certain things when I try to use the query set that is returned.

From what I understand every monolithic object has 1 or more faces (graphic/picture).

I would call the monolithic a one-to-many relationship with facepng but I see django calls this a ForeignKey.

I'm working with these models (yes, a field named object is "bad", it's what I was given)

class Monolithic(models.Model):

object = models.CharField(max_length=128, blank=False, null=False, unique=True)

class FacePng(models.Model):

obj = models.CharField(max_length=128, blank=True, null=True)

I do not see the ForeignKey relationship between Monolithic and FacePng. 

Changing Monolithic class to models.ForeignKey() breaks lots of things.

So I'd prefer to figure out how to do the inner join and group by query but if that's not the django way and I need to change Monolithic.objects to a ForeignKey() and fix all the stuff that is broken I can do that that too. 

Just need some guidance on how to proceed.

[Matthew Pava]

That design could definitely be improved, and it will have to be in order to use the ORM effectively. Then you’d have to change every reference to the fields in all the raw querysets in the app.

 

You need a ForeignKey relationship between the two models, which is an integer value, not a char. You’d have to do migrations to get this adjusted properly.

 

Add a facepng_id Integer Field to Monolith. Run makemigrations. Add a RunSQL command to the new migrations file (https://docs.djangoproject.com/en/2.2/ref/migration-operations/#runsql).  Something like this:

Update items_monolith SET facepng_id=items_monolith.id FROM items_monolith INNER JOIN items_facepng ON items_monolith.object=items_facepng.obj

 

Then delete the object and obj fields from the models. Run makemigrations again. Verify that your app doesn’t reference those fields anywhere else.

 

Then you could access facepng like so:

monolithic.facepng_set

 

Of course, this is just a very rough idea of what to do, and I’m not sure what the whole structure of your tables is.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/ab7ecf5b-9ae8-4428-9502-6b7d5dec03b5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kevin Jay]

The field within your class should be set to ForeignKey.

Try this:

Object = models.ForeignKey(‘FacePng’, on_delete=SET_NULL, max_length= some_int)

on_delete and max_length need to be set based on your requirements 

Sent from my iPhone

[b...@tanners.org]

Just want to make sure I understand. ForeighKeys need to be integers?

Only integers?

[Matthew Pava]

Well, I had always assumed that they do need to be integers, but it looks like this has been discussed at length.

https://groups.google.com/forum/#!topic/django-users/0utRzn98Wxo

 

It does seem clear, though, that the superior database design uses integers for ForeignKeys.

And I didn’t find a way to make it clear to Django not to use an Integer ForeignKey.  Maybe something to do with the primary_key attribute on a CharField?

--

You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9697de34-3d20-4023-81a7-8ea9f257f7f1%40googlegroups.com.

[Kevin Jay]

Maybe that was a typo?

The foreignkey relationship would look like this:

object = models.ForeignKey(FacePng'', max_length=128, blank=False, null=False, unique=True)

--

You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9697de34-3d20-4023-81a7-8ea9f257f7f1%40googlegroups.com.

[b...@tanners.org]

class Monolithic(models.Model):

facepng_id = models.IntegerField(blank=True, null=True)

facepng_id as Integer not ForeignKey?

> UPDATE items_monolithic SET facepng_id=items_monolithic.id FROM items_monolithic INNER JOIN items_facepng ON items_monolithic.object=items_facepng.obj ;

Error: near "from": syntax error

 

I'll keep working on the code.

To unsubscribe from this group and stop receiving emails from it, send an email to django...@googlegroups.com.
To post to this group, send email to djang...@googlegroups.com.

[Kevin Jay]

You are correct.

Django creates a primary key of 'id' for each table as an integer by default. So foreignkey will reference that primary key which by default is an integer.

If the primary key is changed to some other type, Django can still handle it.

Apparently using integers is the more efficient method.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/27eee43ff8024c98bcf9a5e9cd4bfd89%40iss2.ISS.LOCAL.