Home > Articles

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account