DQL Roadmap Functionality

🚧 The functionality in this article is in active development and NOT supported in this version of the DQL language.
This document outlines functionality in the DQL language that is in active development. This is not an exhaustive list of the features and functionality the Ditto team is tracking concerning DQL and aims to address larger functionality gaps.
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.
DQL
SELECT field1, field2
FROM your_collection_name
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
DQL
SELECT COUNT(field1), SUM(field2), MAX(field3), MIN(field4), AVG(field5)
FROM your_collection_name
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.
DQL
SELECT category, COUNT(*)
FROM your_collection_name
GROUP BY category;
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.
DQL
SELECT field1, aggregate_function(field2)
FROM your_collection_name
GROUP BY field1
HAVING condition;
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
DQL
SELECT c.field1
FROM your_collection_name AS c
SELECT c.field1
FROM your_collection_name AS c
﻿
Aliasing a field
DQL
SELECT field1 AS f
FROM your_collection_name
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.
DQL
SELECT *
FROM collection1, collection2
SELECT *
FROM collection1, collection2
﻿
﻿
🚧 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
DQL
SELECT cars._id, dealerships._id, dealerships.location
FROM cars
INNER JOIN dealerships ON cars.dealership_id = dealerships._id
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.
JS
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 });
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.
DQL
CREATE INDEX index_name
ON your_collection_name (field1, field2, ...);
CREATE INDEX index_name
ON your_collection_name (field1, field2, ...);
﻿
DQL
DROP INDEX index_name
DROP INDEX index_name
﻿
DQL
SELECT *
FROM your_collection_name
USE INDEX (index_name)
WHERE field1 > 10000
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.
DQL
DEFINE COLLECTION your_collection_name (
  field1 REGISTER,
  field2 MAP,
  filed3 ATTACHMENT,
  ...
)
DEFINE COLLECTION your_collection_name (
  field1 REGISTER,
  field2 MAP,
  filed3 ATTACHMENT,
  ...
)
﻿
﻿
🚧 Cross Collection Transactions
Cross collection atomic modifications allows for multiple modifications to occur across transactions.
SQL
BEGIN TRANSACTION;

-- Update an employee's name
UPDATE employees
SET name = 'New Name'
WHERE _id = '123';

-- Update a department's budget
UPDATE departments
SET budget = 50000
WHERE _id = 'department_456';

COMMIT;
BEGIN TRANSACTION;

-- Update an employee's name
UPDATE employees
SET name = 'New Name'
WHERE _id = '123';

-- Update a department's budget
UPDATE departments
SET budget = 50000
WHERE _id = 'department_456';

COMMIT;
﻿
﻿
Updated 02 Aug 2024
Did this page help you?
Operator Expressions
Query Syntax (Legacy)