Preside selectData(): the basics — sebduggan.uk
sebduggan.uk

Preside selectData(): the basics

Preside

A little background to start with: Preside is an open-source web development platform, created at Pixl8 Interactive, which runs on the Lucee application server. While at first glance it may look like any other CMS (it was originally called Preside CMS), it does so much more, and can form the hub of some very complex integrations between other applications. The key to this is an incredibly flexible content storage model, and the ability to extend or override almost any aspect of the application to suit your own individual needs.

I've only been using Preside for a few months, and started in my role as a Senior Developer at Pixl8 in December 2016. So I'm constantly learning new things about Preside, which puts me in an ideal position to write about it from the perspective of a newcomer. I expect to write plenty about Preside here in the future!

The foundation of Preside's power is in its data layer implementation, which is based on Preside Data Objects. I may write in the future about aspects of these, but the official guide covers their creation and configuration in a lot of detail. What I'm going to look at here (over the course of several posts) is retrieving data from Preside Objects, which is all done via the selectData() method of the PresideObjectService.

In order to demonstrate selectData(), we'll first need some data. A great source of sample data is the Star Wars Preside Extension, which imports data from The Star Wars API into Preside, and that's what I'll be using throughout these posts.

The simplest query #

The following is as simple as it gets. The only required argument for selectData() is objectName:

cfc
films = presideObjectService.selectData(
	objectName = "starwars_film"
);

This will return every column of every record from the specified object. One thing to note is that -- unlike ORMs and ORM-like approaches -- selectData() returns its data as a query resultset. This gives several benefits, not least of which is the ability to construct some very complex data requests to suit any requirement.

For all these examples, I have injected the PresideObjectService as a property:

cfc
property name="presideObjectService" inject="presideObjectService";

You can, if you want, use an "Auto Service Object", which is the given object decorated with the service API CRUD methods. This can be done either within your method:

cfc
filmObject = presideObjectService.getObject( "starwars_film" );

Or via injection:

cfc
property name="filmObject" inject="presidecms:object:starwars_film";

Your simplest query would then look like this:

cfc
films = filmObject.selectData();

Refining your query #

That's all very well, but it's best usually only to retrieve the columns you require. This is done using the selectFields argument, an array of the columns you want:

cfc
films = presideObjectService.selectData(
	  objectName   = "starwars_film"
	, selectFields = [ "title", "episode_id", "director" ]
);

And orderBy will let you sort your results:

cfc
films = presideObjectService.selectData(
	  objectName   = "starwars_film"
	, selectFields = [ "title", "episode_id", "director" ]
	, orderBy      = "episode_id"
);

orderBy is a simple SQL order by statement, so can be as complex as you like:

cfc
vehicles = presideObjectService.selectData(
	  objectName   = "starwars_vehicle"
	, selectFields = [ "name", "model", "vehicle_class", "crew", "passengers" ]
	, orderBy      = "crew desc, passengers desc, name"
);

Aliases and SQL functions #

If you wish, you can rename your columns using simple SQL aliases:

cfc
films = presideObjectService.selectData(
	  objectName   = "starwars_film"
	, selectFields = [ "title", "episode_id as episode", "director" ]
);

And you can insert SQL logic into your field definition:

cfc
films = presideObjectService.selectData(
	  objectName   = "starwars_film"
	, selectFields = [
		  "episode_id"
		, "concat( 'Episode ', episode_id, ': ', title ) as full_title" ]
	, orderBy      = "episode_id"
);

Note: While Preside's default database is MySQL/MariaDB, there is also beta support for MSSQL and PostgreSQL. When you're writing your own applications -- where you know what database you will be using -- you can use SQL syntax specific to that database. But if you plan on releasing, for example, a Preside extension, you should ensure either that you are using standard SQL syntax, or you provide logic that will provide alternate syntax depending on the DBMS in use.

Paging your results #

You will often want to limit the number of rows returned, and if you're creating a paged interface, you'll also want to specify which row to start from. This is achieved using maxRows and startRow respectively:

cfc
pagedFilms = presideObjectService.selectData(
	  objectName   = "starwars_film"
	, selectFields = [ "title", "episode_id", "director" ]
	, orderBy      = "episode_id"
	, maxRows      = 3
	, startRow     = 4
);

New in Preside 10.8.0 #

There are a few arguments for selectData() which have been introduced in the forthcoming 10.8.0 release of Preside.

distinct does exactly what you would expect: so the following query would return a distinct list of all the different hair colours of Star Wars characters, if that's the sort of thing you're interested in...

cfc
hairColours = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, selectFields = [ "hair_color" ]
	, distinct     = true
);

If all you want from your query is to get a count of matching records, use recordCountOnly:

cfc
filmCount = presideObjectService.selectData(
	  objectName      = "starwars_film"
	, recordCountOnly = true
);

Finally, there are occasions when you may want simply to return the SQL generated by the method, rather than actually executing it. This might be useful for unit tests, or -- as we will discover in a later part of this series -- when constructing complex joins.

cfc
result = presideObjectService.selectData(
	  objectName          = "starwars_character"
	, getSqlAndParamsOnly = true
);

This will return a struct with two items: sql, which contains the SQL statement, and params, an array of all the SQL parameters for that query (more of this when we get to filters in a later post).

Summary #

In this post we've looked at the basics of retrieving data in Preside with selectData() with some very trivial examples.

Real-world applications are, of course, more complicated, and in future parts I'll be talking about filtering records, joins, subqueries and aggregate functions.