Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Try this one:</p> <pre><code>$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 agencies_industries AS IndustryData ON agencies.id = IndustryData.agency_id LEFT JOIN agencies_professions AS ProfessionData ON agencies.id = ProfessionData.agency_id LEFT JOIN agencies_sectors AS SectorData ON agencies.id = SectorData.agency_id LEFT JOIN agencies_seniorities AS SeniorityData ON agencies.id = SeniorityData.agency_id LEFT JOIN agencies_zones AS ZonesData ON agencies.id = ZonesData.agency_id LEFT JOIN agencies_countries AS CountryData ON agencies.id = CountryData.agency_id LEFT JOIN agencies_regions AS RegionData ON agencies.id = RegionData.agency_id LEFT JOIN agencies_cities AS CityData ON agencies.id = CityData.agency_id LEFT JOIN agencies_specialism AS SpecialismData ON agencies.id = SpecialismData.agency_id LEFT JOIN offices AS OfficeData ON (agencies.id = OfficeData.agency_id AND OfficeData.hq = "1") LEFT JOIN countries AS OfficeCountryData ON OfficeData.hq = OfficeCountryData.id LEFT JOIN contacts AS ContactData ON agencies.id = ContactData.agency_id '; </code></pre> <p>But even then it might be slow since you join too many tables. But it's hard to tell without knowing anything about your data and the amount of rows that you'll return. You might want to move some JOINS to a subquery (like country) if you only return a few rows. Or you can add that information in a seperate query.</p> <p>Edit: Without knowing your data and db-structure it's hard to tell. There are a lot of things that influence the speed of your query. First rewrite your query so that tables that are not used for your selection (i.e. the WHERE) or fields you want to show are not used in your query. So if you make no selection (emtpy $selectedFilters) the you don't have to include the industries, professions, sectors, seniorities, etc tables.:</p> <pre><code>$selectedFilters = $this-&gt;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'; $sql2=' LEFT JOIN offices AS OfficeData ON (agencies.id = OfficeData.agency_id AND OfficeData.hq = "1") LEFT JOIN countries AS OfficeCountryData ON OfficeData.hq = OfficeCountryData.id LEFT JOIN contacts AS ContactData ON agencies.id = ContactData.agency_id '; $whereSQL = ' WHERE 1 = 1 '; foreach($selectedFilters as $key) foreach($key as $name=&gt;$value){ if(is_array($key)) foreach($key as $key=&gt;$value){ $i = 0; $connector = 'AND'; if(is_array($value)){ foreach($value as $value){ if($i &gt; 0) $connector = 'OR'; $i++; switch($key){ case 'Profession': $whereSQL .= $connector.' ProfessionData.profession_id = ' . $value . ' '; $sql2.=' LEFT JOIN agencies_professions AS ProfessionData ON agencies.id = ProfessionData.agency_id '; break; case 'Specialism': $whereSQL .= $connector.' SpecialismData.specialism_id = ' . $value . ' '; $sql2.=' LEFT JOIN agencies_specialism AS SpecialismData ON agencies.id = SpecialismData.agency_id '; break; case 'SubSpecialism': $whereSQL .= ''; //$whereSQL .= $connector.' SubData.sub_specialism_id = ' . $value . ' '; break; case 'Seniority': $whereSQL .= $connector.' SeniorityData.seniority_id = ' . $value . ' '; $sql2.=' LEFT JOIN agencies_seniorities AS SeniorityData ON agencies.id = SeniorityData.agency_id '; break; case 'Industry': $whereSQL .= $connector.' IndustryData.industry_id = ' . $value . ' '; $sql2=' LEFT JOIN agencies_industries AS IndustryData ON agencies.id = IndustryData.agency_id '; break; case 'Zone': $whereSQL .= $connector.' ZonesData.zone_id = ' . $value . ' '; $sql2.=' LEFT JOIN agencies_zones AS ZonesData ON agencies.id = ZonesData.agency_id '; break; case 'Country': $whereSQL .= $connector.' CountryData.country_id = ' . $value . ' '; $sql2.=' LEFT JOIN agencies_countries AS CountryData ON agencies.id = CountryData.agency_id '; break; case 'Region': $whereSQL .= $connector.' RegionData.region_id = ' . $value . ' '; $sql2.=' LEFT JOIN agencies_regions AS RegionData ON agencies.id = RegionData.agency_id '; break; case 'City': $whereSQL .= $connector.' CityData.city_id = ' . $value . ' '; $sql2.=' LEFT JOIN agencies_cities AS CityData ON agencies.id = CityData.agency_id '; break; case 'Sector': $whereSQL .= $connector.' SectorData.sector_id = ' . $value . ' '; $sql2.='LEFT JOIN agencies_sectors AS SectorData ON agencies.id = SectorData.agency_id '; break; case 'status': $whereSQL .= $connector.' agencies.status = "' . $value . '" '; break; case 'size': $whereSQL .= $connector.' agencies.size = "' . $value . '" '; break; } } } else if(!isBlank($value) &amp;&amp; $key != 'Search') $whereSQL .= $connector.' agencies.'.$key.' = "'.$value.'" '; } } $groupBySQL = 'GROUP BY agencies.id ORDER BY agencies.id ASC'; $resultAgencies = $this-&gt;Agency-&gt;query($selectSQL . $sql2 . $whereSQL . $groupBySQL); $this-&gt;set(compact('resultAgencies')); </code></pre> <p>Second take a good look at your indexes for each table. Make sure you have an index on the fields you use in the JOINS. </p> <p>Third, look at the field types you use. Don't use a INT if a SMALLINT is large enough.</p> <p>Finaly: Normalization is great, but sometimes it's better to combine some things, even if that means you have duplicate data.</p>
    singulars
    1. This table or related slice is empty.
    1. POEfficiency in SQL query
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USNin
    1. USNin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POEfficiency in SQL query
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO`Unknown column 'offices.hq' in 'on clause'` (and yes, there is a column name `hq` in the offices table.
      singulars
      1. PO
      1. USPiotr Chabros
    2. COchange offices to OfficeData (also changed in the code above)
      singulars
      1. PO
      1. USNin
    3. COStill have to wait like a minute for the results (even without selecting any data).
      singulars
      1. PO
      1. USPiotr Chabros
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload