- A Little Java Is Recommended
- Better Get a Bucket
- Building a Better Mousetrap
- Nearing the Solution of Zeno's Paradox
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Building a Better Mousetrap
All right, our journey must continue. Let's first take a look at buildQueryLevel_0(), called by setBucketResults( 0 ). This is a long function, but it does something very basic—building the query to retrieve the InformIT Recommends bucket, based on your user settings. Let's take it apart piece by piece.
1 private void buildQueryLevel_0 ()
2 {
3
4 StringBuffer select_list = null;
5 StringBuffer from_tables = null;
6 StringBuffer where_conditions = null;
7 StringBuffer order_by = null;
8 String product_group_condition = null;
9
10 sql_query_string.clear();
We first initialize some public variables.
11 select_list = new StringBuffer 12 ( "products.product_id" ); 13 from_tables = new StringBuffer( "products" ); 14 where_conditions = new StringBuffer 15 ( "products.active_flag=1" ); 16 17 order_by = new StringBuffer 18 ( "products.published_date DESC" );
In lines 11–18, we set up the initial select, from, where, and order by clauses in string variables. We'll be selecting the product_id from the products table where the active_flag is 1, and sorting in descending order by the published_date.
19 select_list.append(",products.product_name" );
20 select_list.append(",products.second_product_name" );
21 select_list.append(",product_types.product_type" );
22 select_list.append(",products.published_date" );
23 select_list.append(",products.status" );
24 select_list.append(",product_types.product_group_id" );
25 select_list.append(",product_groups.product_group_name" );
On Lines 19–25 we refurbish the select_list by appending several more relevant tables. You'll notice that we're selecting from the product_types and product_groups tables as well as from products.
26 from_tables.append( ",product_types,product_groups" );
These we append to the from_tables string above.
27 where_conditions.append( " AND 28 products.product_type_id = 29 product_types.product_type_id" ); 30 where_conditions.append( " AND 31 product_types.product_group_id = 32 product_groups.product_group_id" ); 33 where_conditions.append( " AND NOT 34 products.product_name=''" ); 35 where_conditions.append( " AND NOT 36 products.product_name IS NULL" ); 37 where_conditions.append( " AND NOT 38 products.short_description = ''" ); 39 where_conditions.append( " AND NOT 40 products.short_description IS NULL" ); 41 where_conditions.append( " AND 42 ( products.status='INSTOCK' OR 43 products.status='OUT OF STOCK')" );
We append to the where_conditions to create a join on the product_types table by matching the product_type_id. We do the same for the product_groups_table. We also ensure that the product_name and short_description are not empty, and that the book is either in stock or temporarily out of stock. The stocking status lets us know whether or not the book can be sold.
44 if ( getProductGroupID() != null ) {
45 product_group_condition =
46 "product_groups.product_group_id='" +
47 getProductGroupID() + "'";
48 where_conditions.append(" AND " +
49 product_group_condition);
50 }
51 else
52 {
53 if ( getProductGroup() != null )
54 {
55 product_group_condition =
56 "product_groups.product_group_name='" +
57 getProductGroup() + "'";
58 where_conditions.append(" AND " +
59 product_group_condition);
60 }
61 else
62 {
63 // product_group_id and product_group both
64 // not present, we cannot continue
65 throw new IllegalArgumentException
66 ( "Insufficient parameters to
form query string." );
67
68 }
69 }
Lines 44–69 perform a check to see whether we're using the product_group_id or the product_group_name to collect our products. If neither is present, we throw an exception that's caught by setBucketResults(). If you'll recall from a previous article, the product_group specifies the class of product for which we're searching. In this case, our product_group is set to "Books", so we add a clause to match that condition.
70 if ( user_interests_exist )
71 {
72 if ( from_tables.toString().indexOf
73 ("product_tax_mapping a") < 0 )
74 {
75 from_tables.append( ",product_tax_mapping a" );
76 }
77
78 if ( from_tables.toString().indexOf
79 ("reference_dimension c") < 0 )
80 {
81 from_tables.append( ",reference_dimension c" );
82 where_conditions.append( " AND c.reference_id =
83 a.reference_id" );
84 }
85
86 from_tables.append( ",user_interests" );
87
88 where_conditions.append( " AND
89 user_interests.user_id='" +
90 super.getUserID() + "'" );
91 where_conditions.append( " AND
92 user_interests.category=c.category" );
93 where_conditions.append( " AND
94 user_interests.subcategory=c.subcategory" );
95
96
97 }
Above, we check whether one of the flags set in verifyClauses(), user_interests_exist, is set to true. If so, we add three more tables into the mix. The first two, product_tax_mapping and reference_dimension, should be familiar from our past wacky adventures. (Is that a strange barking I hear?) They tie the taxonomy into our query. The third, user_interests, is the auxiliary table that ties some of the user's profile choices into the users table.
I'll save a little time here and skip over the checks that perform the same function for the user_certifications_exist and user_job_titles_exist flags. They work the same as the user_interests check, through which we just strolled.
98 if ( from_tables.toString().indexOf
99 ("product_tax_mapping a") >= 0 )
100 {
101 where_conditions.append( " AND
102 products.product_id = a.product_id" );
103 }
104
105 sql_query_string.setSelectClause
106 ( " SELECT DISTINCT " + select_list.toString() );
107 sql_query_string.setFromClause
108 ( " FROM " + from_tables.toString() );
109 sql_query_string.setWhereClause
110 ( " WHERE " + where_conditions.toString() );
111 sql_query_string.setOrderByClause
112 ( " ORDER BY " + order_by.toString() );
113
114 select_list = null;
115 from_tables = null;
116 where_conditions = null;
117 order_by = null;
118
119 }
What's left? We add in a last clause if we're using the product_tax_mapping table. Finally, we build the sql_query_string that will eventually be passed to the database to fetch the recommended books. After some more cleanup (I wonder what that developer's house looks like—spotless, I'm sure), we're done!
I'll elide a point-by-point exposition of the function buildQueryLevel_1(). It performs the same basic task as buildQueryLevel_0(), namely, building the SQL query string to retrieve the recommendations list. Because it deals with a situation in which all or just one of the flags is true, it builds the query slightly differently, but the end result is the same.