title: postgresql two nested joins and arrays in join
body: <p>I've never had to ask an SO question before, I always eventually stumble upon what I'm looking for through searching, but there's a lot going on here and I've gotten to the end of my searching prowess... Thanks for your help / suggestions.</p>
  
  <h1>The problem</h1>
  
  <p>I need to generate reports on the fly about how many 'students' in my organization have taken each 'assessment', and the standards associated with each 'assessment'.</p>
  
  <ul>
  <li>Student totals are calculated ahead of time and stored in assessments_report
  <ul>
  <li>The report row needs to be joined with the "assessment" to display info</li>
  </ul></li>
  <li>Standards have sub-standards, but assessments only know about the sub-standard.</li>
  <li>Assessments have multiple sub-standards and store them in an array of ids.</li>
  </ul>
  
  <p>I'm trying to do this in the most performant way that minimizes the load on Postgres but I don't believe I've achieved that goal...</p>
  
  <h1>The Setup</h1>
  
  <p><strong>Server:</strong> Amazon EC2 running Ubuntu</p>
  
  <p><strong>The App (Server):</strong> Node.js (0.10.26)</p>
  
  <p><strong>The App (Client):</strong> Angular.js (1.2.13)</p>
  
  <p><strong>Database (Queries):</strong> PostgreSQL (9.3.1)</p>
  
  <p><strong>Database (Cache):</strong> MongoDB <em>(Hope to cache report in future)</em></p>
  
  <h1>The Tables</h1>
  
  <p><strong><a href="http://sqlfiddle.com/#!15/61fd2/11" rel="nofollow">SQL FIDDLE</a></strong></p>
  
  <p><strong>assessments</strong></p>
  
  <pre><code>_id                int4
  description        text
  category           varchar(60)
  sub_standards      int4 ARRAY
  ...
  </code></pre>
  
  <p><strong>assessments_report</strong> (pre-calculated sums)</p>
  
  <pre><code>assessment_id(FK)  int4
  client_id(FK)      int4
  students           int4
  completed          int4
  incomplete         int4
  ...
  </code></pre>
  
  <p><strong>sub_standards</strong></p>
  
  <pre><code>_id
  standard_id(FK)    int4
  description        varchar(255)
  ...
  </code></pre>
  
  <p><strong>standards</strong></p>
  
  <pre><code>_id                int4
  name               varchar(60)
  description        varchar(255)
  ...    
  </code></pre>
  
  <h1>The Query</h1>
  
  <pre><code>//Stored as array for readability in Node-Postgres use
  SELECT r.*, as.* FROM assessments_report r
  INNER JOIN (
    SELECT a._id AS assessment_id, a.description, a.category, a.states,
    array_to_json(array_agg(ss.*)) AS standards
    FROM assessments a LEFT JOIN (
      SELECT ss.*, s.name AS parent_name, s.description AS parent_description
      FROM sub_standards ss
      INNER JOIN standards s ON ss.standard_id = s._id
    ) ss ON ss._id = ANY (a.sub_standards) GROUP BY a._id
  ) as
  ON as.assessment_id = r.assessment_id
  WHERE r.client_id = $1
  </code></pre>
  
  <h1>The Desired Output For Each Row (shown as JSON)</h1>
  
  <pre><code>assessment_id: 2,
  students: 2,
  complete: 1
  incomplete: 1,
  description: "...",
  category: "...",
  states: ["AL","AZ",...],
  standards: [
    {
      _id: 1,
      standard_id: 3,
      description: "...",
      parent_name: "...",
      parent_description: "..."
    },
    {
      _id: 2,
      standard_id: 4,
      description: "...",
      parent_name: "...",
      parent_description: "..."
    },
  ]
  </code></pre>
  
  <p><strong><a href="http://sqlfiddle.com/#!15/61fd2/11" rel="nofollow">SQL FIDDLE</a></strong></p>