Proper ways to do MySQL multiple join

Question

MySQL version : 5.7

Hello. I am working on a new django Project.

For the meanwhile,

I brought 2 tables.

Auth(1st Table) is containing user data(like id, email, password, name, etc.)

Paper(2st Table) is containing research paper information.

The Paper table has 10 columns for user permission. The only 10 users can read the paper, so I have a model for the table like;

class DocDetail(models.Model):
    no = models.AutoField(primary_key=True)
    doc_id = models.CharField(null=False, default="", max_length=255)
    doc_name = models.CharField(null=False, default="", max_length=255)
    doc_pw = models.CharField(null=False, default="", max_length=255)
    view_perm_1 = models.IntegerField(null=True)
    view_perm_2 = models.IntegerField(null=True)
    view_perm_3 = models.IntegerField(null=True)
    view_perm_4 = models.IntegerField(null=True)
    view_perm_5 = models.IntegerField(null=True)
    view_perm_6 = models.IntegerField(null=True)
    view_perm_7 = models.IntegerField(null=True)
    view_perm_8 = models.IntegerField(null=True)
    view_perm_9 = models.IntegerField(null=True)
    view_perm_10 = models.IntegerField(null=True)
    folder = models.CharField(null=False, default="", max_length=255)
    url = models.CharField(null=False, default="", max_length=255)
    status = models.IntegerField(null=True)
    action_exp = models.DateTimeField(null=True)
    date_updated = models.DateTimeField(null=True)

view_perm columns are indicating Auth Table's user_id.

I want to select all columns of the Paper table and user data which linked to each view_perm column with 1 query.

I think I am pretty much wrong with modeling.

I can do select with 10 join, but it doesn't look right. Is there any better idea to make this works?

I am pretty new to SQL, so please guide me to right direction.

I cannot say nothing about Django but from the relational point of view having those ten columns is wrong. The proper way would be an extra table linking users and papers. — sticky bit, Jun 13 '19 at 18:00

score 0 · Accepted Answer · answered Jun 13 '19 at 18:10

This is all kinds of wrong.

Firstly, you should absolutely never under any circumstances define your own authentication. Use Django's. (By all means, define a custom User model, as long as it inherits from AbstractBaseUser and therefore uses the built-in functionality to store and check passwords.)

Secondly, when defining relationships between models, you must use a relationship field, not an integer. In Django those are ForeignKey, OneToOneField, and ManyToManyField. In your case, it seems that you have a many-to-many relationship, so use ManyToManyField.

This also fixes your other problem, in that a many-to-many relationship is defined (behind the scenes) as a linking table that connects user IDs to page IDs. So all the JOINs are done for you.

Your models should look like:

class DocDetail(models.Model):
    no = models.AutoField(primary_key=True)
    doc_id = models.CharField(default="", max_length=255)
    doc_name = models.CharField(default="", max_length=255)
    doc_pw = models.CharField(default="", max_length=255)
    users = models.ManyToManyField('auth.User')
    folder = models.CharField(default="", max_length=255)
    url = models.CharField(default="", max_length=255)
    status = models.IntegerField(null=True)
    action_exp = models.DateTimeField(null=True)
    date_updated = models.DateTimeField(null=True)

(I've also removed all the null=False, since that is the default.)

Now given an instance of DocDetail, you can get the permitted users simply by doing:

my_doc_detail.users.all()

Hi Daniel. Thanks for the answer. I have solved the issue. You are the light of people who are in the learning stage like me. I also got some hints from https://stackoverflow.com/questions/1182380/how-to-add-data-into-manytomany-field, which is also one of your answer. Thanks!! — , Jun 14 '19 at 05:45

Proper ways to do MySQL multiple join

1 Answers1