如何使用Angularjs操作从SQL返回的数据

Question

I am using AngularJS to call a SQL query and return a whole table. Is there a way to manipulate data on the front end once I've retrieved the data or is better to do the manipulation on the processing page before it gets back to Angular?

The code below shows the app.js file that I am using to retrieve the data and I have included a line of HTML to show a sample of what type of output I am trying to achieve.

app.js

var dashboardApp = angular.module('dashboardApp', []);
dashboardApp.controller('dashboardController', function($scope, $http, $location, $anchorScroll) {

    $http.get('lib/dbp.php').success(function(data) {
      $scope.surveydata = data;
    }); 

});

Sample HTML

<ul>
    <li ng-repeat="item in surveydata['data']">{{item.location}}</li>
</ul>

{{surveydata['data'].location.length}}

Sample Output

{ "data": [ 
  { 
    "surveyid": "51", 
    "customerName": "Steve Jones", 
    "companyName": "Acme", 
    "projectName": "Various Project", 
    "customerPhone": "", 
    "customerEmail": "", 
    "location": "Dallas", 
    "q1": "1", 
    "q1Comment": "None", 
    "q2": "1", 
    "q2Comment": "None", 
    "q3": "1", 
    "q3Comment": "None", 
    "q4": "2", 
    "q4Comment": "None", 
    "q5": "1", 
    "q5Comment": "None", 
    "q6": "Yes", 
    "q6Comment": "Always.", 
    "q7": "Yes", 
    "q7Comment": "Always.", 
    "q8": "Yes", 
    "q8Comment": "Certainly.", 
    "q9": "Yes", 
    "q9Comment": "None", 
    "q10Comment": "None", 
    "q11Comment": "Keep up the good work!", 
    "q12": "No", 
    "timestamp": "2015-07-17 11:08:24", 
    "ipaddress": "" 
  }

The ng-repeat section works fine for basic data output but with the location.length what I'd actually like to do is be able to count the number of locations individually, i.e. zone 1 has 5 surveys submitted or zone 2 has 8.

I am new to Angularjs so I am trying to figure out what the best practice is for this type of situation.

What I would like to see as output is a count of all records with the location of "Dallas" or an average of scores from all records from "Dallas". There are numerous other things I would like to do but having a grasp on how to do simple data manipulation like those mentioned would help me for other applications of the data.

Answer 1

Using an SQL query to always return an entire table is not typically a good practice. It's acceptable if you are guaranteed to have a small table forever (a strong statement). Otherwise it's best to target your queries for whatever data will be presented, and use a second query for high-level information like count.

I don't know what your backend looks like, but a typical approach would be something like this (pseudocode):

data = {}
SELECT surveyid, customerName, ... FROM "YourTable" WHERE (...) LIMIT 48 OFFSET 0;
response -> data.rows = response
SELECT count(*) FROM "YourTable" WHERE (...);
response -> data.count = response
return data

Then on the frontend you're manipulating { rows: [...], count: 123 }, which should be in the format you want given your needs above.