Data objects

Overview

Preside Data Objects are the data layer implementation for Preside. Just about everything in the system that persists data to the database uses Preside Data Objects to do so.

The Preside Data Objects system is deeply integrated into the CMS:

• Input forms and other administrative GUIs can be automatically generated for your preside objects
• Data object views provide a way to present your data to end users without the need for handler or service layers
• The Data Manager provides a GUI for managing your client specific data and is based on entirely on Preside Data Objects
• Your preside objects can have their data tied to individual Working with multiple sites, without the need for any extra programming of site filters.

The following guide is intended as a thorough overview of Preside Data Objects. For API reference documentation, see Preside Object Service.

Object CFC Files

Data objects are represented by ColdFusion Components (CFCs). A typical object will look something like this:

component {
    property name="name"          type="string" dbtype="varchar" maxlength="200" required=true;
    property name="email_address" type="string" dbtype="varchar" maxlength="255" required=true uniqueindexes="email";

    property name="tags" relationship="many-to-many" relatedto="tag";
}

A singe CFC file represents a table in your database. Properties defined using the property tag represent fields and/or relationships on the table.

Database table names

By default, the name of the database table will be the name of the CFC file prefixed with pobj_. For example, if the file was person.cfc, the table name would be pobj_person.

You can override these defaults with the tablename and tableprefix attributes:

/**
 * @tablename   mytable
 * @tableprefix mysite_
 */
component {
    // .. etc.
}

Registering objects

The system will automatically register any CFC files that live under the /application/preside-objects folder of your site (and any of its sub-folders). Each .cfc file will be registered with an ID that is the name of the file without the ".cfc" extension.

For example, given the directory structure below, four objects will be registered with the IDs blog, blogAuthor, event, eventCategory:

/application
    /preside-objects
        /blogs
            blog.cfc
            blogAuthor.cfc
        /events
            event.cfc
            eventCategory.cfc

Extensions and core objects

For extensions, the system will search for CFC files in a /preside-objects folder at the root of your extension.

Core system Preside Objects can be found at /preside/system/preside-objects.

Properties

Properties represent fields on your database table or mark relationships between objects (or both).

Attributes of the properties describe details such as data type, data length and validation requirements. At a minimum, your properties should define a name, type and dbtype attribute. For varchar fields, a maxLength attribute is also required. You will also typically need to add a required attribute for any properties that are a required field for the object:

component {
    property name="name"          type="string"  dbtype="varchar" maxLength="200" required=true;
    property name="max_delegates" type="numeric" dbtype="int"; // not required
}

Standard attributes

While you can add any arbitrary attributes to properties (and use them for your own business logic needs), the system will interpret and use the following standard attributes:

Default properties

The bare minimum code requirement for a working Preside Data Object is:

component {}

Yes, you read that right, an "empty" CFC is an effective Preside Data Object. This is because, by default, Preside Data Objects will be automatically given id, label, datecreated and datemodified properties. The above example is equivalent to:

component {
    property name="id"           type="string" dbtype="varchar"   required=true maxLength="35" generator="UUID" pk=true;
    property name="label"        type="string" dbtype="varchar"   required=true maxLength="250";
    property name="datecreated"  type="date"   dbtype="datetime" required=true;
    property name="datemodified" type="date"   dbtype="datetime" required=true;
}

The ID Field

The ID field will be the primary key for your object. We have chosen to use a UUID for this field so that data migrations between databases are achievable. If, however, you wish to use an auto incrementing numeric type for this field, you could do so by overriding the type, dbtype and generator attributes:

component {
    property name="id" type="numeric" dbtype="int" generator="increment";
}

The same technique can be used to have a primary key that does not use any sort of generator (you would need to pass your own IDs when inserting data):

component {
    property name="id" generator="none";
}

The Label field

The label field is used by the system for building automatic GUI selectors that allow users to choose your object records.

If you wish to use a different property to represent a record, you can use the labelfield attribute on your CFC, e.g.:

/**
 * @labelfield title
 *
 */
component {
    property name="title" type="string" dbtype="varchar" maxlength="100" required=true;
    // etc.
}

