/**
  * Searching Many To Many on a custom field (dropdown)
  */
 public function testManyManyCustomFieldSearch()
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     $searchAttributeData = array();
     $searchAttributeData['clauses'] = array(1 => array('attributeName' => 'opportunities', 'relatedModelData' => array('attributeName' => 'stage', 'relatedAttributeName' => 'value', 'operatorType' => 'oneOf', 'value' => array(0 => 'something'))));
     $searchAttributeData['structure'] = '1';
     //Build the query 'where' and 'joins'. Confirm they are as expected
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Contact');
     $where = ModelDataProviderUtil::makeWhere('Contact', $searchAttributeData, $joinTablesAdapter);
     $compareWhere = "({$quote}customfield{$quote}.{$quote}value{$quote} IN('something'))";
     $this->assertEquals($compareWhere, $where);
     $this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount());
     $this->assertEquals(3, $joinTablesAdapter->getLeftTableJoinCount());
     $leftTables = $joinTablesAdapter->getLeftTablesAndAliases();
     $this->assertEquals('contact_opportunity', $leftTables[0]['tableName']);
     $this->assertEquals('opportunity', $leftTables[1]['tableName']);
     $this->assertEquals('customfield', $leftTables[2]['tableName']);
     $this->assertTrue($joinTablesAdapter->getSelectDistinct());
     //Now test that the subsetSQL query produced is correct.
     $subsetSql = Contact::makeSubsetOrCountSqlQuery('contact', $joinTablesAdapter, 1, 5, $where, null, false, $joinTablesAdapter->getSelectDistinct());
     $compareSubsetSql = "select distinct {$quote}contact{$quote}.{$quote}id{$quote} id ";
     $compareSubsetSql .= "from {$quote}contact{$quote} ";
     $compareSubsetSql .= "left join {$quote}contact_opportunity{$quote} on ";
     $compareSubsetSql .= "{$quote}contact_opportunity{$quote}.{$quote}contact_id{$quote} = {$quote}contact{$quote}.{$quote}id{$quote} ";
     $compareSubsetSql .= "left join {$quote}opportunity{$quote} on ";
     $compareSubsetSql .= "{$quote}opportunity{$quote}.{$quote}id{$quote} = {$quote}contact_opportunity{$quote}.{$quote}opportunity_id{$quote} ";
     $compareSubsetSql .= "left join {$quote}customfield{$quote} on ";
     $compareSubsetSql .= "{$quote}customfield{$quote}.{$quote}id{$quote} = {$quote}opportunity{$quote}.{$quote}stage_customfield_id{$quote} ";
     $compareSubsetSql .= "where " . $compareWhere . ' ';
     $compareSubsetSql .= 'limit 5 offset 1';
     $this->assertEquals($compareSubsetSql, $subsetSql);
     //Make sure the sql runs properly.
     $data = Contact::getSubset($joinTablesAdapter, 0, 5, $where, null, null, $joinTablesAdapter->getSelectDistinct());
 }
 public function testUsingOnTableAliasCorrectlyResolves()
 {
     Yii::app()->user->userModel = User::getByUsername('super');
     $searchAttributeData = array();
     $searchAttributeData['clauses'] = array(1 => array('attributeName' => 'iMember', 'operatorType' => 'equals', 'value' => 'somevalue1'));
     $searchAttributeData['structure'] = '1';
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('I');
     $quote = DatabaseCompatibilityUtil::getQuote();
     $where = ModelDataProviderUtil::makeWhere('I', $searchAttributeData, $joinTablesAdapter, 'analias');
     $compareWhere = "({$quote}analias{$quote}.{$quote}imember{$quote} = 'somevalue1')";
     $this->assertEquals($compareWhere, $where);
 }
 /**
  * Gets the date of all meetings as defined by the searchAttributeData
  */
 public function makeSqlQuery()
 {
     assert('get_class($this->model) == "Meeting"');
     $modelClassName = 'Meeting';
     $quote = DatabaseCompatibilityUtil::getQuote();
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
     $where = ModelDataProviderUtil::makeWhere($modelClassName, $this->searchAttributeData, $joinTablesAdapter);
     $selectDistinct = false;
     $modelClassName::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct);
     $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
     $selectQueryAdapter->addClause('meeting', 'startdatetime');
     $sql = SQLQueryUtil::makeQuery('meeting', $selectQueryAdapter, $joinTablesAdapter, null, null, $where);
     return $sql;
 }
 /**
  * @param $modelAttributeToDataProviderAdapter
  * @param null | string $onTableAliasName
  * @return string
  */
 protected function resolveFinalContent($modelAttributeToDataProviderAdapter, $onTableAliasName = null)
 {
     if ($modelAttributeToDataProviderAdapter instanceof ReadOptimizationDerivedAttributeToDataProviderAdapter) {
         $builder = new ReadOptimizationModelWhereAndJoinBuilder($modelAttributeToDataProviderAdapter, $this->joinTablesAdapter);
         $clausePosition = 1;
         $where = array();
         $builder->resolveJoinsAndBuildWhere(null, null, $clausePosition, $where, $onTableAliasName);
         return $where[1];
     } else {
         $modelClassName = $modelAttributeToDataProviderAdapter->getResolvedModelClassName();
         $metadataAdapter = new FilterForReportFormToDataProviderMetadataAdapter($this->componentForm);
         $attributeData = $metadataAdapter->getAdaptedMetadata();
         //todo: right now in makeWhere we always set setDistinct to true when instantiating new ModelWhereAndJoinBuilder
         //todo: but when we are calling makeWhere from here we should not set that to true. or should it? TBD
         return ModelDataProviderUtil::makeWhere($modelClassName, $attributeData, $this->joinTablesAdapter, $onTableAliasName);
     }
 }
 /**
  * Add where clause for searching for related models
  * @param $modelClassName
  * @param array $metadata
  * @param $joinTablesAdapter
  * @return string
  * @throws NotSupportedException
  */
 public static function makeWhere($modelClassName, array $metadata, &$joinTablesAdapter)
 {
     $data = GetUtil::getData();
     if (!isset($data['relationAttributeName']) || !$modelClassName::isRelation($data['relationAttributeName']) || intval($data['id']) <= 0) {
         throw new NotSupportedException();
     }
     $relationAttributeName = $data['relationAttributeName'];
     $additionalMetaData = static::getAdditionalSearchMetadata($relationAttributeName, $data);
     $clausesCount = 0;
     if (isset($metadata['clauses'])) {
         $clausesCount = count($metadata['clauses']);
         $metadata['clauses'][count($metadata['clauses']) + 1] = $additionalMetaData;
     } else {
         $metadata['clauses'][1] = $additionalMetaData;
     }
     if ($clausesCount == 0) {
         $metadata['structure'] = '(1)';
     } else {
         $count = $clausesCount + 1;
         $metadata['structure'] = $metadata['structure'] . ' and (' . $count . ')';
     }
     return ModelDataProviderUtil::makeWhere($modelClassName, $metadata, $joinTablesAdapter);
 }
 /**
  * This function is made public for unit testing. Calculates the total for each of the select statements
  * and adds them up.  Setting $selectDistinct to true when calling getCount since this is always true
  * when counting ids.
  * @return integer - total count across select statements.
  */
 public function calculateTotalItemCount()
 {
     $totalCount = 0;
     foreach ($this->modelClassNamesAndSearchAttributeData as $identifier => $modelClassNameAndSearchAttributeData) {
         foreach ($modelClassNameAndSearchAttributeData as $modelClassName => $searchAttributeData) {
             $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
             $where = ModelDataProviderUtil::makeWhere($modelClassName, $searchAttributeData, $joinTablesAdapter);
             $totalCount = $totalCount + RedBeanModel::getCount($joinTablesAdapter, $where, $modelClassName, true);
         }
     }
     return $totalCount;
 }
 /**
  * Not for use by applications. Public for unit tests only.
  * Override from RedBeanModelDataProvider to support multiple
  * where clauses for the same attribute and operatorTypes
  * @param metadata - array expected to have clauses and structure elements
  * @param $joinTablesAdapter
  * @see DataProviderMetadataAdapter
  * @return string
  */
 public static function makeWhere($modelClassName, array $metadata, &$joinTablesAdapter)
 {
     return ModelDataProviderUtil::makeWhere($modelClassName, $metadata, $joinTablesAdapter);
 }
 /**
  * HHH -> hasOneBelongsTo -> AAA -> hasOne -> BBB -> hasOne GGG (gggMember and gggMember2)
  * @depends testHasOneBelongsToHasOneToHasOne
  */
 public function testHasOneBelongsToHasOneToHasOneWithMultipleClauses()
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     $searchAttributeData = array();
     $searchAttributeData['clauses'] = array(1 => array('attributeName' => 'aaa', 'relatedModelData' => array('attributeName' => 'bbb', 'relatedModelData' => array('attributeName' => 'ggg', 'relatedModelData' => array('attributeName' => 'gggMember', 'operatorType' => 'equals', 'value' => 'somevalue')))), 2 => array('attributeName' => 'aaa', 'relatedModelData' => array('attributeName' => 'bbb', 'relatedModelData' => array('attributeName' => 'ggg', 'relatedModelData' => array('attributeName' => 'gggMember2', 'operatorType' => 'equals', 'value' => 'somevalue')))));
     //Build the query 'where' and 'joins'. Confirm they are as expected
     $searchAttributeData['structure'] = '1 and 2';
     //Build the query 'where' and 'joins'. Confirm they are as expected
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('HHH');
     $where = ModelDataProviderUtil::makeWhere('HHH', $searchAttributeData, $joinTablesAdapter);
     $compareWhere = "({$quote}ggg{$quote}.{$quote}gggmember{$quote} = 'somevalue') and ";
     $compareWhere .= "({$quote}ggg{$quote}.{$quote}gggmember2{$quote} = 'somevalue')";
     $this->assertEquals($compareWhere, $where);
     $this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount());
     $this->assertEquals(3, $joinTablesAdapter->getLeftTableJoinCount());
     $leftTables = $joinTablesAdapter->getLeftTablesAndAliases();
     $this->assertEquals('aaa', $leftTables[0]['tableName']);
     $this->assertEquals('bbb', $leftTables[1]['tableName']);
     $this->assertEquals('ggg', $leftTables[2]['tableName']);
     //Stringing together some hasMany relations, so we need to select distinct.
     $this->assertFalse($joinTablesAdapter->getSelectDistinct());
     //Now test that the subsetSQL query produced is correct.
     $subsetSql = CCC::makeSubsetOrCountSqlQuery('hhh', $joinTablesAdapter, 1, 5, $where, null, false, $joinTablesAdapter->getSelectDistinct());
     $compareSubsetSql = "select {$quote}hhh{$quote}.{$quote}id{$quote} id ";
     $compareSubsetSql .= "from {$quote}hhh{$quote} ";
     $compareSubsetSql .= "left join {$quote}aaa{$quote} on ";
     $compareSubsetSql .= "{$quote}aaa{$quote}.{$quote}hhh_id{$quote} = {$quote}hhh{$quote}.{$quote}id{$quote} ";
     $compareSubsetSql .= "left join {$quote}bbb{$quote} on ";
     $compareSubsetSql .= "{$quote}bbb{$quote}.{$quote}id{$quote} = {$quote}aaa{$quote}.{$quote}bbb_id{$quote} ";
     $compareSubsetSql .= "left join {$quote}ggg{$quote} on ";
     $compareSubsetSql .= "{$quote}ggg{$quote}.{$quote}id{$quote} = {$quote}bbb{$quote}.{$quote}ggg_id{$quote} ";
     $compareSubsetSql .= "where " . $compareWhere . ' ';
     $compareSubsetSql .= 'limit 5 offset 1';
     $this->assertEquals($compareSubsetSql, $subsetSql);
     //Make sure the sql runs properly.
     $data = HHH::getSubset($joinTablesAdapter, 0, 5, $where, null, null, $joinTablesAdapter->getSelectDistinct());
 }
 public function testMultipleManyManysToTheSameModelSearchQueryFormsCorrectly()
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     $searchAttributeData = array();
     $searchAttributeData['clauses'] = array(1 => array('attributeName' => 'ppManyAssumptive', 'relatedModelData' => array('attributeName' => 'name', 'operatorType' => 'equals', 'value' => 'somevalue')), 2 => array('attributeName' => 'ppManySpecific', 'relatedModelData' => array('attributeName' => 'name', 'operatorType' => 'equals', 'value' => 'somevalue2')));
     $searchAttributeData['structure'] = '1 and 2';
     //Build the query 'where' and 'joins'. Confirm they are as expected
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('P');
     $where = ModelDataProviderUtil::makeWhere('P', $searchAttributeData, $joinTablesAdapter);
     $compareWhere = "({$quote}pp{$quote}.{$quote}name{$quote} = 'somevalue') and ";
     $compareWhere .= "({$quote}pp1{$quote}.{$quote}name{$quote} = 'somevalue2')";
     $this->assertEquals($compareWhere, $where);
     $this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount());
     $this->assertEquals(4, $joinTablesAdapter->getLeftTableJoinCount());
     $leftTables = $joinTablesAdapter->getLeftTablesAndAliases();
     $this->assertEquals('p_pp', $leftTables[0]['tableName']);
     $this->assertEquals('pp', $leftTables[1]['tableName']);
     $this->assertEquals('ppmanyspecificlink_p_pp', $leftTables[2]['tableName']);
     $this->assertEquals('pp', $leftTables[3]['tableName']);
     //Distinct because MANY_MANY relationships
     $this->assertTrue($joinTablesAdapter->getSelectDistinct());
     //Now test that the subsetSQL query produced is correct.
     $subsetSql = AAA::makeSubsetOrCountSqlQuery('p', $joinTablesAdapter, 1, 5, $where, null, false, $joinTablesAdapter->getSelectDistinct());
     $compareSubsetSql = "select distinct {$quote}p{$quote}.{$quote}id{$quote} id ";
     $compareSubsetSql .= "from {$quote}p{$quote} ";
     $compareSubsetSql .= "left join {$quote}p_pp{$quote} on ";
     $compareSubsetSql .= "{$quote}p_pp{$quote}.{$quote}p_id{$quote} = {$quote}p{$quote}.{$quote}id{$quote} ";
     $compareSubsetSql .= "left join {$quote}pp{$quote} on ";
     $compareSubsetSql .= "{$quote}pp{$quote}.{$quote}id{$quote} = {$quote}p_pp{$quote}.{$quote}pp_id{$quote} ";
     $compareSubsetSql .= "left join {$quote}ppmanyspecificlink_p_pp{$quote} on ";
     $compareSubsetSql .= "{$quote}ppmanyspecificlink_p_pp{$quote}.{$quote}p_id{$quote} = {$quote}p{$quote}.{$quote}id{$quote} ";
     $compareSubsetSql .= "left join {$quote}pp{$quote} pp1 on ";
     $compareSubsetSql .= "{$quote}pp1{$quote}.{$quote}id{$quote} = {$quote}ppmanyspecificlink_p_pp{$quote}.{$quote}pp_id{$quote} ";
     $compareSubsetSql .= "where " . $compareWhere . ' ';
     $compareSubsetSql .= 'limit 5 offset 1';
     $this->assertEquals($compareSubsetSql, $subsetSql);
     //Make sure the sql runs properly.
     $data = P::getSubset($joinTablesAdapter, 0, 5, $where, null, null, $joinTablesAdapter->getSelectDistinct());
 }
 public static function getCountByModelClassName($modelClassName, $relationItemIds, $ownedByFilter)
 {
     assert('is_string($modelClassName)');
     assert('is_array($relationItemIds)');
     assert('$ownedByFilter == LatestActivitiesConfigurationForm::OWNED_BY_FILTER_ALL ||
                 $ownedByFilter == LatestActivitiesConfigurationForm::OWNED_BY_FILTER_USER ||
                 is_int($ownedByFilter)');
     $searchAttributeDataArray = static::getSearchAttributesDataByModelClassNamesAndRelatedItemIds(array($modelClassName), $relationItemIds, $ownedByFilter);
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
     $where = ModelDataProviderUtil::makeWhere($modelClassName, $searchAttributeDataArray[0][$modelClassName], $joinTablesAdapter);
     $count = RedBeanModel::getCount($joinTablesAdapter, $where, $modelClassName, true);
     if (isset($searchAttributeDataArray[1][$modelClassName])) {
         $where = ModelDataProviderUtil::makeWhere($modelClassName, $searchAttributeDataArray[1][$modelClassName], $joinTablesAdapter);
         return $count + RedBeanModel::getCount($joinTablesAdapter, $where, $modelClassName, true);
     }
     return $count;
 }