Custom Queries
The generated code and APIs that exist often aren't enough and you'll want to perform custom queries to access your data in the database.
There are multiple ways of doing this:
- load all ents with privacy checks with
EntName.loadCustom
- load all raw data with
EntName.loadCustomData
- load EntQuery with pagination, filtering, ordering and all the bells and whistles of the generated queries.
We'll be using the following schema in all the examples below:
export default class Todo extends BaseEntSchema {
fields: Field[] = [
StringType({ name: "Text" }),
BooleanType({
name: "Completed",
index: true,
defaultValueOnCreate: () => {
return false;
},
}),
UUIDType({
name: "creatorID",
foreignKey: { schema: "Account", column: "ID" },
}),
];
}
CustomQuery
Each Ent has generated static methods loadCustom
and loadCustomData
which take CustomQuery
which can be used to customize the query sent to the database.
declare type CustomQuery = string | rawQueryOptions | clause.Clause | QueryDataOptions;
query with string
To query all completed todos in the schema, you can do the following:
// sqlite query
const closedTodos = await Todo.loadCustom(
account.viewer,
`SELECT * FROM todos where completed = 1`,
);
// postgres
const closedTodos = await Todo.loadCustom(
account.viewer,
`SELECT * FROM todos where completed = true`,
);
query with clause
To query all open todos of a user:
const openTodos = await Todo.loadCustom(
account.viewer,
query.And(query.Eq("creator_id", account.id), query.Eq("completed", false)),
);
query with raw parameterized query
// sqlite
// like the first example but using prepared queries
const closedTodos = await Todo.loadCustom(account.viewer, {
query: `SELECT * FROM todos where completed = ?`,
values: [1],
});
// postgres
const closedTodos = await Todo.loadCustom(account.viewer, {
query: `SELECT * FROM todos where completed = $1`,
values: [true],
});
This uses prepared queries.
query and orderby
const orderedOpenedTodos = await Todo.loadCustom(account.viewer, {
clause: query.And(
query.Eq("creator_id", account.id),
query.Eq("completed", false),
),
orderby: "created_at desc",
});
Other options supported here are:
interface QueryDataOptions {
distinct?: boolean;
clause: clause.Clause;
orderby?: string;
groupby?: string;
limit?: number;
}
loadCustomData
Any of the above can be used with loadCustomData
instead of loadCustom
to just fetch the raw data instead of the ents:
const closedTodos: Data[] = await Todo.loadCustomData({
clause: query.Eq("completed", false),
query: `SELECT * FROM todos where completed = ?`,
values: [1],
});
expose to graphql
To expose the Ent accessors above to GraphQL as a list, use gqlField.
export class Account extends AccountBase {
@gqlField({ name: "openTodosPlural", type: "[Todo]" })
async openTodosPlural() {
return await Todo.loadCustom(
this.viewer,
query.And(query.Eq("creator_id", this.id), query.Eq("completed", false)),
);
}
Custom EntQuery
Fetching all objects in a custom query isn't always desired because as usage of your application grows, the amount of data that could be returned is a lot. We provide the ability to return an EntQuery which can be exposed as a GraphQL Connection that follows the Relay Spec.
To do so, we need to be aware of the following concepts:
Here's what the end product looks like here:
const openTodosLoader = new QueryLoaderFactory({
...Todo.loaderOptions(),
// or tableName: Todo.loaderOptions().tableName
groupCol: "creator_id",
clause: query.Eq("completed", false),
toPrime: [todoLoader],
});
const openTodosCountLoader = new RawCountLoaderFactory({
...Todo.loaderOptions(),
// or tableName: Todo.loaderOptions().tableName
groupCol: "creator_id",
clause: query.Eq("completed", false),
});
export class AccountToOpenTodosQuery extends CustomEdgeQueryBase<Todo> {
constructor(viewer: Viewer, src: ID | Account) {
super(viewer, {
src,
countLoaderFactory: openTodosCountLoader,
dataLoaderFactory: openTodosLoader,
options: Todo.loaderOptions(),
});
}
}
CustomEdgeQueryBase
This is the base class of a custom EntQuery that needs to be implemented. This is what's used by the generated index based queries.
The relevant API is as follows:
interface CustomEdgeQueryOptions<T extends Ent> {
src: Ent | ID;
countLoaderFactory: LoaderFactory<ID, number>;
dataLoaderFactory: ConfigurableLoaderFactory<ID, Data[]>;
options: LoadEntOptions<T>;
sortColumn?: string;
}
declare class CustomEdgeQueryBase<TDest extends Ent> extends BaseEdgeQuery<TDest, Data> {
constructor(viewer: Viewer, options: CustomEdgeQueryOptions<TDest>);
}
CustomEdgeQueryOptions has the following properties:
src
: The source ent of the querycountLoaderFactory
: LoaderFactory used to get therawCount
dataLoaderFactory
: LoaderFactory used to get the data at the end of the queryoptions
: used to load the ents at the end of the query. The generatedEnt.loaderOptions()
method suffices heresortColumn
: optional. Column in the database to sort by. If not provided, uses thecreated_at
field.
expose query to graphql
To expose the custom ent query above to GraphQL as a connection, use gqlConnection
.
export class Account extends AccountBase {
@gqlField({ name: "openTodos", type: gqlConnection("Todo") })
openTodos() {
return new AccountToOpenTodosQuery(this.viewer, this);
}
}