If you do not want your object to have a label field at all (i.e. you know it is not something that will ever be selectable, and there is no logical field that might be used as a string representation of a record), you can add a nolabel=true attribute to your CFC:

/**
 * @nolabel true
 *
 */
component {
    // ... etc.
}

The DateCreated and DateModified fields

These do exactly what they say on the tin. If you use the APIs to insert and update your records, the values of these fields will be set automatically for you.

Default values for properties

You can use the default attribute on a property tag to define a default value for a property. This value will be used during an insertData() operation when no value is supplied for the property. E.g.

component {
    // ...
    property name="max_attendees" type="numeric" dbtype="int" required=false default=100;
}

Dynamic defaults

Default values can also be generated dynamically at runtime. Currently, this comes in two flavours:

1. Supplying raw CFML to be evaluated at runtime
2. Supplying the name of a method defined in your object that will be called at runtime, this method will be passed a 'data' argument that is a structure containing the data to be inserted

For raw CFML, prefix your value with cfml:, e.g. cfml:CreateUUId(). For methods that are defined on your object, use method:methodName. e.g.

component  {
    // ...
    property name="event_start_date" type="date"   dbtype="date"                      required=false default="cfml:Now()";
    property name="slug"             type="string" dbtype="varchar"   maxlength="200" required=false default="method:calculateSlug";

    public string function calculateSlug( required struct data ) {
        return LCase( ReReplace( data.label ?: "", "\W", "_", "all" ) );
    }
}

Generated fields

As of 10.8.0, generators allow you to dynamically generate the value of a property when a record is first being inserted and, optionally, when a record is updated. The generate attribute of a property dictates when to use a generator. Valid values are:

• never (default), never generate the value
• insert, only generate a value when a record is first inserted
• always, generate a value on both insert and update of records

The generator attribute itself then allows you to use a system pre-defined generator or use your own by prefixing the generator with method: (the method name that follows should be defined on your object). For example:

component {
    // ...

    property name="alternative_pk"   type="string" dbtype="varchar" maxlength=35 generate="insert" generator="UUID";
    property name="description"      type="string" dbtype="text";
    property name="description_hash" type="string" dbtype="varchar" maxlength=32 generate="always" generator="method:hashDescription";

    // ...

    // The method will receive a single argument that is the struct
    // of data passed to the insertData() or updateData() methods
    public any function hashDescription( required struct changedData ) {
        if ( changedData.keyExists( "description" ) ) {
            if ( changedData.description.len() ) {
                return Hash( changedData.description );
            }

            return "";
        }
        return; // return NULL to not alter the value when no description is being updated
    }
}

The core system provides you with these named generators:

• UUID - uses CreateUUId() to generate a UUID for your field. This is used by default for the primary key in preside objects.
• timestamp - uses Now() to auto generate a timestamp for your field
• hash - used in conjunction with a generateFrom attribute that should be a list of other properties which to concatenate and generate an MD5 hash from
• nextint - introduced in 10.12.0, gives the next incremental integer value for the field
• slug - takes an optional generateFrom attribute that defines which field (if present in the submitted data) should be used to generate the slug; by default it will use the object's label field. A unique slug will be generated, so may be suffixed with -1, -2, etc.

Developer provided generators

As of 10.13.0, you are able to create convention based handler actions for generators. The convention based handler name for any generator is generators.{generatorname}.

For example, the property below would attempt to use a handler action of generators.my.generator, i.e. a file /handlers/generators/My.cfc with a generator() method.

property name="is_cool" ... generator="my.generator";

Your handler action will receive an args struct in the arguments with the following keys:

• objectName: the name of the object whose record is being added/updated
• id: the ID of the record (for updates only)
• generator: the full generator string used
• data: a struct with the data being passed to the insert/update operation
• prop: a struct with all the property attributes of the property whos value is being generated

Example

component {

    private boolean function generator( event, rc, prc, args={} ) {
        return IsTrue( args.data.under_thirty ?: "" ) && ( ( args.status ?: "" ) == "active" );
    }

}

