# Query Building

Query building a major aspect of requesting data that you require. With it, you could translate your data requirement for example:

Get all users who have a shift starting tomorrow between 6am and 10am.

Into something similar to:

const morningWorkers = await User
    .has(['shifts'])
    .whereBetween('shifts.starts_at', [tomorrow().hour(6).toISOString(), tomorrow().hour(10).toISOString()])
    .get();
1
2
3
4

Which will generate request url similar to the described Shape of the request the API service class generates.

# Customising the generated query string

Given the above may not follow the desired namings for the parameters, you could customise the result by implementing the provided FormatsQueryParameters interface.

// User.ts
import { Model, FormatsQueryParameters, QueryParams } from "@upfrontjs/framework";

export default class User extends Model implements FormatsQueryParameters {
    /**
     * Transform the default query string keys.
     *
     * @param {QueryParams} parameters
     */
    public formatQueryParameters(parameters: QueryParams): Record<string, any> {
        if (parameters.wheres) {
            parameters.filters = parameters.wheres;
            delete parameters.wheres;
        }
        
        return parameters;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

With the above now instead of using the wheres keyword in the query string it will now be filters.

# Methods

The below methods are all available both on the instance and statically on the models. Exceptions are the methods starting with or. They're only available on the instance.

import User from '@Models/User';

User.where(...); // correct
(new User).where(...); // correct

User.orWhere(...); // incorrect
(new User).orWhere(...); // correct
1
2
3
4
5
6
7

All methods return the instance allowing chaining.


# Wheres


# where

The where method adds a where query to the next request. It takes 2-4 arguments. If 2 arguments given the operator assumed to be '=' and the boolean operator to be 'and'. The arguments in order are the column, operator/value, value, boolean.

import User from '@Models/User';

User.where('column', '=', 1, 'and');
// same as
User.where('column', 1);
1
2
3
4
5

# orWhere

The orWhere method adds a where query to the next request with the boolean operator set to 'or'.

import User from '@Models/User';

User.where('column', 1).orWhere('column', 2);
1
2
3

# whereKey

The whereKey method adds a where query to the next request with the column set to the value of the getKeyName. You may give the method a single, or an array of numbers or strings.

import User from '@Models/User';

User.whereKey(1);
User.whereKey('3c1513e5-924e-47d2-bf4b-e6baf9adf2f9');
1
2
3
4

# orWhereKey

The orWhereKey method adds a where query to the next request with the column set to the value of the getKeyName and the boolean operator set to 'or'. You may give the method a single, or an array of numbers or strings.

import User from '@Models/User';

User.whereKey(1).orWhereKey(1);
1
2
3

# orWhereKeyNot

The orWhereKeyNot method adds a where query to the next request with the column set to the value of the getKeyName and the boolean operator set to 'or' and the operator to '!='. You may give the method a single, or an array of numbers or strings.

import User from '@Models/User';

User.orWhereKeyNot(1);
1
2
3

# whereNull

The whereNull method adds a where query to the next request with the value set to 'null'. You may give the method a single, or an array of numbers or strings.

import User from '@Models/User';

User.whereNull('column');
1
2
3

# orWhereNull

The orWhereNull method adds a where query to the next request with the value set to 'null' and the boolean operator set to 'or'. You may give the method a single, or an array of numbers or strings.

import User from '@Models/User';

User.orWhereNull('column');
1
2
3

# orWhereNotNull

The orWhereNotNull method adds a where query to the next request with the value set to 'null', the boolean operator set to 'or' and the operator set to '!='. You may give the method a single, or an array of numbers or strings.

import User from '@Models/User';

User.orWhereNotNull('column');
1
2
3

# whereIn

The whereIn method adds a where query to the next request with the operator set to 'in'.

import User from '@Models/User';

User.whereIn('column', [1, 2, 3]);
1
2
3

# orWhereIn

The orWhereIn method adds a where query to the next request with the operator set to 'in' and the boolean operator set to 'or'.

import User from '@Models/User';

User.orWhereIn();
1
2
3

# whereNotIn

The whereNotIn method adds a where query to the next request with the operator set to 'notIn'.

import User from '@Models/User';

User.whereNotIn();
1
2
3

# orWhereNotIn

The orWhereNotIn method adds a where query to the next request with the operator set to 'notIn' and the boolean operator set to 'or'.

import User from '@Models/User';

User.orWhereNotIn();
1
2
3

# whereBetween

The whereBetween method adds a where query to the next request with the operator set to 'between'.

import User from '@Models/User';

User.whereBetween('column', [1, 3]);
1
2
3

# orWhereBetween

The orWhereBetween method adds a where query to the next request with the operator set to 'between' and the boolean operator set to 'or'.

import User from '@Models/User';

User.orWhereBetween('column', [1, 3]);
1
2
3

# whereNotBetween

The whereNotBetween method adds a where query to the next request with the operator set to 'notBetween'

import User from '@Models/User';

User.whereNotBetween('column', [1, 3]);
1
2
3

# orWhereNotBetween

The orWhereNotBetween method adds a where query to the next request with the operator set to 'notBetween' and the boolean operator set to 'or'.

import User from '@Models/User';

User.orWhereNotBetween('column', [1, 3]);
1
2
3

# Miscellaneous


# limit

The limit method adds a limit constraint to the next request indicating that only this many records are expected.

import User from '@Models/User';

User.limit(5);
1
2
3

# when

The when method calls the given closure when the first argument evaluates to a truthy value, allowing for adding constraints conditionally without breaking the method chaining.

import User from '@Models/User';

User.when(() => true, model => model.whereKey(1));
1
2
3

# unless

The unless method calls the given closure when the first argument evaluates to a falsy value, allowing for adding constraints conditionally without breaking the method chaining.

import User from '@Models/User';

User.unless(() => false, model => model.whereKey(1));
1
2
3

# distinct

The distinct method adds a distinct constraint to the next request indicating that only distinct records are expected.

import User from '@Models/User';

User.distinct();
1
2
3

# select

The select method indicates to the backend that only the following columns/properties are expected to be returned from the record.

import User from '@Models/User';

User.select('id');
User.select(['id', 'name']);
1
2
3
4

# has

The has method adds a constraint to the next request indicating to only return records that has existing relations for the given relation name.

import User from '@Models/User';

User.has('shifts');
1
2
3

# with

The with method indicates to the backend to return the records with the given relations.

import User from '@Models/User';

User.with('shifts');
User.with(['shifts', 'team']);
1
2
3
4

# without

The without method indicates to the backend to return the records without the given relations in case it always returns with them.

import User from '@Models/User';

User.without('shifts');
User.without(['shifts', 'team']);
1
2
3
4

# scope

The scope method indicated to the backend to apply the given scopes/filters to your query.

import User from '@Models/User';

User.scope('experiencedDriver');
1
2
3

# orderBy

The orderBy method indicates to the backend to order the returned records by the given column in the given direction. The second argument can take 2 possible values: 'asc' and 'desc' with 'asc' being the default value.

import User from '@Models/User';

User.orderBy('column');
User.orderBy('column', 'desc');
1
2
3
4

# orderByDesc

The orderByDesc method indicates to the backend to order the returned records by the given column in a descending order.

import User from '@Models/User';

User.orderByDesc('column');
1
2
3

# latest

The latest method is an alias of the orderBy method using a descending order. You may optionally specify which column to order by with the default being the result of the getCreatedAtColumn method.

import User from '@Models/User';

User.latest();
User.latest('signed_up_at');
1
2
3
4

# oldest

The oldest method is an alias of the orderBy method using an ascending order. You may optionally specify which column to order by with the default being the result of the getCreatedAtColumn method.

import User from '@Models/User';

User.oldest();
User.oldest('signed_up_at');
1
2
3
4

# offset

The offset method indicates to the backend to return the records starting from the given offset.

import User from '@Models/User';

User.offset(10);
1
2
3

# skip

The skip method is an alias of the offset method

import User from '@Models/User';

User.skip(10);
1
2
3

# newQuery

static only

The newQuery is a static method that returns the builder. You're not expected to use it as all methods are already available statically as well.

import User from '@Models/User';

User.newQuery(); // The query builder
1
2
3

# Properties

# withRelations

The withRelations is a property that specifies which relations should be set to be included in every request. It merges the unique values set by the with method and removes the values specified by the without method.

// User.js
import { Model } from '@upfrontjs/framework';

export default class User extends Model {
    withRelations = [
        'relation1',
        'relation2'
    ];
}
1
2
3
4
5
6
7
8
9