-
Notifications
You must be signed in to change notification settings - Fork 14
Forming Dynamic SQL with the Repository
While developing DeepLynx, you may often find yourself in a situation where you need to retrieve certain information from the database beyond the default SELECT * FROM table
. There are various ways to go about this, depending on your needs. This article will serve as a guide for which methods to use in which cases, as well as details on how to use the various functions in the repository layer.
The first question to ask yourself is if you even need to use the dynamic repository layer to build your query, or if it is more appropriate to use a mapper function. Mapper functions should be used if you don't need any filters on your query, or if you need the same filter every time you call the query. If there is a filter that is only applied sometimes, it is better to use a repository listing function. Below are a few examples of using mapper functions.
Mapper functions could be used for a simple retrieval when targeting a specific node:
SELECT * FROM type_mappings WHERE id = $1
(where $1 is mapping ID)
You also might use a mapper function with a few filters on it to retrieve only specific fields that you need:
SELECT key, user_id, note FROM keypairs WHERE user_id = $1 AND note = $2
to fetch all key-secret pairs for a user with a specific note (only returning relevant fields)
Or you may use a mapper to apply filters from multiple tables, such as when searching for an edge based on its relationship:
SELECT e.* FROM current_edges e
JOIN metatype_relationship_pairs mp
ON mp.id = e.relationship_pair_id
LEFT JOIN metatype_relationships mr
ON mp.relationship_id = mr.id
LEFT JOIN metatypes origin
ON origin.id = mp.origin_id
LEFT JOIN metatypes destination
ON destination.id = mp.destination_id
WHERE origin.name = $1
AND relationship.name = $2
AND destination.name = $3
If you find yourself needing something more complex or dynamic than what the mapper layer can provide, it may be time to use the repository layer for data retrieval. The repository layer builds off of the most basic mapper functions but allows for more flexibility in querying. Any interactions with the frontend query layer, graphQL, or optional filters (such as listing datasources with the parameter timeseries=true
) are accomplished by using the repository layer.
Below is a table describing various SQL query clauses, their repo equivalent, any arguments the method may take, and examples of those arguments. These functions will be described in more detail below the table.
Desired Output | SQL Clause | Repo Method | Arguments | Example |
Select all fields from table |
SELECT t.*
FROM table t |
list() with no arguments | N/A |
repo.list() |
Count all rows in table |
SELECT COUNT(*)
FROM nodes |
count() | N/A |
repo.count() |
Filter on a specific column using a wrapper function from the repository |
WHERE thisTable.name = 'bob' |
columnName(operator, value) |
operator: comparison or matching operator for filter |
repo.where().name('eq', 'bob') |
Use grouping to aggregate data |
SELECT id, COUNT(*)
FROM nodes
GROUP BY id |
groupBy(fields, table?) |
fields: string or array of strings specifying fields to group by. |
repo.addFields('id')
.groupBy('id')
.count() |
Limit (select first n) results |
LIMIT 25 |
list({limit}) |
limit: integer specifying number of records to limit to. |
repo.list({limit: 25}) |
Order results |
ORDER BY name DESC |
list({sortBy, sortDesc}) |
sortBy: column to sort by |
repo.list({sortBy: 'name', sortDesc: true}) |
Offset (skip first n) results |
OFFSET 10 |
list({offset}) |
offset: integer specifying number of records to skip. |
repo.list({offset: 10}) |
Eliminate duplicate records |
SELECT DISTINCT t.* |
list({distinct}) |
distinct: boolean specifying whether to SELECT DISTINCT. |
repo.list({distinct: true}) |
Eliminate duplicates for one field |
SELECT DISTINCT ON (t.id) t.* |
list({distinct_on}) |
distinct_on: string specifying column to select distinct on. |
repo.list({distinct_on: 'id'}) |
Select specific fields from table (replacing the default SELECT *) |
SELECT t.x, t.y, t.z |
select(fields, table?) |
fields: string or array of strings specifying fields to be selected. This replaces the default select-list. |
repo.select(['x', 'y', 'z'], 't') |
Add fields to current select-list (in addition to the default SELECT *) |
SELECT t.*, t2.name |
addFields(fields, table?) |
fields: string or array of strings specifying fields to be selected. This adds to the default select-list. |
repo.addFields(['name'], 't2') |
Override default table |
FROM new_table nt |
from(table, alias?) |
table: name of new table to select from |
repo.from('new_table', 'nt') |
Join two tables |
FROM books b
INNER JOIN authors a
ON b.author_id = a.id |
join(destination, options, origin?) |
destination: table you want to join to |
repo.from('books', 'b')
.join('authors', {
conditions: {origin_col: 'author_id', destination_col: 'id'},
destination_alias: 'a',
join_type: 'INNER'
}
) |
Filter on a column that does not have a repo wrapper function |
WHERE notThisTable.name = 'bob' |
query(column, operator, value, conditions?) |
column: the column to query on |
repo.where()
.query('name', 'eq', 'bob', {tableAlias: 'notThisTable'}) |
Filter on property of JSON column |
WHERE properties->>'name' = 'bob' |
queryJsonb(key, column, operator, string, conditions?) |
key: subproperty to filter on |
repo.where()
.queryJsonb('name', 'properties', 'eq', 'bob') |
Combine two filters |
WHERE name = 'bob'
AND state = 'ID' |
query(...args).and().query(...args) | N/A |
repo.where()
.name('eq', 'bob')
.and()
.state('eq', 'ID') |
Use one filter or another |
WHERE name = 'bob'
OR state = 'ID' |
query(...args).or().query(...args) | N/A |
repo.where()
.name('eq', 'bob')
.or()
.state('eq', 'ID') |
Use one filter, then one or another |
WHERE containerID = 1
AND (name = 'bob' OR state = 'ID') |
and(repo) |
repo: a new repository object containing the nested filters |
repo.where()
.containerID('eq', 1)
.and(
new MyRepository()
.name('eq', 'bob')
.or()
.state('eq', 'ID')
) |
Use one filter or a combination of other filters |
WHERE containerID = 1 OR
(name = 'bob' AND state = 'ID') |
or(repo) |
repo: a new repository object containing the nested filters |
repo.where()
.containerID('eq', 1)
.or(
new MyRepository()
.name('eq', 'bob')
.and()
.state('eq', 'ID')
) |
Filter using a subquery |
WHERE import_id IN (
SELECT sub.id
FROM imports sub
WHERE sub.data_source_id = 123
) |
subquery(repo) |
repo: a new repository object containing the subquery |
const sub = repo.subquery(
new MyRepository()
.select('id', 'sub')
.from('imports', 'sub')
.where()
.query('data_source_id', 'eq', 123, {tableAlias: 'sub'})
)
repo = repo.where().import_id('in', sub) |
Sections marked with ! are in progress.
- HTTP Authentication Methods
- Generating and Exchanging API Keys for Tokens
- Creating a DeepLynx Enabled OAuth2 App
- Authentication with DeepLynx Enabled OAuth2 App
- Creating an Ontology
- Creating Relationships and Relationship Pairs
- Ontology Versioning
- Ontology Inheritance
- Querying Tabular (Timeseries) Data
- Timeseries Quick Start
- Timeseries Data Source
- Timeseries Data Source via API
- Exporting Data
- Querying Data
- Querying Timeseries Data
- Querying Jazz Data
- Querying Data - Legacy
- Querying Tabular Data