Formula fields

Properties that define a formula are not generated as fields in your database tables. Instead, they are made available to your application to be selected in selectData queries. The value of the formula attribute should be a valid SQL statement that can be used in a SQL select statement and include ${prefix} tokens before any field definitions (see below for an explanation). For example:

/**
 * @datamanagerGridFields title,comment_count,datemodified
 *
 */
component {
    // ...

    property name="comments" relationship="one-to-many" relatedto="article_comment";
    property name="comment_count" formula="Count( distinct ${prefix}comments.id )" type="numeric";

    // ...
}

articles = articleDao.selectData(
    selectFields = [ "id", "title", "comment_count" ]
);

Formula fields can also be used in your DataManager data grids and be assigned labels in your object's i18n .properties file.

Formula ${prefix} token

The ${prefix} token in formula fields allows your formula field to be used in more complex select queries that traverse your data model's relationships. Another example, this time a person cfc:

component {
    // ...
    property name="first_name" ...;
    property name="last_name"  ...;

    property name="full_name" formula="Concat( ${prefix}first_name, ' ', ${prefix}last_name )";
    // ...
}

Now, let us imagine we have a company object, with an "employees" one-to-many property that relates to our person object above. We may want to select employees from a company:

var employees = companyDao.selectData(
      id           = arguments.companyId
    , selectFields = [ "employees.id", "employees.full_name" ]
);

The ${prefix} token allows us to take the employees. prefix of the full_name field and replace it so that the final select SQL becomes: Concat( employees.first_name, ' ', employees.last_name ). Without a ${prefix} token, your formula field will only work when selecting directly from the object in which the property is defined, it will not work when traversing relationships as with the example above.

Aggregate functions in formula fields

As of 10.23.0, a new syntax for aggregate functions within formula fields is available, which gives significant performance gains in the generated SQL queries.

Whereas previously you may have written:

property name="comment_count"        type="numeric" formula="count( distinct ${prefix}comments.id )";
property name="latest_comment_reply" type="date"    formula="max( ${prefix}comments$replies.date )";

...these would now be written like this:

property name="comment_count"        type="numeric" formula="agg:count{ comments.id }";
property name="latest_comment_reply" type="date"    formula="agg:max{ comments$replies.date }";

The syntax takes the form agg: followed by the aggregate function name (count, min, max, sum, avg) and then the property to be aggregated contained within curly braces {}. Note that ${prefix} is not required.

The existing syntax will still work, but the new syntax should provide improved performance - especially when multiple formulas are included in the same query, and when the volumes of data involved grow larger. Existing count() formulae will automatically be detected and will make use of the optimisation.

ENUM properties

Properties defined with an enum attribute implement an application enforced ENUM system. Named ENUM types are defined in your application's Config.cfc and can then be attributed to a property which then automatically limits and validates the options that are available to the field. ENUM options are saved to the database as a plain string; we avoid any mapping with integer values to keep the implementation portable and simple. Example ENUM definitions in Config.cfc:

settings.enum = {};
settings.enum.redirectType                = [ "301", "302" ];
settings.enum.pageAccessRestriction       = [ "inherit", "none", "full", "partial" ];
settings.enum.pageIframeAccessRestriction = [ "inherit", "block", "sameorigin", "allow" ];

In addition to the Config.cfc definition, each ENUM type should have a corresponding .properties file to define the labels and optional description of each item. The file must live at /i18n/enum/{enumTypeId}.properties. For example:

# /i18n/enum/redirectType.properties
301.label=301 Moved Permanently
301.description=A 301 redirect indicates that the resource has been *permanently* moved to the new locations. This is particularly important to use for moved content as it instructs search engines to index the new location, potentially without losing any SEO rankings. Browsers will aggressively cache these redirects to avoid wasted calls to a URL that it has been told is moved.

302.label=302 Found (Temporary redirect)
302.description=A 302 redirect indicates that the resource has been *temporarily* moved to the new location. Use this only when you know that you will/might reinstate the original source URL at some point in time.

Defining relationships with properties

Relationships are defined on property tags using the relationship and relatedTo attributes. For example:

// eventCategory.cfc
component {}

// event.cfc
component {
    property name="category" relationship="many-to-one" relatedto="eventCategory" required=true;
}

If you do not specify a relatedTo attribute, the system will assume that the foreign object has the same name as the property field. For example, the two objects below would be related through the eventCategory property of the event object:

// eventCategory.cfc
component {}

// event.cfc
component {
    property name="eventCategory" relationship="many-to-one" required=true;
}

One to Many relationships

In the examples, above, we define a one to many style relationship between event and eventCategory by adding a foreign key property to the event object.

The category property will be created as a field in the event object's database table. Its datatype will be automatically derived from the primary key field in the eventCategory object and a Foreign Key constraint will be created for you.

You can also declare the relationship on the other side (i.e. the 'one' side). This will allow you to traverse the relationship from either angle. e.g. we could add a 'one-to-many' property on the eventCategory.cfc object; this will not create a field in the database table, but will allow you to query the relationship from the category viewpoint:

// eventCategory.cfc
component {
    // note that the 'relationshipKey' property is the FK in the event object
    // this will default to the name of this object
    property name="events" relationship="one-to-many" relatedTo="event" relationshipKey="eventCategory";
}

// event.cfc
component {
    property name="eventCategory" relationship="many-to-one" required=true;
}

Many to Many relationships

If we wanted an event to be associated with multiple event categories, we would want to use a Many to Many relationship:

// eventCategory.cfc
component {}

// event.cfc
component {
    property name="eventCategory" relationship="many-to-many";
}

In this scenario, there will be no eventCategory field created in the database table for the event object. Instead, a "pivot" database table will be automatically created that looks a bit like this (in MySQL):

-- table name derived from the two related objects, delimited by __join__
create table `pobj_event__join__eventcategory` (
    -- table simply has a field for each related object
      `event`         varchar(35) not null
    , `eventcategory` varchar(35) not null

    -- plus we always add a sort_order column, should you care about
    -- the order in which records are related
    , `sort_order`    int(11)     default null

    -- unique index on the event and eventCategory fields
    , unique key `ux_event__join__eventcategory` (`event`,`eventcategory`)

    -- foreign key constraints on the event and eventCategory fields
    , constraint `fk_1` foreign key (`event`        ) references `pobj_event`         (`id`) on delete cascade on update cascade
    , constraint `fk_2` foreign key (`eventcategory`) references `pobj_eventcategory` (`id`) on delete cascade on update cascade
) ENGINE=InnoDB;

"Advanced" Many to Many relationships

You can excert a little more control over your many-to-many relationships by making use of some extra, non-required, attributes:

// event.cfc
component {
    property name                 = "eventCategory"
             relationship         = "many-to-many"
             relatedTo            = "eventCategory"
             relationshipIsSource = false              // the event object is regarded as the 'target' side of the relationship rather than the 'source' (default is 'source' when relationship defined in the object)
             relatedVia           = "event_categories" // create a new auto pivot object called "event_categories" rather than the default "event__join__eventCategory"
             relatedViaSourceFk   = "cat"              // name the foreign key field to the source object (eventCategory) to be just 'cat'
             relatedViaTargetFk   = "ev";              // name the foreign key field to the target object (event) to be just 'ev'
}

TODO: explain these in more detail. In short though, these attributes control the names of the pivot table and foreign keys that get automatically created for you. If you leave them out, Preside will figure out sensible defaults for you.

As well as controlling the automatically created pivot table name with "relatedVia", you can also use this attribute to define a relationship that exists through a pre-existing pivot object.

Subquery relationships with "SelectData Views"

In 10.11.0 the concept of SelectData views was introduced. These 'views' are loosely synonymous with SQL views in that they allow you to store a complex query and reference it by a simple name.

They can be used in relationship helper properties and result in subqueries being created when querying them. The syntax is the same as that of a one-to-many relationship:

component {
    property name="active_posts" relationship="select-data-view" relatedTo="activePosts" relationshipKey="blog_category";
}

See SelectData views for more.

