From a30e57e025b085c93ee10e159be2cc463656b7f3 Mon Sep 17 00:00:00 2001
From: "Donald A. Lobo" <lobo@civicrm.org>
Date: Tue, 30 Apr 2013 11:00:32 -0700
Subject: [PATCH] get rid of left joins

---
 CRM/Contact/BAO/Query.php | 39 ++++++++++++++++-----------------------
 CRM/Core/OptionValue.php  | 15 +++++++++------
 2 files changed, 25 insertions(+), 29 deletions(-)

diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php
index 9e1f39650e..c930717567 100644
--- a/CRM/Contact/BAO/Query.php
+++ b/CRM/Contact/BAO/Query.php
@@ -30,7 +30,7 @@
  * @package CRM
  * @copyright CiviCRM LLC (c) 2004-2013
  * $Id$
- *
+nnnnnnnnn *
  */
 
 /**
@@ -142,6 +142,7 @@ class CRM_Contact_BAO_Query {
    *
    */
   public $_permissionWhereClause;
+
   /**
    * the from string
    *
@@ -619,8 +620,7 @@ class CRM_Contact_BAO_Query {
             }
 
             if (
-              $tableName == 'gender' || $tableName == 'individual_prefix' ||
-              $tableName == 'individual_suffix' || $tableName == 'im_provider' ||
+              $tableName == 'im_provider' ||
               $tableName == 'email_greeting' || $tableName == 'postal_greeting' ||
               $tableName == 'addressee'
             ) {
@@ -1727,7 +1727,9 @@ class CRM_Contact_BAO_Query {
         $name, $op, $value, $grouping,
         CRM_Core_PseudoConstant::{$field['pseudoconstant']['name']}(),
         $field,
-        $field['title']
+        $field['title'],
+        'String',
+        TRUE
       );
       if ($name == 'gender') {
         self::$_openedPanes[ts('Demographics')] = TRUE;
@@ -2287,21 +2289,6 @@ class CRM_Contact_BAO_Query {
                                   AND civicrm_group_contact.group_id =  civicrm_subscription_history.group_id";
           continue;
 
-        case 'individual_prefix':
-          $from .= " $side JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix')";
-          $from .= " $side JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id ) ";
-          continue;
-
-        case 'individual_suffix':
-          $from .= " $side JOIN civicrm_option_group option_group_suffix ON (option_group_suffix.name = 'individual_suffix')";
-          $from .= " $side JOIN civicrm_option_value individual_suffix ON (contact_a.suffix_id = individual_suffix.value AND option_group_suffix.id = individual_suffix.option_group_id ) ";
-          continue;
-
-        case 'gender':
-          $from .= " $side JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender')";
-          $from .= " $side JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id) ";
-          continue;
-
         case 'civicrm_relationship':
           if (self::$_relType == 'reciprocal') {
             $from .= " $side JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id)";
@@ -4856,11 +4843,12 @@ AND   displayRelType.is_active = 1
     $selectValues,
     $field,
     $label,
-    $dataType = 'String'
+    $dataType = 'String',
+    $useIDsOnly = FALSE
   ) {
     $qill = $value;
     if (is_numeric($value)) {
-      $qill = $value = $selectValues[(int ) $value];
+      $qill = $selectValues[(int ) $value];
     }
     elseif ($op == 'IN' || $op == 'NOT IN') {
       $values = self::parseSearchBuilderString($value);
@@ -4873,12 +4861,17 @@ AND   displayRelType.is_active = 1
         $qill = implode(', ', $value);
       }
     }
-    $wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
+    else {
+      // its a string, lets get the int value
+      $value = array_search($value, $selectValues);
+    }
+    $wc = self::caseImportant($op) && ! $useIDsOnly ? "LOWER({$field['where']})" : "{$field['where']}";
     $this->_where[$grouping][] = self::buildClause($wc, $op, $value, $dataType);
     $this->_qill[$grouping][] = $label . " $op '$qill'";
   }
 
-  /** function to check and explode a user defined numeric string into an array
+  /**
+   * function to check and explode a user defined numeric string into an array
    * this was the protocol used by search builder in the old old days before we had
    * super nice js widgets to do the hard work
    *
diff --git a/CRM/Core/OptionValue.php b/CRM/Core/OptionValue.php
index e326171503..a665413d12 100644
--- a/CRM/Core/OptionValue.php
+++ b/CRM/Core/OptionValue.php
@@ -326,23 +326,26 @@ class CRM_Core_OptionValue {
               'title' => ts('Gender'),
               'headerPattern' => '/^gender$/i',
               'pseudoconstant' => $contactFields['gender_id']['pseudoconstant'],
-            ),
+              'where' => 'contact_a.gender_id',
+             ),
             'individual_prefix' => array(
               'name' => 'individual_prefix',
               'title' => ts('Individual Prefix'),
               'headerPattern' => '/^(prefix|title)/i',
               'pseudoconstant' => $contactFields['prefix_id']['pseudoconstant'],
+              'where' => 'contact_a.prefix_id',
             ),
             'individual_suffix' => array(
               'name' => 'individual_suffix',
               'title' => ts('Individual Suffix'),
               'headerPattern' => '/^suffix$/i',
               'pseudoconstant' => $contactFields['suffix_id']['pseudoconstant'],
-            ),
-          );
-          $nameTitle = array_merge($nameTitle, $title);
-        }
-      }
+              'where' => 'contact_a.suffix_id',
+             ),
+           );
+           $nameTitle = array_merge($nameTitle, $title);
+         }
+       }
 
       if (is_array($nameTitle)) {
         foreach ($nameTitle as $name => $attribs) {
-- 
2.25.1