Playlistr: Albums & Artists

If we're going to create a relationship between our Albums and our Artists we'll need to alter our database schema. As we've seen before, commandbox-migrations with qb allows us to create tables and columns in our database. Amazingly it also allows us to alter an existing schema. So let's create a new script with migrate create albums-artists and modify it to add our relationships.

component {

	function up( schema ) {
		schema.alter( "albums", function( table ) {
			table.addColumn(
				table.unsignedInteger( "artistID" ).nullable()
			).addConstraint(
				table.foreignKey( "artistID" )
					.references( "id" )
					.onTable( "artists" )
					.onDelete( "cascade" )
			);
		} );
	}

	function down( schema ) {
		schema.alter( "albums", function( table ) {
			table.dropForeignKey( table.foreignKey( "artistID" ) )
				.dropColumn( "artistID" );
		} );
	}

}

Now when we call migrate up from our terminal our albums table is altered with a foreign key column called artistID on the artists table. Also we can call migrate down to drop the foreign key and column.

Beware: Calling migrate down will run all your down() methods and will drop you back to a database empty of all records and tables. If you just wanted to revert the last change you add the switch --once like so: migrate down --once and the same can be used if you want to step up through your migration with migrate up --once

We need to make changes to both the Album and Artist side of the model, controllers and views as well as our AlbumService.

Album (Model)

Here we need to tell the application that the Album is a child of an Artist and therefore it belongs to an Artist.

component extends="quick.models.BaseEntity" {
	property name;
	property artistID sqltype="cf_sql_integer";

	function artist() {
		return belongsTo( "Artist" );
	}
}

We've added an artistID property with an sqltype of cf_sql_integer. This allows Quick to insert an Album row where there is, as yet, no Artist associated and therefore the value would be NULL.

The artist() method allows us to get the Artist associated with the Album by using getArtist() on the retrieved Album. The belongsTo() is a Quick method that tells the entity which entity to relate to.

Artist (Model)

With our Artist we need to be able to get all the Album entities associated, or children, of our Artist.

component extends="quick.models.BaseEntity" {
	property name;

	function albums() {
		return hasMany( "Album" );
	}
}

There is no extra property to add but there is a new method called albums(). This provides a getAlbums() method to our Artist entity. The method hasMany() returns a Quick collection of Album entities.

Album (Controller)

We need to touch a few of the action methods to add support for the Artists associated with our Album.

Action: create()

function create( event, rc, prc ) {
	param rc.id = "";

	prc.name = "";

	prc.artistID = "";

	prc.artists = getInstance( "Artist" )
		.orderby( "id" )
		.get();

	prc.action = "Create";
	prc.formAction = "albums.createAction";

	event.setView( "albums/createUpdate" );
}

Here we've added a rc.artistID to initialise the value and an array of all the Artists for use in the view.

Action: createAction()

function createAction( event, rc, prc ) {
	param rc.name = "";
	param rc.artistID = "";

	AlbumService.create( {
		name: rc.name,
		artistID: rc.artistID
	} );

	relocate( "albums" );
}

All that has been changed here is adding a default value for rc.artistID and passing the submitted value onto the create() method in our AlbumService.

Action: update()

function update( event, rc, prc ) {
	param rc.id = "";

	var album = AlbumService.getByID( rc.id );

	prc.name = album.getName();

	prc.artistID = album.getArtist().getID();

	prc.artists = getInstance( "Artist" )
		.orderby( "id" )
		.get();

	prc.action = "Update";
	prc.formAction = "albums.updateAction";

	event.setView( "albums/createUpdate" );
}

For our Album view we need to know which, if any, Artist is associated with our Album. The line rc.artistID = album.getArtist().getID(); get the Artist ID value for us to use in the view. Once again we also need to have an array of all the Artists.

Action: updateAction()

function updateAction( event, rc, prc ) {
	param rc.id = "";
	param rc.name = "";
	param rc.artistID = "";

	AlbumService.update( rc.id, {
		name: rc.name,
		artistID: rc.artistID
	} );

	relocate( "albums" );
}

Finally we need to ensure there is an rc.artistID and that it's passed onto our AlbumService method update().

Artist (Controller)

Once again, just a few changes to support the views.

Action: create()

function create( event, rc, prc ) {
	param rc.id = "";

	prc.name = "";
	prc.albums = [];

	prc.action = "Create";
	prc.formAction = "artists.createAction";

	event.setView( "artists/createUpdate" );
}

There's very little to change here. We just need to defined our prc.albums as an empty array for the view to know not display a table of Albums.

Action: update()

function update( event, rc, prc ) {
	param rc.id = "";

	var artist = getInstance( "Artist" )
		.findOrFail( rc.id );

	prc.name = artist.getName();

	prc.albums = artist.getAlbums();

	prc.action = "Update";
	prc.formAction = "artists.updateAction";

	event.setView( "artists/createUpdate" );
}

There's very little to change here. Just to get all the Albums for use to display when updating an Artist.

Next we need to make use of all this new data from the relationships we've established by using the views to reference the Album and Artist entities.

Album (createUpdate.cfm)

<cfif prc.artists.len() != 0>
  <div class="form-group">
    <label for="exampleFormControlSelect1">Artist</label>

    <select id="artistID" name="artistID" class="form-control">
      <option value="">Select an artist</option>

      <cfloop array="#prc.artists#" item="artist">
        <option value="#artist.getID()#" #( artist.getID() == prc.artistID ? "selected" : "" )#>#artist.getName()#</option>
      </cfloop>
    </select>
  </div>
</cfif>

If we have some Artist entities, we want to display a dropdown list of them to be able to choose from. Also, when updating an Album where an Artist has previously been associated with the Album, we want that Artist to be pre-selected.

Artist (createUpdate.cfm)

<cfif prc.albums.len() != 0>
  <table class="table table-striped table-bordered">
    <thead class="thead-dark">
      <tr>
        <th scope="col">Albums</th>
      </tr>
    </thead>

    <tbody>
      <cfloop array="#prc.albums#" item="local.album">
        <tr>
          <td><a href="#event.buildLink( to='albums.update', queryString='id=#local.album.getID()#' )# ">#local.album.getName()#</a></td>
        </tr>
      </cfloop>
    </tbody>
  </table>
</cfif>

Again, if we have some Albums associated with our Artist we'll want to list then and also provide a link to allow us to jump to that Album.