Defining indexes and unique constraints

The Preside Object system allows you to define database indexes on your fields using the indexes and uniqueindexes attributes. The attributes expect a comma separated list of index definitions. An index definition can be either an index name or combination of index name and field position, separated by a pipe character. For example:

// event.cfc
component {
    property name="category" indexes="category,categoryName|1" required=true relationship="many-to-one" ;
    property name="name"     indexes="categoryName|2"          required=true type="string" dbtype="varchar" maxlength="100";
    // ...
}

The example above would result in the following index definitions:

create index ix_category     on pobj_event( category );
create index ix_categoryName on pobj_event( category, name );

The exact same syntax applies to unique indexes, the only difference being the generated index names are prefixed with ux_ rather than ix_.

Keeping in sync with the database

When you reload your application, the system will attempt to synchronize your object definitions with the database. While it does a reasonably good job at doing this, there are some considerations:

• If you add a new, required, field to an object that has existing data in the database, an exception will be raised. This is because you cannot add a NOT NULL field to a table that already has data. You will need to provide upgrade scripts to make this type of change to an existing system.

• When you delete properties from your objects, the system will rename the field in the database to _deprecated_yourfield. This prevents accidental loss of data but can lead to a whole load of extra fields in your DB during development.

• The system never deletes whole tables from your database, even when you delete the object file

Working with the API

The PresideObjectService service object provides methods for performing CRUD operations on the data along with other useful methods for querying the metadata of each of your data objects. There are two ways in which to interact with the API:

1. Obtain an instance the PresideObjectService and call its methods directly
2. Obtain an "auto service object" for the specific object you wish to work with and call its decorated CRUD methods as well as any of its own custom methods

You may find that all you wish to do is to render a view with some data that is stored through the Preside Object service. In this case, you can bypass the service layer APIs and use the presidedataobjectviews system instead.

Getting an instance of the Service API

We use Wirebox to auto wire our service layer. To inject an instance of the service API into your service objects and/or handlers, you can use wirebox's "inject" syntax as shown below:

// a handler example
component {
    property name="presideObjectService" inject="presideObjectService";

    function index( event, rc, prc ) {
        prc.eventRecord = presideObjectService.selectData( objectName="event", id=rc.id ?: "" );

        // ...
    }
}

// a service layer example
// (here at Pixl8, we prefer to inject constructor args over setting properties)
component {

    /**
     * @presideObjectService.inject presideObjectService
     */
     public any function init( required any presideObjectService ) {
        _setPresideObjectService( arguments.presideObjectService );

        return this;
     }

     public query function getEvent( required string id ) {
        return _getPresideObjectService().selectData(
              objectName = "event"
            , id         = arguments.id
        );
     }

     // we prefer private getters and setters for accessing private properties, this is our house style
     private any function _getPresideObjectService() {
         return variables._presideObjectService;
     }
     private void function _setPresideObjectService( required any presideObjectService ) {
         variables._presideObjectService = arguments.presideObjectService;
     }

}

Using Auto Service Objects

An auto service object represents an individual data object. They are an instance of the given object that has been decorated with the service API CRUD methods.

Calling the CRUD methods works in the same way as with the main API with the exception that the objectName argument is no longer required. So:

record = presideObjectService.selectData( objectName="event", id=id );

// is equivalent to:
eventObject = presideObjectService.getObject( "event" );
record      = eventObject.selectData( id=id );

Getting an auto service object

This can be done using either the getObject() method of the Preside Object Service or by using a special Wirebox DSL injection syntax, i.e.

// a handler example
component {
    property name="eventObject" inject="presidecms:object:event";

    function index( event, rc, prc ) {
        prc.eventRecord = eventObject.selectData( id=rc.id ?: "" );

        // ...
    }
}

// a service layer example
component {

    /**
     * @eventObject.inject presidecms:object:event
     */
     public any function init( required any eventObject ) {
        _setPresideObjectService( arguments.eventObject );

        return this;
     }

     public query function getEvent( required string id ) {
        return _getEventObject().selectData( id = arguments.id );
     }

     // we prefer private getters and setters for accessing private properties, this is our house style
     private any function _getEventObject() {
         return variables._eventObject;
     }
     private void function _setEventObject( required any eventObject ) {
         variables._eventObject = arguments.eventObject;
     }

}

