Bluestep JS Documentation
    Preparing search index...

    Class AdvancedQueryOptions

    Customizes the SQL of a record query: fragments appended to its SELECT list, its joins, its WHERE and its HAVING, plus how the result is cached and how ? placeholders are bound. Only whereClause and havingClause may hold placeholders; psAction binds them and psActionParameters declares the bound values for the cache key.

    {
       *   selectClause: "count(D"Notes".seqnum) AS adv_note_count",
       *   joinClause: "LEFT JOIN D"Notes" n ON n.seqEntity = entity.seqChild AND n.classIdEntity = entity.classIdChild",
       *   whereClause: "AND D"Personal Info".lastName = ?",
       *   havingClause: "count(D"Notes".seqnum) > ?",
       *   maxAgeMillis: 60000,
       *   psAction: (ps) => {
       *     ps.setString(1, lastName); // ? placeholders number from 1 across whereClause then havingClause
       *     ps.setLong(2, minimumNotes);
       *   },
       *   psActionParameters: [lastName, minimumNotes]
       * }
    Index
    constructor
    havingClause? joinClause? maxAgeMillis? psAction? psActionParameters? selectClause? whereClause?
    havingClause?: string

    An aggregate filter. Implies a GROUP BY over the query's own columns, as an aggregating selectClause does. Its ? placeholders continue the numbering from whereClause.

    joinClause?: string

    Extra joins, appended after the query's own. Name a form with the quote syntax and give it your own alias: LEFT JOIN D"Notes" n ON n.seqEntity = entity.seqChild. No ? placeholders, for the same reason as selectClause.

    maxAgeMillis?: number

    Optional, defaults to -1 (no limit). If set, the query will use a maximum age for caching. Other factors may lower this limit. If set to 0, the query is not cached at all: its rows are neither served from nor stored in the result cache.

    psAction?: (ps: DbViewPreparedStatement) => number | void

    Optional, used for prepared statements. If set, the query executes the psAction function, which is called with the prepared statement and sets one value per ? placeholder in whereClause then havingClause, numbered from 1. Setting an index past those is an error. Any return value is ignored: the placeholders the query holds are what the statement has, not what this function reports having set.

    psActionParameters?: any[]

    The values psAction binds. They are what carries those values into the cache key, so two queries differing only in what they bind are treated as distinct.

    Required whenever psAction is set, unless maxAgeMillis is 0. Without it the cache key does not include the bound values, and a query is served the rows of an earlier query that differed only in them.

    selectClause?: string

    Extra SELECT columns, appended after the query's own. Uses the same quote syntax as whereClause. Every item needs an alias, which is how its value is read back: row.selectValue("adv_total"). An aggregate here (count, sum, json_agg, ...) groups the query by every one of its own columns, so a sort or a report break on a column the query does not display is then an error, reported by the database rather than refused here. No ? placeholders: these columns bind before the query's own parameters, so a value here has no index it could be given.

    whereClause?: string

    A fragment appended to the query's WHERE clause. It must begin with "AND" or "OR" and can contain any valid SQL, including ? placeholders bound by psAction. Tables and fields are named with the quote syntax below, which selectClause, joinClause and havingClause share.

    "A" - Lookup By Form AltId:

    • AND A"SID=FormAltId".D"Field Name" = 'value'
    • OR A"SID=FormAltId"."Field Name" = 'value'
    • AND A"SID=AltId".formulaId = 'value'

    "C" - Lookup Table and Field By Field AltId:

    • AND C"SID=USERNAME" = 'value'

    "D" - Lookup By Display Name:

    • AND D"Display Name".D"Field Name" = 'value'
    • OR D"Display Name"."Field Name" = 'value'
    • AND D"Display Name".formulaId = 'value'

    "F" - Lookup By Formula Form Id:

    • AND F"name".lastName = 'value'

    Raw Table Name:

    • AND "basetable"."classid" = 111111
    • OR basetable.classid = 111111
    • AND ZZZ_DYNOTABLE_456.C3456 = 'value'
    • OR "zzz_dynotable_456"."c3456" = 'value'

    Invalid Examples:

    • invalid: AND D"Display Name".A"SID=USERNAME"
    • invalid: OR D"Display Name".F"SID=USERNAME"
    • invalid: AND F"name".A"SID=USERNAME"
    • invalid: OR F"name".F"SID=USERNAME"

    Settings
    Member Visibility

    On This Page
    Constructors
    Properties