Я создал функцию поиска для своего приложения для тортов. Он состоит из нескольких окон выбора, в которых вы можете выбирать данные, затем он циклически перебирает выбранные параметры и реализует их в синтаксисе SQL.
В основном функция выглядит так:
$selectedFilters = $this->data;
$selectSQL = 'SELECT
agencies.agency, agencies.website_url, agencies.status, agencies.size, agencies.id, OfficeData.id, ContactData.name, ContactData.surname, ContactData.job_title, ContactData.email,
ContactData.mobile, OfficeCountryData.country
FROM agencies
LEFT JOIN (SELECT agencies_industries.agency_id, agencies_industries.industry_id FROM agencies_industries) AS IndustryData ON agencies.id = IndustryData.agency_id
LEFT JOIN (SELECT agencies_professions.agency_id, agencies_professions.profession_id FROM agencies_professions) AS ProfessionData ON agencies.id = ProfessionData.agency_id
LEFT JOIN (SELECT agencies_sectors.agency_id, agencies_sectors.sector_id FROM agencies_sectors) AS SectorData ON agencies.id = SectorData.agency_id
LEFT JOIN (SELECT agencies_seniorities.agency_id, agencies_seniorities.seniority_id FROM agencies_seniorities) AS SeniorityData ON agencies.id = SeniorityData.agency_id
LEFT JOIN (SELECT agencies_zones.agency_id, agencies_zones.zone_id FROM agencies_zones) AS ZonesData ON agencies.id = ZonesData.agency_id
LEFT JOIN (SELECT agencies_countries.agency_id, agencies_countries.country_id FROM agencies_countries) AS CountryData ON agencies.id = CountryData.agency_id
LEFT JOIN (SELECT agencies_regions.agency_id, agencies_regions.region_id FROM agencies_regions) AS RegionData ON agencies.id = RegionData.agency_id
LEFT JOIN (SELECT agencies_cities.agency_id, agencies_cities.city_id FROM agencies_cities) AS CityData ON agencies.id = CityData.agency_id
LEFT JOIN (SELECT agencies_specialisms.agency_id, agencies_specialisms.specialism_id FROM agencies_specialisms) AS SpecialismData ON agencies.id = SpecialismData.agency_id
LEFT JOIN (SELECT offices.id, offices.agency_id, offices.hq FROM offices WHERE offices.hq = "1") AS OfficeData ON agencies.id = OfficeData.agency_id
LEFT JOIN (SELECT countries.id, countries.country FROM countries) AS OfficeCountryData ON OfficeData.hq = OfficeCountryData.id
LEFT JOIN (SELECT contacts.name, contacts.surname, contacts.agency_id, contacts.job_title, contacts.email, contacts.mobile FROM contacts) AS ContactData ON agencies.id = ContactData.agency_id
';
$whereSQL = ' WHERE 1 = 1 ';
foreach($selectedFilters as $key)
foreach($key as $name=>$value){
if(is_array($key))
foreach($key as $key=>$value){
$i = 0;
$connector = 'AND';
if(is_array($value)){
foreach($value as $value){
if($i > 0)
$connector = 'OR';
$i++;
switch($key){
case 'Profession': $whereSQL .= $connector.' ProfessionData.profession_id = ' . $value . ' ';
break;
case 'Specialism': $whereSQL .= $connector.' SpecialismData.specialism_id = ' . $value . ' ';
break;
case 'SubSpecialism': $whereSQL .= ''; //$whereSQL .= $connector.' SubData.sub_specialism_id = ' . $value . ' ';
break;
case 'Seniority': $whereSQL .= $connector.' SeniorityData.seniority_id = ' . $value . ' ';
break;
case 'Industry': $whereSQL .= $connector.' IndustryData.industry_id = ' . $value . ' ';
break;
case 'Zone': $whereSQL .= $connector.' ZonesData.zone_id = ' . $value . ' ';
break;
case 'Country': $whereSQL .= $connector.' CountryData.country_id = ' . $value . ' ';
break;
case 'Region': $whereSQL .= $connector.' RegionData.region_id = ' . $value . ' ';
break;
case 'City': $whereSQL .= $connector.' CityData.city_id = ' . $value . ' ';
break;
case 'Sector': $whereSQL .= $connector.' SectorData.sector_id = ' . $value . ' ';
break;
case 'status': $whereSQL .= $connector.' agencies.status = "' . $value . '" ';
break;
case 'size': $whereSQL .= $connector.' agencies.size = "' . $value . '" ';
break;
}
}
}
else
if(!isBlank($value) && $key != 'Search')
$whereSQL .= $connector.' agencies.'.$key.' = "'.$value.'" ';
}
}
$groupBySQL = 'GROUP BY agencies.id ORDER BY agencies.id ASC';
$resultAgencies = $this->Agency->query($selectSQL . $whereSQL . $groupBySQL);
$this->set(compact('resultAgencies'));
Проблема, с которой я столкнулся при поиске, заключается в том, что он работает очень медленно. Это происходит из-за использования слишком большого количества команд LEFT JOIN
. Каждый LEFT JOIN
выбирает данные из отдельных таблиц и собирает их все, создавая другую таблицу. Затем данные отображаются.
Мне нужен кто-нибудь, чтобы подсказать, как это сделать, не используя так много LEFT JOIN
.
Ура.