These are chat archives for mypebble/django-pgviews

5th
Jun 2017
Shane Grey
@shanemgrey
Jun 05 2017 18:09
Trying to use pgviews and the app is generating the views correctly when I run sync_pgviews, but DRF only shows a list of the id field for the result of the query even when I use queryset = Report.objects.all() and fields = 'all'
When I request the view data in pgModeler I get all the fields as expected. What am I missing that's causing DRF to only render the ID?
Scott Walton
@scott-w
Jun 05 2017 18:14
hi @shanemgrey, do you have a code sample of your model?
Shane Grey
@shanemgrey
Jun 05 2017 18:15

``` class HouseholdTargetSummaryView(pgView.View):

# projection = ['target.HouseholdTarget.*',]
# dependencies = ['target.HouseholdTarget', 'target.HouseholdTargetJoin']
sql = """
    SELECT t.*,
           COUNT(e.start_date) AS enrolled,
           COUNT(e.end_date) AS completed,
           CASE WHEN t.enrollment_goal > 0 THEN ROUND((COUNT(e.start_date))::decimal/t.enrollment_goal*100,0) ELSE NULL END AS percent_enrolled,
           CASE WHEN t.completion_goal > 0 THEN ROUND((COUNT(e.end_date))::decimal/t.completion_goal*100,0) ELSE NULL END AS percent_completed
    FROM target_householdtarget t
    INNER JOIN target_householdtargetjoin e ON t.id = e.target_id
    WHERE e.start_date IS NOT NULL
      AND e.active = TRUE
      AND t.active = TRUE
    GROUP BY t.id
    ORDER BY t.calendar_year DESC, t.target_num;
"""

```

class Meta:
  app_label = 'target'
  db_table = 'target_householdtarget_summary'
  managed = False
Is it necessary to create another models.model mapping the fields to the resulting view fields?
Scott Walton
@scott-w
Jun 05 2017 18:22
@shanemgrey I think it’s because DRF looks at the fields attached to the Model
Shane Grey
@shanemgrey
Jun 05 2017 18:22
what is projection supposed to do?
Scott Walton
@scott-w
Jun 05 2017 18:23
does it work if you add the fields as:
class HouseholdTargetSummaryView(View):
    sql = ‘...'
    enrolled = models.DateField()
    completed = models.DateField()
Shane Grey
@shanemgrey
Jun 05 2017 18:23
The view I pasted doesn't have it's own model. It's the result of a join of two others. Do I need to create a third model just to map the fields resulting from the query?
Scott Walton
@scott-w
Jun 05 2017 18:24
no, the View creates the model for you
Shane Grey
@shanemgrey
Jun 05 2017 18:25
I'm confused on where I would add those fields. In the views.py, or the models.py
n/m. Added to the models.py
No change.
Scott Walton
@scott-w
Jun 05 2017 18:31
can you paste the code from your DRF serializer?
Shane Grey
@shanemgrey
Jun 05 2017 18:31
class HouseholdTargetSummaryReportViewSerializer(CustomBaseSerializer):
  class Meta:
      model = HouseholdTargetSummaryView
      fields = '__all__'
The custombaseserialzer just adds timestamps- Correction, it adds links to the users who created or modified the row
Shane Grey
@shanemgrey
Jun 05 2017 18:39
Found my error. I needed to explicitely define all of the model fields.
class HouseholdTargetSummaryView(pgView.View):
    # projection = ['target.HouseholdTarget.*',]
    # dependencies = ['target.HouseholdTarget', 'target.HouseholdTargetJoin']
    sql = """
        SELECT t.*,
               COUNT(e.start_date) AS enrolled,
               COUNT(e.end_date) AS completed,
               CASE WHEN t.enrollment_goal > 0 THEN ROUND((COUNT(e.start_date))::decimal/t.enrollment_goal*100,0) ELSE NULL END AS percent_enrolled,
               CASE WHEN t.completion_goal > 0 THEN ROUND((COUNT(e.end_date))::decimal/t.completion_goal*100,0) ELSE NULL END AS percent_completed
        FROM target_householdtarget t
        INNER JOIN target_householdtargetjoin e ON t.id = e.target_id
        WHERE e.start_date IS NOT NULL
          AND e.active = TRUE
          AND t.active = TRUE
        GROUP BY t.id
        ORDER BY t.calendar_year DESC, t.target_num;
    """

    created_at = models.DateTimeField()
    modified_at = models.DateTimeField()
    target_num = models.IntegerField()
    target_type = models.TextField()
    calendar_year = models.IntegerField()
    description = models.TextField()
    enrollment_goal = models.IntegerField()
    completion_goal = models.IntegerField()
    notes = models.TextField()
    active = models.BooleanField()
    created_by_id = models.IntegerField()
    modified_by_id = models.IntegerField()
    enrolled = models.IntegerField()
    completed = models.IntegerField()
    percent_enrolled = models.IntegerField()
    percent_completed = models.IntegerField()


    class Meta:
      app_label = 'target'
      db_table = 'target_householdtarget_summary'
      managed = False
Which is what I think you were getting at when suggesting I add those fields. :-)
Thank you for the nudge in the right direction!
Scott Walton
@scott-w
Jun 05 2017 19:06
no worries dude :)
i’d like to get round to making that work somehow without the fields but it’s never got high up enough in the list :P
Shane Grey
@shanemgrey
Jun 05 2017 19:27
I totally understand. I appreciate what you have already accomplished.
Scott Walton
@scott-w
Jun 05 2017 19:32
most of the credit has to go to others, I have to admit
i’m merely the shepherd ;)