Joins

The Self Referencing Table

Databases are designed in different ways when it comes to Joins. Joins become necessary when you want to extract data from more than one table in the database. However, here is a special case scenario where we still require a Join, but on only one table. How does that work? Follow along and we will explain.

This tutorial will focus on a design scenario based on the third Self Referencing Table Example. In this example, we are dealing with only one table: users. So what makes this one so special? This design pattern doesn't come up very often but it does appear from time to time. If you were to view this table in a database designer application, it would look like what you see in IMAGE 1.

IMAGE 1
Join diagram of table: users

What you have here is a table with a Primary Key id and a field named manager. Both are of type INT. If you wanted to show who is the manager for every individual record in the table, you would place the id value of a manager into the manager field. For instance,

id
first_name
last_name
manager
ismanager
1TerryChase30
2ChrisMonroe30
3ShirleyBassett1
4DebbieMacDonald30
records 1,2, and 4 are individuals whose manager is Shirley Bassett. Her id is 3. Since she is the manager, she shows no one as being a manager over her, and the ismanager field flags her as being a manager with the number 1. All the other records have the value 3 under the manager field to indicate that Shirley is their manager.

The following snippet of code is how we program our Java Server Page. As you can see, it is heavily commented since it is one of the more challenging join scenarios. If you read the comments in the JSP page, you can follow the authors thought process and things will become more apparent.

The table users contains a field we don't want to display on the client side. It is named "manager". It contains a numeric value and refers to the id(s) of individuals who are managers. We will set its value when we perform an Insert or Update transaction, but we will change its value for the JSON string on output. We will have the Join produce our output providing us with the first and last name of every manager for every individual record.

We require only one Join to make this work. When we create an instance of the Join, you will notice that the second and third parameters of the constructor receive the same value:

Join join1 = new Join(editor,tableNames[0], tableNames[0])

The second parameter is the Parent Table name. The third parameter is supposed to be the Child Table name. In this case, however, they both refer to the same table. We regard the Join as though it were referencing a lookup table, therefore, we set its canWrite property to false. The same is true of the fields. This is probably one of the only times when you will need to create an alias name for the table that the Join references. We will call a special method on the Join and set an alias name for table users. We will name it simply: "manager". The method call is: join1.setTableAlias("users", "manager").

Note that when we create instances of each Field within the Join, we provide for its first parameter of the constructor the alias name for table users. We set it to "manager". This is important as you will see later.

The next important aspect of join1 is setting the join using method setJoin(...). It takes two parameters. In this case, we pass the value "manager" as its first parameter and the value "id" as its second paramter. The pattern of the join condition, when we are dealing with an alias name for the table, is somewhat different. We wind up with the statement:

    SELECT users.id, manager.first_name,manager.last_name FROM users JOIN users AS manager ON users.manager=manager.id ORDER BY users.id;

It might require a bit of thought to understand what is happening here, but what you need to keep in mind is we are attempting to give the table users an alias, being manager, and treating it as though we were working with two separate tables. Those tables just happen to have the exact same field names within them. The SELECT statement refers to the first and last name, but under the table alias name manager. Things come together when we view the join condition: users.manager=manager.id. users is the Parent table and its manager field is made to equal the Child table "manager" (the alias table name for users) and its field id. Now we can set our Join: join1.setJoin("manager","id").

Lastly, there is one extra step we need to make in our programming. We know that the Join and its Fields are READ ONLY. On an Insert or Update transaction, however, we still need to set the values on the fields so that we can get a proper output on the JSON string destined for the client side. We set a special method call on the instance of Editor:

editor.setJoinFieldsOnUpdateAndInsert(true, new Field("manager", pKey, Field.Type.INT), "manager")

This method requires a Field as its second parameter. What it represents will be used in an SQL query that involves a WHERE condition. The third parameter for the method call refers to the "manager" parameter that will be one of the parameters sent from the client side. So we are telling JED to look for a parameter named "manager", which contains a numeric value. Our lookup SQL statement would be:

    SELECT manager.first_name,manager.last_name FROM users AS manager WHERE manager.id = 5;

When JED finds a parameter sent from the client side named "manager", its value is 5. That value is what completes the condition on the SQL statement: WHERE `manager`.`id` = 5. Once the lookup on the database is completed, JED can then set the first name and last name on the fields inside the join.

On an update transaction, the JSON string returned to the client side would look similar to this:

{"row":{"DT_RowId":"row_16","first_name":"Bobby","last_name":"Pearson","manager":{"first_name":"Ken","last_name":"Allain"}},"id":"row_16","data":[],"fieldErrors":[],"error":""}

Note the "manager" object. It contains the first and last name of the manager. DataTables, in our example, concatonates those names so they appear as one string under the Manager field displayed to the user.

A Create or Edit form on the client side looks like IMAGE 2:

IMAGE 2
DataTables edit form with drop-down select box

We have a select drop down box that contains a list of all the managers. Here we make use of class Editor's method IncludeOptionsForOutput(java.lang.String key, java.util.ArrayList<java.util.LinkedHashMap<java.lang.String,java.lang.Object>> opts) to capture the default options for all the managers. The output on the JSON string will be as follows:

"users.manager":[{"value":"4","label":"4 Louise Adongo"},{"value":"5","label":"5 Sherri Aikenhead"},{"value":"9","label":"9 Ken Allain"}]

This concludes the Joins tutorial.