CRUD Operations

The service layer provides core methods for creating, reading, updating and deleting records (see individual method documentation for reference and examples):

• selectData()
• insertData()
• updateData()
• deleteData()

In addition to the four core methods above, there are also further utility methods for specific scanarios:

• dataExists()
• selectManyToManyData()
• syncManyToManyData()
• getDeNormalizedManyToManyData()
• getRecordVersions()
• insertDataFromSelect()

Specifying fields for selection

The selectData() method accepts a selectFields argument that can be used to specify which fields you wish to select. This can be done by the field's name or one of it's aliasses. This can be used to select properties on your object as well as properties on related objects and any plain SQL aggregates or other SQL operations. For example:

records = newsObject.selectData(
    selectFields = [ "news.id", "news.title", "Concat( category.label, category$tag.label ) as catandtag"  ]
);

The example above would result in SQL that looked something like:

select      news.id
          , news.title
          , Concat( category.label, tag.label ) as catandtag

from        pobj_news     as news
inner join  pobj_category as category on category.id = news.category
inner join  pobj_tag      as tag      on tag.id      = category.tag

Filtering data

All but the insertData() methods accept a data filter to either refine the returned recordset or the records to be updated / deleted. The API provides two arguments for filtering, filter and filterParams. Depending on the type of filtering you need, the filterParams argument will be optional.

Simple filtering

A simple filter consists of one or more strict equality checks, all of which must be true. This can be expressed as a simple CFML structure; the structure keys represent the object fields; their values represent the expected record values:

records = newsObject.selectData( filter={
      category             = chosenCategory
    , "category$tag.label" = "red"
} );

Complex filters

More complex filters can be achieved with a plain SQL filter combined with filter params to make use of parametized SQL statements:

records = newsObject.selectData(
      filter       = "category != :category and DateDiff( publishdate, :publishdate ) > :daysold and category$tag.label = :category$tag.label"
    , filterParams = {
           category             = chosenCategory
         , publishdate          = publishDateFilter
         , "category$tag.label" = "red"
         , daysOld              = { type="integer", value=3 }
      }
);

Multiple filters

In addition to the filter and filterParams arguments, you can also make use of an extraFilters argument that allows you to pass an array of structs, each with a filter and optional filterParams key. All filters will be combined using a logical AND:

records = newsObject.selectData(
    extraFilters = [{
          filter = { active=true }
    },{
          filter       = "category != :category and DateDiff( publishdate, :publishdate ) > :daysold and category$tag.label = :category$tag.label"
        , filterParams = {
               category             = chosenCategory
             , publishdate          = publishDateFilter
             , "category$tag.label" = "red"
             , daysOld              = { type="integer", value=3 }
          }

    } ]
);

Pre-saved filters

Developers are able to define named filters that can be passed to methods in an array using the savedFilters argument, for example:

records = newsObject.selectData( savedFilters = [ "activeCategories" ] );

These filters can be defined either in your application's Config.cfc file or, as of 10.11.0, by implementing a convention based handler. In either case, the named filter should resolve to a struct with filter and filterParams keys that follow the same rules documented above.

Defining saved filters in Config.cfc

A saved filter is defined using the settings.filters struct. A filter can either be a struct, with filter and optional filterParams keys, or an inline function that returns a struct:

settings.filters.activeCategories = {
      filter       = "category.active = :category.active and category.pub_date > Now()"
    , filterParams = { "category.active"=true }
};

// or:

settings.filters.activeCategories = function( struct args={}, cbController ) {
    return cbController.getWirebox.getInstance( "categoriesService" ).getActiveCategoriesFilter();
}

Defining saved filters using handlers

As of 10.11.0, these filters can be defined by convention by implementing a private coldbox handler at DataFilters.filterName. For example, to implement a activeCategories filter:

