SELECT fieldlist FROM table \ WHERE condition \ GROUP BY groupfield \ ORDER BY orderfield \ LIMIT limitcount OFFSET offsetcount
The different elements within the source statement affect how a view is written in the following ways:
SELECT fieldlist
The field list within the SQL statement affects either the
corresponding key or value within the
map() function, depending on whether you
are also selecting or reducing your data. See
Section 9.10.1, “Translating SQL Field Selection (SELECT) to
Map/Reduce”
FROM table
There are no table compartments within Couchbase Server and
you cannot perform views across more than one bucket boundary.
However, if you are using a type field
within your documents to identify different record types, then
you may want to use the map() function to
make a selection.
For examples of this in action, see Section 9.9.8, “Selective Record Output”.
WHERE condition
The map() function and the data generated
into the view key directly affect how you can query, and
therefore how selection of records takes place. For examples
of this in action, see
Section 9.10.2, “Translating SQL WHERE to Map/Reduce”.
ORDER BY orderfield
The order of record output within a view is directly
controlled by the key specified during the
map() function phase of the view
generation.
For further discussion, see
Section 9.10.3, “Translating SQL ORDER BY to Map/Reduce”.
LIMIT limitcount OFFSET offsetcount
There are a number of different paging strategies available
within the map/reduce and views mechanism. Discussion on the
direct parameters can be seen in
Section 9.10.5, “Translating SQL LIMIT and OFFSET”. For
alternative paging solutions, see
Section 9.8.3, “Pagination”.
GROUP BY groupfield
Grouping within SQL is handled within views through the use of
the reduce() function. For comparison
examples, see
Section 9.10.4, “Translating SQL GROUP BY to Map/Reduce”.
The interaction between the view map()
function, reduce() function, selection
parameters and other miscellaneous parameters according to the
table below:
| SQL Statement Fragment | View Key | View Value | map() Function | reduce() Function | Selection Parameters | Other Parameters |
| SELECT fields | Yes | Yes | Yes | No: with GROUP BY and SUM() or
COUNT() functions only | No | No |
| FROM table | No | No | Yes | No | No | No |
| WHERE clause | Yes | No | Yes | No | Yes | No |
| ORDER BY field | Yes | No | Yes | No | No | descending |
| LIMIT x OFFSET y | No | No | No | No | No | limit, skip |
| GROUP BY field | Yes | Yes | Yes | Yes | No | No |
Within SQL, the basic query structure can be used for a multitude
of different queries. For example, the same 'SELECT
fieldlist FROM table WHERE xxxx can be used with a
number of different clauses.
Within map/reduce and Couchbase Server, multiple views may be needed to be created to handled different query types. For example, performing a query on all the blog posts on a specific date will need a very different view definition than one needed to support selection by the author.