INNER JOIN in three models of DJANGO RF

Question

Here My Models:

Model

I need to make a Join between these three tables, I need a list with the field machine_name, which are in the Action table, based on the resetter_uuid of the machine.

class Machine(models.Model):
    machine_uuid = models.UUIDField(default=uuid.uuid4,unique=True)
    machine_created_date = models.DateTimeField(default=timezone.now)
    machine_name = models.CharField(max_length=50, null=True, default=None, blank=True)
    machine_time_up = models.CharField(max_length=10, null=True, default=None, blank=True)
    resetter_uuid = models.ForeignKey(Resetter, to_field="resetter_uuid", db_column="resetter_uuid", related_name='Resetters', on_delete=models.CASCADE, null=True)

    class Meta:
        db_table = 'Machine'

class Resetter(models.Model):
    resetter_uuid = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
    resetter_created_date = models.DateTimeField(default=timezone.now)
    class Meta:
        db_table = 'Resetter'

class Action(models.Model):
    action_uuid = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
    action_created_date = models.DateTimeField(default=timezone.now)
    action_change_port = models.PositiveSmallIntegerField(null=True, default=None)
    action_reset_machine = models.PositiveSmallIntegerField(null=True, default=None)
    machine_uuid = models.ForeignKey(Machine, to_field="machine_uuid", db_column="machine_uuid", related_name='Machines', on_delete=models.CASCADE)
    action_status = models.BooleanField()

    class Meta:
        db_table = 'Action'

With MySQL I have solved the query in this way:

SELECT 
    m.machine_name
FROM
    machine m
INNER JOIN
    resetter r
ON
    m.resetter_uuid =r.resetter_uuid
INNER JOIN
    xem_api.action a
ON
    a.machine_uuid = m.machine_uuid 
WHERE
    m.resetter_uuid    = '5b94e54e7fd644359374146fe78eaea4' and a.action_status = true and a.action_reset_rig = 1;

I have used prefetch_related() and select_related() in the views but, without success how would the best way to get that result?

Answer 1

I believe the join will work with select_related / prefetch_related. Try the code below. Remember select_related is used to query a single field (the one side of a relation) and prefetch is used to query the many field.

Machine.objects.select_related('Resetters').prefetch_related('Machines).all()

You can double check that the join is working by adding a logging config to your settings file. Add the code below, do your query, and check your logs.

LOGGING = {
    'version': 1,
    'filters': {
        'require_debug_true': {
            '()': 'django.utils.log.RequireDebugTrue',
        }
    },
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'filters': ['require_debug_true'],
            'class': 'logging.StreamHandler',
        }
    },
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
            'handlers': ['console'],
        }
    }
}