In the first two parts of this series, I’ve looked at selecting and filtering data from Preside objects using selectData().

In this third and final instalment, we’ll be accessing the power of related objects. Simple, standalone data objects are all very well, but even a very basic application will define connections between objects. These will be in the form of many-to-many and many-to-one relationships (also one-to-many, which is a many-to-one relationship looked at from the other direction).

If we look at the starwars_character.cfc object, we can see both types of relationship:

component {
	// ... other basic proprties defined ...
	property name="homeworld"  relationship="many-to-one"  relatedTo="starwars_planet";
	property name="films"      relationship="many-to-many" relatedTo="starwars_film"     relatedVia="starwars_film_character";
	property name="species"    relationship="many-to-many" relatedTo="starwars_species"  relatedVia="starwars_character_species";
	property name="starships"  relationship="many-to-many" relatedTo="starwars_starship" relatedVia="starwars_starship_pilot";
	property name="vehicles"   relationship="many-to-many" relatedTo="starwars_vehicle"  relatedVia="starwars_vehicle_pilot";
}

We can see from this that a character has a homeworld property, by which it can be related to one single planet; but then can be related to multiple films, vehicles, etc.

Basic joins

The key to selectData() joins is to use a dotted syntax in your column definition, very much like you would in SQL. Bear in mind though that you are using property names rather than table names, e.g. property_name_of_related_object.property_on_related_object:

characters = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, selectFields = [
		  "name"
		, "homeworld.name as homeworld_name"
	  ]
);

Here we are selecting each character along with the name of their homeworld. Note that homeworld.name is actually retrieving data from the starwars_planet object – but the property name is homeworld.

Behind the scenes, Preside is converting this to the following SQL (but you usually won’t need to worry about this):

select    `starwars_character`.`name`, `homeworld`.`name` as homeworld_name
from      `pobj_starwars_character` `starwars_character`
left join `pobj_starwars_planet` `homeworld` on (`homeworld`.`id` = `starwars_character`.`homeworld`) 

Extended joins

This gives you data from an object that is directly related to your main object, but Preside allows you to traverse joins as deep as you like. For instance, you might want to get values from an object related to an object related to your main object. To do this, you simple separate the objects in your chain with a $.

In this example, we are getting a list of characters, their homeworld, and the titles of films in which that homeworld has appeared:

characters = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, selectFields = [
		  "name"
		, "homeworld.name as homeworld"
		, "homeworld$films.title as homeworld_film"
	  ]
	, orderBy      = "name, homeworld.name, homeworld$films.episode_id"
);

You will see that the many-to-many relationships are stored in linking tables (whose names are defined here using the relatedVia attribute, but you can leave Preside to set default names for these tables). When selecting data using selectData() you can ignore these linking tables, as Preside does all the heavy lifting for you – it will create SQL joins between starwars_character and starwars_film_character, and then between starwars_film_character and starwars_film.

Or, one step further down the rabbit-hole, we can also add all the species that appeared in films in which that homeworld appeared:

characters = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, selectFields = [
		  "name"
		, "homeworld.name as homeworld"
		, "homeworld$films.title as homeworld_film"
		, "homeworld$films$species.name as homeworld_film_species"
	  ]
	, orderBy      = "name, homeworld.name, homeworld$films.episode_id, homeworld$films$species.name"
);

Note that we use the same syntax for relationships when defining orderBy parameters (and also groupBy, filter, etc.).

Because we are returning a query with joins, there will be many rows of data for each character, containing all the combinations of homeworlds, films and species that match the request. You might want to use the group attribute on your cfloop or cfoutput for the query in order to group the data meaningfully.

Join types

By default, most joins that Preside performs will be left outer join (joins to required properties will, however, use an inner join). So the following query will return all characters and the starships they are associated with, or a blank/null value for the starship if they are not associated with any:

characters = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, selectFields = [ "name", "starships.name as starship" ]
)

However, it may be that you only wish to return those characters who actually have starships assigned to them. In SQL this is done using an inner join; you can instruct Preside to use inner joins for all joins in a query by using the forceJoins argument:

characters = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, selectFields = [ "name", "starships.name as starship" ]
	, forceJoins   = "inner"
)

Valid values for this are left (the default) and inner.

Extra joins and subqueries

Of course, sometimes the data you want to extract from the database will not be accessible via the object’s defined relationships. Maybe you need a subquery that does some calculation for you, or maybe you need to join a table to itself.

This is achieved using the extraJoins argument. This is an array of explicitly defined subqueries, along with all the information needed to join it to your main query:

characters = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, selectFields = [ "name", "homeworld.name as homeworld_name", "homeworld_count.character_count" ]
	, orderBy      = "homeworld_count.character_count desc, name"
	, filter       = "homeworld_count.homeworld = starwars_character.homeworld"
	, extraJoins   = [ {
		  subQuery       = "select count( id ) as character_count, homeworld from pobj_starwars_character group by homeworld"
		, subQueryAlias  = "homeworld_count"
		, subQueryColumn = "homeworld"
		, joinToTable    = "starwars_character"
		, joinToColumn   = "homeworld"
		, type           = "inner"
	  } ]
);

