Цикл в цикле как избежать?

[Plexx]

Имеем города
Имеем пользователей

таблицы
1 - city
id name
2 - users
id name city

Нужно выбрать все города и рядом с каждым городом вывести цифрой кол-во юзеров которые отмечены в данном городе.

Т.к. опыта маловато то первым делом приходит на ум запрос в цикле. Но так оч много запросов получается.

$sql_result = mysql_query("SELECT * FROM city");
while ($row = mysql_fetch_assoc($sql_result)) {


$sql_result1 = mysql_query("SELECT city FROM users WHERE city='".$row['id']."'");
$count = mysql_num_rows($sql_result1);


$content .="$row['name'] - $count";

}

Как это будет выглядеть через COUNT и LEFT JOIN одним запросом? Как не пытался что то не получается.

@темы: PHP, Вопросы

Comments

[La personne mystique]

SELECT COUNT(users.id), city.name FROM users LEFT JOIN city ON users.city=city.id GROUP BY users.city?

[Plexx]

La personne mystique хорошо но при таком запросе выводит только те города которые имеют пользователей. А нужно вывести все города даже те в которых нет юзеров

[Джей Ди]

Plexx можно просто в лоб, не лучший вариант, но:

SELECT city.name, (SELECT COUNT(*) FROM users WHERE users.city=city.id) AS number FROM city

[Джей Ди]

Джей Ди а вообще лучше добавить поле number в city, и заполнять его либо триггером, либо напрямую когда пользователь устанаваливает/меняет город.

[La personne mystique]

Хе-хе ))
Как вариант:
SELECT COUNT(users.id), city.name FROM city LEFT JOIN users ON city.id=users.city GROUP BY city.id;

Хотя получится не слишком быстро.


upd:
CREATE TABLE `city` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`city` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
);

SELECT COUNT(users.id), city.name FROM city FORCE INDEX (PRIMARY) LEFT JOIN users ON city.id=users.city GROUP BY city.id;

Вот так вполне себе быстро.

[Plexx]

хотелось бы универсальнее. т.к. часто встречается цикл к которому нужно пристыковать либо count либо просто какую то выборку

[Plexx]

Повторяю.. Нужно выводить все города даже те в которых нет юзеров

[La personne mystique]

Нужно выводить все города даже те в которых нет юзеров

Ни один из предложенных выше трех вариантов не "катит"?

[Plexx]

La personne mystique везде выводит только те в которых помечены юзеры....

[Plexx]

Вот мой рабочий вариант и тут тоже выводит только отмеченные

SELECT COUNT(users.user_id) AS count, city.* FROM users, city WHERE city.id = users.city GROUP BY city.id

[La personne mystique]

CREATE TABLE `city` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
);
INSERT IGNORE INTO `city` VALUES (1, 'Moscow');
INSERT IGNORE INTO `city` VALUES (2, 'Spb');
INSERT IGNORE INTO `city` VALUES (3, 'Samara');
INSERT IGNORE INTO `city` VALUES (5, 'test');

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `city` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `city` (`city`)
);
INSERT IGNORE INTO `users` VALUES (1, 'a', 1);
INSERT IGNORE INTO `users` VALUES (2, 'b', 1);
INSERT IGNORE INTO `users` VALUES (3, 'c', 1);
INSERT IGNORE INTO `users` VALUES (4, 'd', 2);
INSERT IGNORE INTO `users` VALUES (5, 'e', 3);
INSERT IGNORE INTO `users` VALUES (6, 'f', 3);


mysql> SELECT city.name, (SELECT COUNT(*) FROM users WHERE users.city=city.id) AS number FROM city;
+--------+--------+
| name   | number |
+--------+--------+
| Moscow |      3 |
| Spb    |      1 |
| Samara |      2 |
| test   |      0 |
+--------+--------+
4 rows in set (0.00 sec)

mysql> SELECT COUNT(users.id), city.name FROM city FORCE INDEX (PRIMARY) LEFT JOIN users ON city.id=users.city GROUP BY city.id;
+-----------------+--------+
| COUNT(users.id) | name   |
+-----------------+--------+
|               3 | Moscow |
|               1 | Spb    |
|               2 | Samara |
|               0 | test   |
+-----------------+--------+
4 rows in set (0.00 sec)

Странно что-то. Города-то другие точно есть?

[Plexx]

Да есть контакт.. спасибо...La personne mystique

Если не трудно для закрепления объясните как это работает?

именно непонятно про индексы

[La personne mystique]

Ура )

Вариант Джей Ди выбирает таблицу city целиком и (условно) для каждой строки делает запрос, считая число пользователей в этом городе. Для обеспечения быстродействия этого всего хорошо бы создать индекс для users.city (хотя, если там ~50-100 пользователей, на обычно это можно "забить").
Мой вариант к таблице city подцепляет соответствующие значения из users и считает там количество не-NULL. Если интересно, посмотрите на результат запроса SELECT users.name, сity.name FROM city LEFT JOIN users ON city.id=users.city. Тут опять индекс по users.city, и FORCE INDEX (PRIMARY), чтобы city читалась по индексу (что избавляет сервер от необходимости создавать временную таблицу под этот запрос).

[Plexx]

все равно сложновато..

Я так понял FORCE INDEX (PRIMARY) обращается сразу к users.city если city имеет INDEX а не ко всей таблице?

Почему мы группируем вконце GROUP BY city.id?

[La personne mystique]

Plexx, индекс позволяет не перебирать и не сравнивать все строки в таблице.

dev.mysql.com/doc/refman/5.0/en/mysql-indexes.h...
dev.mysql.com/doc/refman/5.0/en/index-hints.htm...
www.mysql.ru/docs/man/EXPLAIN.html

[Джей Ди]

La personne mystique повторюсь ещё раз, лучшим способом с точки зрения производительности и гибкости - будет просто добавить ключ с числом юзеров в города. Если не хочется делать триггеры и обновлять в живую, то произвидить отложенное обновление. Работать будет быстрее чем все предложенные методы.

[Джей Ди]

Plexx не могу, кстати понять почему мой вариант не катит.

[Plexx]

Джей ди отлично катит. Спасибо и вам. Ваш метод я лучше понимаю и уже приспособил.
по производительности пока сказать не могу.

По поводу доп строки ключа. много раз замечал что со временем цифры показывать начинают не верные значения.. и тогда приходится писать доп функцию для перерасчета.

а тут все просто и напрямю.. + кэширование никто не отменял

[Джей Ди]

Plexx как много раз показала практика, точные и актуальные значения почти никогда не нужны.

Если много пользователей то можно спокойно забить на триггеры и т.д. И просто раз в день делать:
UPDATE city SET number=(SELECT COUNT(*) FROM users WHERE users.city=city.id)

P.S. Кстати, если уж совсем гнаться за производительностью то лучше не пользоваться счетчиками отличными от COUNT(*), почитайте на форумах.

[Plexx]

Джей Ди Спасибо. Будем иметь ввиду.. Я недавно начал осваивать язык, поэтому много пока еще неизвестного и непонятного.))