There are 3 tables:

  1. properties id | published | ... id | published | ...
  2. categories object categories id | category_name | ... id | category_name | ...
  3. property_categories connection of objects with categories (one object can belong to several categories) ... | pid | category_id | ... | pid | category_id |

It is necessary to obtain a multidimensional array of categories of objects with the number of objects included in this category, excluding 0. At the moment, with one query I get an array of categories, and already sorting through the array, I count the number of matches in the table of objects. For simplicity, I removed from the code all unnecessary:

I pull out the categories that are used, that is, if their id is found in objects and they are published:

 SELECT DISTINCT a.id, a.category_name FROM categories AS a INNER JOIN property_categories AS b ON a.id = b.category_id INNER JOIN properties AS c ON b.pid = c.id AND c.published = '1'

Can I (should) do without DISTINCT , avoiding repetitions in the resulting array?

Parse the array and count the number of category objects in the loop:

 for($i=0;$i<count($categories);$i++){ $category = $categories[$i]; ... SELECT COUNT(a.id) FROM properties AS a INNER JOIN property_categories AS b ON b.pid = a.id INNER JOIN categories AS c ON b.category_id = c.id AND c.id = '$category->id' WHERE a.published = 1 ... }

How can I get the number of category objects in the first query, avoiding subsequent queries in a loop?

It seems that the truth is already somewhere nearby, but the head is already spinning = (

  • one
    select a.id, a.category_name, count(1) from ... where .... group by a.id, a.category_name - Mike
  • @Mike thank you so much, it works! - Blacknife
  • @Mile can not understand, in the count(1) unit means the number of the column on which the count is? How it works? - Blacknife
  • Instead of it is necessary to consider a specific column. count (column) considers not null values ​​in it. count (1) counts just the number of lines, since in each line he sees, he sees 1. Read more about this here. stackoverflow.com/questions/511270/… there have recently been performance problems due to the count (column) - Mike
  • In principle, SQL standards for such cases recommend count (*), but I don’t like it, in the old Oracle the optimizer worked better with count (1) than with * - Mike

1 answer 1

Reply from comments from Mike

 SELECT a.id AS id, a.category_name AS category_name, count(1) AS total FROM categories AS a INNER JOIN property_categories AS b ON a.id = b.category_id INNER JOIN properties AS c ON b.pid = c.id WHERE c.published = '1' GROUP BY a.id, a.category_name