// /handlers/DataFilters.cfc
component {

    property name="categoriesService" inject="categoriesService";

    private struct function activeCategories( event, rc, prc, args={} ) {
        return categoriesService.getActiveCategoriesFilter();

        // or

        return {
              filter       = "category.active = :category.active and category.pub_date > :category.pub_date"
            , filterParams = { "category.active"=true, "category.pub_date"=Now() }
        }
    }

}

Default filters

As of 10.11.0, developers can use saved filters as default filters. Default filters are filters that will be automatically applied to selectData().

Using default filters

Default filters can be applied by passing a list of saved filters to the @defaultFilters annotations in the object file. For example:

/**
 * @defaultFilters publishedStuff,approvedStuff
 */
component {
    // ...
}

Ignoring default filters

In case of needing to ignore the default filters, developers need to pass an array of default filters that wished to be ignored to ignoreDefaultFilters argument in their selectData(). For example:

allRecords = recordObject.selectData( ignoreDefaultFilters = [ "publishedStuff", "approvedStuff" ] );

Making use of relationships

As seen in the examples above, you can use a special field syntax to reference properties in objects that are related to the object that you are selecting data from / updating data on. When you do this, the service layer will automatically create the necessary SQL joins for you.

The syntax takes the form: (relatedObjectReference).(propertyName). The related object reference can either be the name of the related object, or a $ delimited path of property names that navigate through the relationships (see examples below).

This syntax can be used in:

• Select fields
• Filters
• Order by statements
• Group by statements

To help with the examples, we'll illustrate a simple relationship between three objects:

// tag.cfc
component {}

// category.cfc
component {
    property name="category_tag" relationship="many-to-one" relatedto="tag"  required=true;
    property name="news_items"   relationship="one-to-many" relatedTo="news" relationshipKey="news_category";
    // ..
}

// news.cfc
component {
    property name="news_category" relationship="many-to-one" relatedto="category" required=true;
    // ..
}

Auto join example

// update news items whose category tag = "red"
presideObjectService.updateData(
      objectName = "news"
    , data       = { archived = true }
    , filter     = { "tag.label" = "red" } // the system will automatically figure out the relationship path between the news object and the tag object
);

Property name examples

// delete news items whose category label = "red"
presideObjectService.deleteData(
      objectName = "news"
    , data       = { archived = true }
    , filter     = { "news_category.label" = "red" }
);

// select title and category tag from all news objects, order by the category tag
presideObjectService.selectData(
      objectName   = "news"
    , selectFields = [ "news.title", "news_category$category_tag.label as tag" ]
    , orderby      = "news_category$category_tag.label"
);

// selecting categories with a count of news articles for each category
presideObjectService.selectData(
      objectName   = "category"
    , selectFields = [ "category.label", "Count( news_items.id ) as news_item_count" ]
    , orderBy      = "news_item_count desc"
);

Caching

By default, all selectData() calls have their recordset results cached. These caches are automatically cleared when the data changes.

You can specify not to cache results with the useCache argument.

Cache per object

As of Preside 10.10.55, an additional feature flag enables the setting of caches per object. This greatly simplifies and speeds up the cache clearing and invalidation logic which may benefit certain application profiles. The feature can be enabled in your Config.cfc with:

settings.features.queryCachePerObject.enabled = true;

Configuration of the defaultQueryCache then becomes the default configuration for each individual object's own cachebox cache instance.

In addition, you can annotate your Preside object with @cacheProvider to use a different cache provider for a specific object. Finally, any other annotation attributes on your object that begin with @cache will be treated as properties of the cache box cache.

A common example may be to set a larger cache for a specific object with different reaping frequency and eviction count:

/**
 * @cacheMaxObjects    10000
 * @cacheReapFrequency 5
 * @cacheEvictCount    2000
 */
component {

}

Extending Objects

Objects with the same name, but from different sources, are merged at runtime so that you can have multiple extensions all contributing to the final object definition.

Take the page object, for example. You might write an extension that adds an allow_comments property to the object. That CFC would look like this:

// /extensions/myextension/preside-objects/page.cfc
component {
    property name="allow_comments" type="boolean" dbtype="boolean" required=false default=true;
}