This query will return a list of characters along with the total count of characters from the same homeworld. All 6 of the parameters for the extraJoin are required:

subQuery is the SQL of the subquery. Note that table names in here are the actual database table names, not the name of the Preside object. subQueryAlias gives the subquery a name, and subQueryColumn is the column in this query that will be used to join to a column on the main query (defined by joinToTable (Preside object name) and joinToColumn). Finally, the type parameter defines the type of join (inner or left) that will be used to join to the subquery.

To clarify how all this fits together, here is the generated SQL from this request:

select `starwars_character`.`name`, homeworld.name as homeworld_name, homeworld_count.character_count
from   `pobj_starwars_character` `starwars_character`
left join `pobj_starwars_planet` `homeworld`
	on (`homeworld`.`id` = `starwars_character`.`homeworld`)
inner join (
	select count( id ) as character_count, homeworld
	from pobj_starwars_character
	group by homeworld
) `homeworld_count`
	on (`homeworld_count`.`homeworld` = `starwars_character`.`homeworld`)
where homeworld_count.homeworld = starwars_character.homeworld
order by homeworld_count.character_count desc, `starwars_character`.`name`

Remember from part one how you can use the getSqlAndParamsOnly argument to return a query and its parameters? This can come in very handy when you’re building complex joins with subqueries…

There is one more (optional) parameter that an extraJoin can take, and that is additionalClauses. This is a SQL string which will be added to the on clause which joins the subquery. The following (rather contrived) example will return a list of characters, their homeworld, and the number of characters from that homeworld who share the same hair colour.

result = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, selectFields = [ "name", "homeworld.name as homeworld_name", "count( homeworld_subquery.hair_color ) as same_hair_color_count" ]
	, orderBy      = "name"
	, groupBy      = "starwars_character.id, homeworld_subquery.hair_color"
	, filter       = "homeworld_subquery.homeworld = starwars_character.homeworld"
	, extraJoins   = [ {
		  subQuery          = "select id, homeworld, hair_color from pobj_starwars_character"
		, subQueryAlias     = "homeworld_subquery"
		, subQueryColumn    = "homeworld"
		, joinToTable       = "starwars_character"
		, joinToColumn      = "homeworld"
		, type              = "inner"
		, additionalClauses = "homeworld_subquery.hair_color = starwars_character.hair_color"
	  } ]
);

Both the subQuery and additionalClauses values may include dynamically defined filter parameters, using the same :parameter_name syntax as elsewhere. These dynamic values would be defined in the filterParams argument of the main query, and may be shared by the main query and its subqueries.

Aggregate functions

In the previous example, you’ll notice that we’ve used a count() aggregate function in our selectFields. This shows how easy it is to include aggregate data functions (this combines here with the groupBy clause). Here are a few more examples of simple aggregation of data:

longestVehicle = presideObjectService.selectData(
	  objectName   = "starwars_vehicle"
	, selectFields = [ "max( length ) as max_length" ]
);

vehiclesClasses = presideObjectService.selectData(
	  objectName   = "starwars_vehicle"
	, selectFields = [ "vehicle_class", "count( vehicle_class ) as vehicle_count" ]
	, groupBy      = "vehicle_class"
	, orderBy      = "vehicle_count desc, vehicle_class"
);

There are of course occasions when a subquery using an extraJoin will be the better solution, but most basic aggregation can be achieved just by including here – especially as of Preside 10.8.0, which adds to selectData() the having argument:

vehiclesClasses = presideObjectService.selectData(
	  objectName   = "starwars_vehicle"
	, selectFields = [ "vehicle_class", "count( vehicle_class ) as vehicle_count" ]
	, groupBy      = "vehicle_class"
	, having       = "count( vehicle_class ) >= 4"
	, orderBy      = "vehicle_count desc, vehicle_class"
);

This will return a list of vehicle classes with counts of vehicles, for classes with 4 or more vehicles. having functions exactly as it does in raw SQL, basically acting as a where clause which acts on the aggregate function.

Finally, you can use your DBMS’s proprietary aggregate functions, just like any other function. A particularly useful one is MySQL’s group_concat(), which you can use to generate lists of grouped data directly in the database; it is handy for getting a list of IDs of related records in order to pre-populate object pickers, etc. Note that it is MySQL only: PostgreSQL has the similar string_agg() function; but MS SQL Server doesn’t let you do this without some ugly fiddling about with xml path. So if you’re looking to create a database-agnostic application, you’ll probably be better sticking with standard SQL and grouping the data in your CFML code.

characters = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, selectFields = [ "id", "name", "group_concat( films.title separator ', ' ) as films_appeared_in" ]
	, groupBy      = "id"
	, orderBy      = "name"
);

Conclusion

I hope I’ve demonstrated in these posts the power of getting data from Preside’s object model. I’ve learned a lot from putting this together, especially about complex joins and the trickier filters.

If you have any questions or suggestions, there is usually someone who can help on the Preside Slack channel, which is the best place to get any Preside questions answered…