Temat: Ja chcę czysty SQL!
Mnie krew zalewa jak widzę takie zapiski jak poniżej:
[quote]$oSelect1 = $this->select()
->from(array('o' => $this->_name), array(
'o.i_id', 'o.s_name', 'o.s_tags', 'o.s_place',
'o.i_region_id', 'o.i_district_id',
'o.s_gps_coords'
))
->columns(array(
'i_location_type' => new Zend_Db_Expr(Objects_Model_Poi_Row::LOCATION_TYPE_SINGLE),
's_availability' => new Zend_Db_Expr('NULL'),
'i_type' => new Zend_Db_Expr(self::OBJECT_TYPE_NORMAL),
))
->setIntegrityCheck(false)
->joinLeft(array('s1' => 'erp_packages_services_accommodations'), 's1.i_object_id = o.i_id AND s1.s_deleted_at IS NULL',
array(
's1.i_id as i_accommodation_id', 's1.s_name as s_accommodation_name', 's1.i_out_of_season_availability',
's1.i_other_localization as i_accommodation_other_localization', 's1.s_gps_coords as s_accommodation_gps_coords'
)
)
->joinLeft(array('s2' => 'erp_packages_services_catering'), 's2.i_object_id = o.i_id AND s2.s_deleted_at IS NULL',
array('s2.i_id as i_catering_id', 's2.s_name as s_catering_name')
)
->joinLeft(array('s4' => 'erp_packages_services_attractions'), 's4.i_object_id = o.i_id AND s4.s_deleted_at IS NULL' . $s_where_s4,
array(
's4.i_id as i_attraction_id', 's4.s_name as s_attraction_name',
's4.i_other_location as i_attraction_other_location', 's4.s_gps_coords as s_attraction_gps_coords', 's4.i_location_type as i_attraction_location_type'
)
)
->joinLeft(array('p2o' => 'erp_pictures2objects'), 'p2o.i_object_id = o.i_id AND p2o.i_main = 1',
array('p2o.i_picture_id')
)
->joinLeft(array('p' => 'erp_pictures'), 'p.i_id = p2o.i_picture_id',
array('p.s_extension as s_picture_extension')
)
;
if ($params['s_starting_at'] !== '' || $params['s_ending_at'] !== '') {
$oSelect1->joinLeft(array('s1s' => 'erp_packages_services_accommodations_seasons'), 's1s.i_accommodation_id = s1.i_id AND s1s.s_deleted_at IS NULL' . $s_where_s1s,
array('s1s.i_id as i_season_id')
);
}
$oSelect1->where('s1.i_id IS NOT NULL OR s2.i_id IS NOT NULL OR s4.i_id IS NOT NULL');
if ($params['s_text'] !== '') {
$oSelect1->where("LOWER(o.s_name) LIKE LOWER('%{$params['s_text']}%') OR LOWER(o.s_tags) LIKE LOWER('%{$params['s_text']}%')");
}
if ($params['i_accommodation'] !== '0') {
$oSelect1->where('s1.i_id IS NOT NULL');
}
if ($params['i_catering'] !== '0') {
$oSelect1->where('s2.i_id IS NOT NULL');
}
if ($params['i_attraction'] !== '0') {
$oSelect1->where('s4.i_id IS NOT NULL');
}
if ($params['s_place'] !== '') {
$oSelect1->where('LOWER(o.s_place) LIKE LOWER(?)', $params['s_place']);
}
if ($params['i_land_id'] !== '') {
$oSelect1->where('o.i_land_id = ?', $params['i_land_id']);
}
if ($params['i_region_id'] !== '') {
$oSelect1->where('o.i_region_id = ?', $params['i_region_id']);
}
if ($params['i_district_id'] !== '') {
$oSelect1->where('o.i_district_id= ?', $params['i_district_id']);
}
$firephp->info($oSelect1->__toString(), 'sql');
$oPoiModel = new Objects_Model_Poi();
$oSelect2 = $oPoiModel->findServices($params);
if (!$params['i_poi'] && (!$params['i_accommodation'] && !$params['i_catering'] && !$params['i_attraction'])) {
// przeszukaj usługi i obiekty okołoturystyczne
$oUnionSelect = $this->select()->union(array($oSelect1, $oSelect2), Zend_Db_Select::SQL_UNION_ALL);
$firephp->info($oUnionSelect->__toString(), 'sql');
$aRowset = $this->fetchAll($oUnionSelect)->toArray();
} else if ($params['i_accommodation'] || $params['i_catering'] || $params['i_attraction']) {
// przeszukaj tylko usługi
$aRowset = $this->fetchAll($oSelect1)->toArray();
} else if ($params['i_poi']) {
// przeszukaj tylko obiekty okołoturystyczne
$aRowset = $this->fetchAll($oSelect2)->toArray();
}[/quote]
Powyższe zapytanie jest niczym innym jak antywzorcem. Jest nieczytelne i posiada błędy.
Takie rzeczy robi się poprzez widoki ( w końcu do czegoś one są).
пропозиції роботи
