/**
* 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;
}