DQL Roadmap

All syntax included in this article is sudo syntax added to express functionality. The final syntax may differ from the syntax included here.

​

Projections

A projection refers to an operation of selecting specific fields from a collection in a query result by allowing you to specify which fields you want to include in the output of a query while excluding others.

SELECT field1, field2
FROM your_collection_name

​

Aggregates

Applied most commonly to grouped data or the entire result set of a query, use aggregation functions to perform calculations on a set of values and produce a single result, as well as summarize data across multiple documents in a collection.

Aggregate operations: COUNT SUM MAX MIN AVG

SELECT COUNT(field1), SUM(field2), MAX(field3), MIN(field4), AVG(field5)
FROM your_collection_name

​

GROUP BY

The GROUP BY clause is used to group documents based on one or more fields in a query. It allows you to group data together based on common values in specific fields and perform aggregate calculations on those groups.

SELECT category, COUNT(*)
FROM your_collection_name
GROUP BY category;

​

HAVING

The HAVING clause is used in conjunction with the GROUP BY clause to filter the result of a query based on conditions involving aggregate functions. The HAVING clause allows you to apply filtering conditions to the grouped data in a query.

SELECT field1, aggregate_function(field2)
FROM your_collection_name
GROUP BY field1
HAVING condition;

​

Aliasing

Aliasing allows users to assign a temporary name or alias to a collection or field in a query. Aliases are assigned using the AS keyword followed by the alias.

Aliasing a collection

SELECT c.field1
FROM your_collection_name AS c

Aliasing a field

SELECT field1 AS f
FROM your_collection_name

​

Multiple FROM Terms

Multiple FROM terms can be used to refer to multiple collections within a query. When you include multiple FROM terms, the result is a cross-product of the collections. This results in a larger result set that includes all possible combinations.

SELECT *
FROM collection1, collection2

​

DO REPLACE

The DO REPLACE clause is used to replace a document in a collection if it already exists, or insert a new document if it does not exist. This is useful for ensuring that a document will exist in the collection with the specified data, regardless of whether it already exists or not, instead of utilizing the default merge behavior.

INSERT INTO your_collection_name
DOCUMENTS (:document)
ON ID CONFLICT DO REPLACE

​

JOINs

Joins are used to combine documents from two or more tables based on a related fields between them.

Join types include: LEFT JOIN INNER JOIN CROSS JOIN RIGHT JOIN OUTER JOIN

SELECT cars._id, dealerships._id, dealerships.location
FROM cars
INNER JOIN dealerships ON cars.dealership_id = dealerships._id

​

INSERT with CBOR

The INSERT INTO statement will support CBOR serialized data.

CBOR data is declared using CBOR keyword prior to the CBOR object.

import { encode, decode } from "cbor";

const data = {
  _id: "123",
  field1: 0,
  field2: "blue"
};
const cborData = encode(data);

ditto.store.execute(`
	INSERT INTO your_collection_name
	DOCUMENTS (CBOR :cborData)`,
	{ cborData });

​

Secondary Indexing

An index is a data structure that enhances the efficiency of queries by providing a faster lookup mechanism for retrieving data based on specific attributes or fields. An index allows DQL to locate and access data more quickly, reducing the need for scanning the entire collection or table.

CREATE INDEX index_name
ON your_collection_name (field1, field2, ...);

DROP INDEX index_name

SELECT *
FROM your_collection_name
USE INDEX (index_name)
WHERE field1 > 10000

​

Pre-Defined Type Definitions

Setting a pre-defined type definition will allow users to set a type definition once for a given collection instead of needing to declare it within each query.

DEFINE COLLECTION your_collection_name (
  field1 REGISTER,
  field2 MAP,
  filed3 ATTACHMENT,
  ...
)