/** * AAA -> hasOne -> BBB -> hasMany -> FFF -> hasOne -> EEE * @depends testHasOneToHasOneToHasOneUsingRelatedAttributeName */ public function testHasOneToHasManyToHasOne() { $quote = DatabaseCompatibilityUtil::getQuote(); $searchAttributeData = array(); $searchAttributeData['clauses'] = array(1 => array('attributeName' => 'bbb', 'relatedModelData' => array('attributeName' => 'fff', 'relatedModelData' => array('attributeName' => 'eee', 'relatedModelData' => array('attributeName' => 'eeeMember', 'operatorType' => 'equals', 'value' => 'somevalue'))))); //Build the query 'where' and 'joins'. Confirm they are as expected $searchAttributeData['structure'] = '1'; //Build the query 'where' and 'joins'. Confirm they are as expected $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('AAA'); $where = ModelDataProviderUtil::makeWhere('AAA', $searchAttributeData, $joinTablesAdapter); $compareWhere = "({$quote}eee{$quote}.{$quote}eeemember{$quote} = 'somevalue')"; $this->assertEquals($compareWhere, $where); $this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount()); $this->assertEquals(3, $joinTablesAdapter->getLeftTableJoinCount()); $leftTables = $joinTablesAdapter->getLeftTablesAndAliases(); $this->assertEquals('bbb', $leftTables[0]['tableName']); $this->assertEquals('fff', $leftTables[1]['tableName']); $this->assertEquals('eee', $leftTables[2]['tableName']); //Stringing together some hasMany relations, so we need to select distinct. $this->assertTrue($joinTablesAdapter->getSelectDistinct()); //Now test that the subsetSQL query produced is correct. $subsetSql = AAA::makeSubsetOrCountSqlQuery('aaa', $joinTablesAdapter, 1, 5, $where, null, false, $joinTablesAdapter->getSelectDistinct()); $compareSubsetSql = "select distinct {$quote}aaa{$quote}.{$quote}id{$quote} id "; $compareSubsetSql .= "from {$quote}aaa{$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}fff{$quote} on "; $compareSubsetSql .= "{$quote}fff{$quote}.{$quote}bbb_id{$quote} = {$quote}bbb{$quote}.{$quote}id{$quote} "; $compareSubsetSql .= "left join {$quote}eee{$quote} on "; $compareSubsetSql .= "{$quote}eee{$quote}.{$quote}id{$quote} = {$quote}fff{$quote}.{$quote}eee_id{$quote} "; $compareSubsetSql .= "where " . $compareWhere . ' '; $compareSubsetSql .= 'limit 5 offset 1'; $this->assertEquals($compareSubsetSql, $subsetSql); //Make sure the sql runs properly. $data = AAA::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()); }