The JavaScript ml.query API provides advanced functions that may be used to query data from any table using a fluent interface and receive AJAX responses.

Define and run an advanced query

  1. <html>
  2. <head>
  3. <script type="text/javascript" src="/JS"></script>
  4. <script type="text/javascript">
  5. ml.onload(function () {
  6.  
  7. var q = ml.query().from('maplarge/Donors').where('PartyCode', 'Contains', 'r').groupby('State');
  8. q.select('State').select('Amount.sum').orderby('Amount');
  9. q.run(function (data, query) {
  10. var dataDiv = document.getElementById('dataDiv');
  11. dataDiv.innerHTML = ml.util.toJSON(data);
  12. });
  13.  
  14. });
  15. </script>
  16. </head>
  17. <body>
  18. <div id="dataDiv"></div>
  19. </body>
  20. </html>

The Return Object from the server is a JSON object which contains 2 Objects, data and totals. The data object contains all non-aggregate data from the query. Grouped aggregate data is contained in the totals data object following the naming convention columnName_aggregateName.

Return Object Description

Property Description
data object containing return data
{ “data”:
{ “column1”:[“row1”,”row2”],
“column2”:[“row1”,”row2”] }}
totals non grouped aggregated data
{ "column1_sum":[100] }

Methods

Method Description
start Begin at this row number. Equivalent to the X in sql's LIMIT X,Y
take Limit response to this many rows. Equivalent to the Y in sql's LIMIT X,Y
select If set, will only return specific columns (excluding geographic columns). Values are comma delimited. The value "*" means all non-geo columns. To include aggregates: ex: select=*, Amount.sum, Amount.avg, Amount.unique, Amount.countIf omitted, will return all non-geographic columns in the table.
groupby If set, uses the value as a comma delimited list. No aggregate grouping. Aggregate functions count, avg, sum, unique are available.
orderby If set, orders output data by that column. Supports single. Append asc or desc to sort ascending or descending. If not set, default is ascending order. ex: orderby=Amount.desc
where Standard where clauses, as defined by the API. column.test.value|column.test.value_OR_column.test.value etc. _OR_ is or, | is and.
Between Column row values between a minimum and maximum, inclusive
Bottom A given number of smallest column row values.
Contains Column rows that contain a value, as a substring.
ContainsAll Column rows that contain all of a list of values, as a substring.
ContainsAny Column rows that contain any of a list of values, as a substring.
ContainsNone Column rows that do not contain any of a list of values, as a substring.
ContainsOr Column rows that contain any of a list of values, as a substring. Alias for “ContainsAny” Equal Column rows that equal exactly some value.
EqualAny Column rows that equal any of a list of values.
EqualNone Column rows that do not equal any of a list of values.
EqualNot Column rows that do not equal a value exactly. Greater Column rows whose values are strictly greater than some value.
GreaterOR Column rows whose values are greater than or equal to some value. Less Column rows whose values are strictly less than some value.
LessOR Column rows whose values are less than or equal to some value.
NotBetween Column row values not between a min and max, inclusive. Top A given number of largest column row values.