Relational Data and Data Retrieval

Working with Relational Data

In Aerial, we also have the ability to save and update related data. In the sample database we have two tables: User and Comment. We have set up a one-to-many relation between Comment and User so that the User can possess many Comment records, and each comment is related to its parent User record via the foreign key userId.

If we inspect the UserVO.as file (found under src_flex/org/aerial/vo), we will see that we have a property called comments:

private var _comments:*

public function get comments():IList
{
	return _comments;
}

public function set comments(value:IList):void
{
	_comments = value;
}

...and in the CommentVO.as file, we have two properties, namely User and userId:

private var _User:*
private var _userId:*

public function get User():UserVO
{
	return _User;
}

public function set User(value:UserVO):void
{
	_User = value;
}

public function get userId():Number
{
	return _userId;
}

public function set userId(value:Number):void
{
	_userId = value;
}

These properties allow us to create relational data in ActionScript and insert/update the information accordingly in the database. Take a look at the example below:

// create a new User model
var user:UserVO = new UserVO();
user.firstName = "Random";
user.lastName = "Guy";

// add a comment
var comment1:CommentVO = new CommentVO();
comment1.title = "I like it!";
comment1.message = "So, like, do I type here or what?";

// ...and another
var comment2:CommentVO = new CommentVO();
comment2.title = "Oh, I see!";
comment2.message = "Here's my message :)";

// create an ArrayList of all the comments
var commentsList:ArrayList = new ArrayList();
commentsList.addItem(comment1);
commentsList.addItem(comment2);

// set the "comments" property on the User to relate the Comment instances to it
user.comments = commentsList;

userService.save(user)						// - save the user into the "User" table, and the related comments into the "Comment" table
	.callback(resultHandler, faultHandler)			// - add "result" and "fault" handlers
	.execute();						// - execute the request

Simple enough, right? All we need to do is represent the objects in ActionScript as we'd like them to be in the database, and we save them. Notice in the example that we used the save function, not insert - the save function operates just like insert, except that if your VO doesn't have an id set, it will insert it into the database, whereas insert will create a new record regardless of whether the VO has an id or not.

Here is our result:

> SELECT * FROM User;
+----+-----------+----------+
| id | firstName | lastName |
+----+-----------+----------+
|  2 | Random    | Guy      |
+----+-----------+----------+

> SELECT * FROM Comment WHERE userId = 2;
+----+------------+-----------------------------------+--------+
| id | title      | message                           | userId |
+----+------------+-----------------------------------+--------+
|  1 | I like it! | So, like, do I type here or what? |      2 |
|  2 | Oh, I see! | Here's my message :)              |      2 |
+----+------------+-----------------------------------+--------+

Data Retrieval

In Aerial, we have a really fancy function called find(). This function can do a whole bunch of neat things, as we'll see.

At this point in our application, we want to find all existing User records and all of their related Comment records. In Aerial, this is very easy!

userService.find()					// - call the "find" function
	.addRelation("User(*).comments(*)")		// - fetch all the "User" properties and their related "Comment" records
	.callback(resultHandler, faultHandler)		// - add "result" and "fault" handlers
	.execute();					// - execute the request

The addRelation function allows us to define what data we want back, which fields to return and which relations to return as well. Let's simplify the process somewhat and build up to a more complex query.

NOTE: The identifiers you use in your query (for example User, comments and Comment) must correlate with the model names and relation aliases in the relevant models. To find these values, simple open the ActionScript model (VO) and look at the identifiers.

Return all User records and their properties only

(an asterisk * means "all")

...
.addRelation("User(*)")
...

Return all Comment records and their properties only

(an asterisk * means "all")

...
.addRelation("Comment(*)")
...

Return all User records, excluding the lastName property

(to exclude a property, use the - modifier)

...
.addRelation("User(*, -lastName)")
...

Return all User records with all their properties and only the title property of each related Comment record

(to include a property, use the + modifier)

...
.addRelation("User(*).comments(+title)")
...

Return a specific User record

(you can use conditions inside each property such as =, !=, >=, <=, >, <, &, IS, NOT, BETWEEN, LIKE and IN)

...
.addRelation("User(*, id=1)")
...

Return all User records except a specific User record

(you can use the standard != to indicate NOT EQUALS)

...
.addRelation("User(*, id != 1)")
...

Return all User records, their related Comment records and the related User record of each Comment

(you can use multiple addRelation calls)

...
.addRelation("User(*).comments(*)")
.addRelation("User(*).comments(*).User(*)")
...

Here is a screenshot of the format of the data when it comes back from the last example:

Next Step: Advanced Querying