After adding that code and reloading your application, you would find that the psys_page table now had an allow_comments field added.

Then, in your site, you may have some client specific requirements that you need to implement for all pages. Simply by creating a page.cfc file under your site, you can mix in properties along with the allow_comments mixin above:

// /application/preside-objects/page.cfc
component {
    // remove a property that has been defined elsewhere
    property name="embargo_date" deleted=true;

    // alter attributes of an existing property
    property name="title" maxLength="50"; // strict client requirement?!

    // add a new property
    property name="search_engine_boost" type="numeric" dbtype="integer" minValue=0 maxValue=100 default=0;
}

Versioning

By default, Preside Data Objects will maintain a version history of each database record. It does this by creating a separate database table that is prefixed with _version_. For example, for an object named 'news', a version table named **_version_pobj_news** would be created.

The version history table contains the same fields as its twin as well as a few specific fields for dealing with version numbers, etc. All foreign key constraints and unique indexes are removed.

Opting out

To opt out of versioning for an object, you can set the versioned attribute to false on your CFC file:

/**
 * @versioned false
 *
 */
component {
    // ...
}

Interacting with versions

Various admin GUIs such as the :doc:datamanager implement user interfaces to deal with versioning records. However, if you find the need to create your own, or need to deal with version history records in any other way, you can use methods provided by the service api:

• getRecordVersions()
• getVersionObjectName()
• objectIsVersioned()
• getNextVersionNumber()

In addition, you can specify whether or not you wish to use the versioning system, and also what version number to use if you are, when calling the insertData(), updateData() and deleteData() methods by using the useVersioning and versionNumber arguments.

Finally, you can select data from the version history tables with the selectData() method by using the fromVersionTable, maxVersion and specificVersion arguments.

Many-to-many related data

By default, auto generated many-to-many data tables will be versioned along with your record changes. You can opt out of this by adding a versioned=false attribute to the many-to-many property:

property name="categories" relationship="many-to-many" relatedTo="category" versioned=false;

Inversely, you may have a many-to-many relationship for which you have an explicit join table that you'd like versioned along with the parent record. In this scenario, you can explicitly set versioned=true:

property name="categories" relationship="many-to-many" relatedTo="category" relatedVia="explicit_categories_obj" versioned=true;

Ignoring changes

By default, when the data actually changes in your object, a new version will be created. If you wish certain fields to be ignored when it comes to determining whether or not a new version should be created, you can add a ignoreChangesForVersioning attribute to the property in the preside object.

An example scenario for this might be an object whose data is synced with an external source on a schedule. You may add a helper property to record the last sync check date, if no other fields have changed, you probably don't want a new version record being created just for that sync check date. In this case, you could do:

property name="_last_sync_check" type="date" dbtype="datetime" ignoreChangesForVersioning=true;

Only create versions on update

As of 10.9.0, you are able to specify that a version record is not created on insert. Instead, the first version record will be created on the first update to the record. This allows you to save on unnecessary version records in your database. To do this, add the versionOnInsert=false attribute to you object, e.g.

/**
 * @versioned       true
 * @versionOnInsert false
 */
component {
    // ...
}

Organising data by sites

You can instruct the Preside Data Objects system to organise your objects' data into your system's individual sites (see Working with multiple sites). Doing so will mean that any data reads and writes will be specific to the currently active site.

To enable this feature for an object, simply add the siteFiltered attribute to the component tag:

/**
 * @siteFiltered true
 *
 */
component {
    // ...
}

Flagging an object record

You are able to flag a record for your objects' data. Doing so will mean you able to filter which records are flagged in the object.

To enable this feature for an object, simple add the flagEnabled attribute (disabled by default) to the component tag:

/**
 * @flagEnabled true
 *
 */
component {
    // ...
}

If you wish to use a different property to flag a record, you can use the flagField attribute on your CFC, e.g.:

/**
 * @flagField record_flag
 *
 */
component {
    property name="record_flag" type="boolean" dbtype="boolean" default="0" renderer="none" required=true;
}