How to make a "SELECT" of a model in django?

Question

I am new to django and I am trying to make a django view that will bring me certain values from two models, this would be accomplished by doing a join if done directly in sql. What I intend to do with the obtained data is return it as JSON and use this json in an html page. I just don't know how to structure or if there is any way to get the data like sql.

Model device

class device(models.Model):
    device_name = models.CharField(max_length=50, unique=True, help_text='Station Name', validators=[validate_slug])
    parent_area_id = models.ForeignKey('area', on_delete=models.CASCADE, null=True, help_text='Parent Area')
    f2cuid = models.CharField(max_length=100, unique=True, validators=[validate_slug])
    ip_address = models.GenericIPAddressField(protocol='both', unpack_ipv4='True', default='127.0.0.1', blank=False, null=False)
    tower_ip_address = models.GenericIPAddressField(protocol='both', unpack_ipv4='True', default='127.0.0.1', blank=True, null=True)
    layered_tower = models.BooleanField(default=False, blank=True, help_text='Check if tower is multilayer')
    layer = models.CharField(max_length=1, unique=False, null=True, default=None, help_text='Layer', choices=layer_choices)
    target_oee = models.DecimalField(validators=[MinValueValidator(0), MaxValueValidator(100)], help_text='OEE Target', decimal_places=2, max_digits=6, default=0)
    target_availability = models.DecimalField(validators=[MinValueValidator(0), MaxValueValidator(100)], help_text='Availability Target', decimal_places=2, max_digits=6, default=0)
    target_performance = models.DecimalField(validators=[MinValueValidator(0), MaxValueValidator(100)], help_text='Performance Target', decimal_places=2, max_digits=6, default=0)
    target_quality = models.DecimalField(validators=[MinValueValidator(0), MaxValueValidator(100)], help_text='Quality Target', decimal_places=2, max_digits=6, default=0)

Model notification_radio

class notification_radio(models.Model):
    device_id = models.ForeignKey('device', on_delete=models.CASCADE, null=False)
    event_id = models.ForeignKey('event', on_delete=models.CASCADE, null=False)
    to_address = models.CharField(null=False, blank=False, max_length=100)
    message = models.CharField(null=False, blank=False, max_length=100, default='ANDON ALERT')
    notification_type = models.CharField(null=False, blank=False, choices=notification_type_choices, max_length=100)
    notification_id = models.IntegerField(null=False)
    requested_date = models.DateTimeField(null=True)
    processed = models.BooleanField(default=False)
    processed_date = models.DateTimeField(null=True)

Sentence SQL

SELECT 
    `and`.`device_name` AS `device_name`,
    COUNT(`anr`.`device_id_id`) AS `notif_sended`
FROM
    (`andon_notification_radio` `anr`
    JOIN `andon_device` `and` ON ((`anr`.`device_id_id` = `and`.`id`)))
WHERE
    (`anr`.`processed` = 1)
GROUP BY `device_name`

VIEW Django

def notif_count_by_station(request):
    data = notification_radio.objects.all() \
        device.objects.all()
    return JsonResponse(list(data), safe=False)

This is how you would expect to get the JSON, you would get the device name and the notif_sended grouped by the device_name, it would output the notifications sent by each device_name.

Regards.

this is not a JSON blob. Can you describe how the format of the JSON should look like? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Apr 15, 2020 at 22:26
Like this : " {"data": [{"device_name": "PR02", "notif_sended": 1}, {"device_name": "PR01", "notif_sended": 8}, {"device_name": "IN02", "notif_sended": 1}]} " — Saul JP
– Saul JP, Commented Apr 15, 2020 at 22:53

willeM_ Van Onsem · Accepted Answer · 2020-04-15 22:02:28Z

1

You can perform such a query with:

from django.db.models import Count

def notif_count_by_station(request):
    data = device.objects.values('device_name').filter(
        notification_radio__processed=1
    ).annotate(
        notif_sended=Count('notification_radio')
    )
    return JsonResponse({'data': list(data)})

Please do not return data wrapped in an outer list, since it can be victim to cross-site request forgery.

answered Apr 15, 2020 at 22:02

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

Comments

EL hassane · Accepted Answer · 2020-04-15 22:06:05Z

0

i don't know what exactly you are looking foor, but if oy want to get device name and device id where processed =1.

data = Notification_radio.objects.filter(processed=1).values('device_id __device_name').annotate(total=Count('device_id'))

answered Apr 15, 2020 at 22:06

EL hassane

1341 silver badge9 bronze badges

1 Comment

Saul JP Over a year ago

The JSON, the device name would be obtained and the notif_sended grouped by the device_name, would throw the notifications sent by each device_name. As in the image above.

Collectives™ on Stack Overflow

How to make a "SELECT" of a model in django?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related