Курилка.co.ua
Orphus RSS kurilka.co.ua

Category

Archives

Синхронизация количества записей в древовидных категориях

Author wmas wmas | Category Category MySQL, PHP

Здравствуйте, уважаемые посетители блога Курилка.co.ua. В своей заметке: «MySQL: Синхронизация данных (пример с категориями и записями)» — я пытался, коротко и понятно, растолковать концепцию затронутого вопроса. Идя от простого к сложному, методично акцентируя внимание лишь на усложнении и уменьшении количества SQL-запросов. В тоже время, снова забываюсь, что некоторые, кажущиеся простыми и очевидными, вещи могут оказаться сложными. В частности, Александр попросил меня уделить внимание работе с древовидной структурой категорий, что я с удовольствием и делаю.

Рассмотрим несколько усложненный, в отличии от использованной в ранее упомянутой заметке, пример следующей структур баз данных:

Синхронизация количества записей в древовидных категориях

У нас есть таблица items, в которой хранятся некие записи. Каждая из записей соответствует той или иной категории из таблицы cats. Особенностью данного примера является то, что здесь мы используем вспомогательное после cat_parent, которое содержит значение идентификатора родительской категории (cat_id). Таким образом, получается древовидная структура и проблема подсчета количества записей соответствующих той или иной категории — становится более затруднительным делом. Надеюсь, пока, всё более-менее понятно?

Шаг 1: подсчёт и синхронизация количества записей в категория

Как таковой, суть предлагаемого мной варианта сводится к тому, чтобы не мучиться с подсчетом суммирующего значения количества записей для родительских категорий. По крайней мере, на уровне работы с базами данных. Так что SQL-запрос остается прежним:

UPDATE cats SET cats.cat_count = ( SELECT COUNT(*) FROM items WHERE items.cat_id=cats.cat_id )

Шаг 2: класс cats и данные категорий

Фактически, всю работу с категориями я осуществляю через класс cats, основной фишкой которого является ассоциативный массив данных категорий ($data) и вспомогательный массив идентификаторов подкатегорий ($childrens). Вот как может выглядеть PHP-код этого класса:

class cats {
var $data = array();
var $childrens = array();
  function init() {
    $this->data = array();
    $this->childrens = array();
    $r = mysql_query("SELECT * FROM cats");
    if ( ($r) && (mysql_num_rows($r)>0) ) {
      while ( $ar=mysql_fetch_array($r) ) {
        $this->data[$ar['cat_id']] = $ar;
        $this->childrens[$ar['cat_parent']][] = $ar['cat_id'];
      }
    }
  }
}

Обратите внимание на внутреннюю переменную $childrens, представляющую собой двумерный массив, состоящий из идентификаторов родительских категорий (cat_parent) и соответствующих им идентификаторов подкатегорий (cat_id). В дальнейшем именно он и позволит нам рекурсивно (зациклено, идя по соответствующей ветке) подсчитывать суммарное значения родительской категории.

Что касается переменной $data, то для нашего примера её можно было бы свести к простому массиву из счетчиков, что-то вроде такого:

$this->data[$ar['cat_id']] = $ar['cat_count'];

Однако, класс cats в значительной мере потеряло бы свой смысл и функциональность. Тут уж потрудитесь и додумывайте сами.

Шаг 3: расчет суммарного значения количества записей в родительской категории

Как я уже и говорил, суть предлагаемого варианта сводится к тому, чтобы производить расчет суммарного значения количества записей в родительской категории, на программном уровне. Это осуществляется за счет обработки выше упомянутых массивов: $data и $childrens — рекурсивной функцией get_count() класса cats. Вот как может выглядеть её PHP-код:

function get_count($cat_parent=0) {
  $cat_count = 0;
  $childrens = $this->childrens[$cat_parent];
  if ( count($childrens)>0 ) {
    foreach ( $childrens as $cat_id ) {
      $ar = $this->data[$cat_id];
      $cat_count = $cat_count + $ar['cat_count'];
      $cat_count = $cat_count + $this->get_count($cat_id);
    }
  }
  return $cat_count;
}

Обратите внимание, что таким образом мы подсчитываем только суммарное количество записей у подкатегорий родительской категории $cat_parent. Если вы хотите учитывать и количество записей у родительской категории – его необходимо добавить отдельно. Это сделано для того, чтобы не усложнять рекурсию и избежать лишнего усложнения.

Шаг 4: пример применения класса cats

Несмотря на то, что всё уже более-менее очевидно, приведу пример применения класса cats. Вот как это может выглядеть:

$cats = new cats;
$cats->init();
$cat_parent = 1;
$cat_count = $cats->get_count($cat_parent);
$cat_count = $cat_count + $cats->data[$cat_parent]['cat_count'];
echo 'Сумарное значени cat_count для родительской категории #'.$cat_parent.' = '.$cat_count;

В начале мы создаем объект $cats нашего класса cats. Потом вызываем функцию считывания данных категорий init() через объект $cats. По сути, если функцию init() назвать также как и класс, т.е. cats(), то она будет выполнять при создании объекта и нам будет достаточно только первой строки. Но я посчитал разумным показать вам вариант, когда объект $cats создан, а данные категорий ещё не считаны. Это позволяет не создавать лишней нагрузки, применяя функцию init() лишь тогда, когда это необходимо.

Идём дальше? Переменной $cat_parent мы задаем значение идентификатора родительской категории, который используется как параметр для функции get_count() и как ключ подмассива $cats->data. Как я уже говорил, функция get_count() подсчитывает суммарное значения записей в подкатегориях, так что для полноты можно прибавить к $cat_count значение количества записей у родительской категории, что и было сделано в нашем примере.

Последняя строка выводит полученное значение.

Шаг 5: Синхронизация количества записей для родительских категорий

Можно сказать что этот «шаг» является чем-то вроде «P.S.» и рассматривает вариант с одноразовой синхронизацией счетчиков категорий. Другими словами, после такой синхронизации вам не понадобится что-то подсчитывать на программном уровне, получая конечные данные счетчиков из БД. Как это сделать?

Уточним последовательность наших действий. Как таковой, мы будем использовать всё тот же SQL-запрос, который подсчитает количество записей для каждой из категорий — функция sync() класса cats. При этом, счетчики родительских категорий, будут содержать лишь количество связанных с ними записей. Таким образом, нам необходимо добавить к ним сумму количества записей у подкатегорий, подсчитываемую функцией get_count() класса cats. Введём в наш класс cats дополнительную функцию sync_parents():

function sync() {
  mysql_query("UPDATE cats SET cats.cat_count = ( SELECT COUNT(*) FROM items WHERE items.cat_id=cats.cat_id )") or die(mysql_error());
}
function sync_parents() {
  if ( count($this->data)>0 ) {
    foreach ( $this->data as $ar ) {
      $childrens = $this->childrens[$ar['cat_id']];
      if ( count($childrens)>0 ) {
        $cat_count = $this->get_count($ar['cat_id']);
        if ( $cat_count>0 ) mysql_query("UPDATE cats SET cat_count=cat_count+".$cat_count);
      }
    }
  }
}

В приведенном примере, на всякий случай отобразил и содержание упомянутой функции sync(). Думаю, понятно, что она делает? Теперь поговорим о sync_parent(). Мы пройдемся по ассоциативному массиву $data – внутренняя переменная класса cats, которая должна содержит обновленные после вызова sync данные категорий. Внутри foreach (перебора) мы проверяем, есть ли у категории подкатегории, т.е. является она родительской, путем подсчета количества подкатегорий:

$childrens = $this->childrens[$ar['cat_id']];
if ( count($childrens)>0 ) {

Если таковые имеются, используя функция get_count() класса cats, подсчитываем сумму колличества записей в подкатегория ($cat_count). Дабы уменьшить количество запросов к БД, мы будет обновлять счетчик родительской категории только если $cat_count больше нуля.

if ( $cat_count>0 ) mysql_query("UPDATE cats SET cat_count=cat_count+".$cat_count);

Еще раз хочу подчеркнуть, что здесь мы добавляет к количеству записей родительской категории, количество записей в её подкатегориях:

cat_count=cat_count+".$cat_count

Теперь о том, как это применить:

$cats = new cats;
$cats->sync();
$cats->init();
$cats->sync_parents();

Обратите внимание, что функция считывания данных категорий init() мы используем после функции (теперь предварительной) синхронизации sync().

«Фух…» — вот вроде бы и всё. Возможно где-то мог ошибиться. Писал на вскидку, для заметки… но главное суть, которую постарался разложить от «А» до «Я». Напоследок, упомяну об одном очевидном и немаловажном недостаток такого варианта. Им является то, что при большом количестве категорий будет съедаться значительная доля памяти сервера. Зато мы получаем очень удобный инструмент работы с категориями. В общем, выбор за вами. В конце концов, исходя из сути вопроса, можно ограничиться одноразовой, полномасштабной синхронизацией и использовать конечные значения, хранимые в базе данных. Теперь точно все. Спасибо за внимание.

скачать Скачать пример синхронизации количества записей в древовидных категориях (sync.zip 1,96 КБ)
скачать Скачать пример синхронизации количества записей в древовидных категориях (sync.zip 1,96 КБ)

Publish: Вторник Ноя 24, 2009

5 Responses for "Синхронизация количества записей в древовидных категориях"

feed for comments on this post

  • Комментарий #2504 author: Александр Reply (subscribed to comments)
    publish: Вторник Ноя 24, 2009 at 8:59 пп

    вау, как вы быстро написали заметку, спасибо :-)
    решил проверить код, заметил что потомки дублируются несколько раз, пока не выяснил, просто времени нет разбираться, на выходе при

    echo '';
    print_r($cats->childrens );
    echo '';

    у меня массив получился таким

    [39] => Array
      (
        [0] => 40
        [1] => 43
        [2] => 40
        [3] => 43
    )

    то есть, в БД родитель id=39 имеет два потомка с id 40 и 43, но в массиве они повторяются дважды. Точно также и с другими ветками. Соответственно подсчёт является завышенным. Бегло просмотрел, вроде что-то не так в этой строчке

    $this->childrens[$ar['cat_parent']][] = $ar['id'];

    в функции cats()
    наверное думаю нужно делать проверку in_array() перед занесением в массив childrens

  • Комментарий #2505 author: wmas Reply
    publish: Среда Ноя 25, 2009 at 2:07 дп

    2Александр: hi! Честно говоря, я считал, что если при создании объекта не указывать круглые скобки, то одноименная функция класса вызываться не будет, просто создастся объект класса и всё :oops: Век живи век учись — конструктор класса он и есть… то, что есть.

    В общем получалось двойное считывание данных категорий. В заметке я переименовал функцию cats() на init(), хотелось оставить считывание данных категорий отдельной фишкой.

    Ну а для страховки, можно в функции считывания данных категорий обнулять внутренние переменные… тоже добавил в заметку… на всякий случа ;-)

  • Комментарий #2507 author: Александр Reply (subscribed to comments)
    publish: Четверг Ноя 26, 2009 at 10:05 дп

    нда, теперь вообще что-то не то, при указании любого из родителя, не подсчитывает записи, всегда показывает 0. Записи хранятся как у потомков, так и у самого родителя. Ладно, разбираться пока у меня нет времени, просто случайно заглянул, но всё равно спасибо вам, что уделили время. Для себя пока оставил свой вариант что выкладывал в прошлой вашей заметке, только изменил свою функцию

    data_count($cat_id)

    на ваш код

    $sql_result = $db->q("UPDATE category SET category.count = ( SELECT COUNT(*) FROM data WHERE data.catcategory.id )");

    таким образом, кол-во запросов сократилось на много, если тогда было 470 запросов, то теперь 27 шт. при этом время уменьшилось с 5 сек. до 0.47 сек., что конечно меня очень порадовало.
    Ещё бы если избавится от повторяющих запросов, тоесть если бы вашем SQL(что выше привёл) запросе, избавиться от обновлении родительских разделов, и обновлять только потомки, было бы супер. Потом просто можно пробежаться по родителям и суммировать кол-во записей, и обновить родителей. Тогда кол-во запросов уменьшилось ещё где-то в половину из тех 27 запросов.

  • Комментарий #2508 author: wmas Reply
    publish: Четверг Ноя 26, 2009 at 2:57 пп

    2Александр: в заметке я выложил архив с готовым примером. Там вроде всё подсчитывается, get_count() у меня считает. Если говорить о твоем SQL-запросе, то там есть проблема во WHERE подзапроса, где должно быть условие: поле идентификатора категории в БД записях (data) равно идентификатору категории в БД категорий (category) т.е.:

    $sql_result = $db->q("UPDATE category SET category.count = ( SELECT COUNT(*) FROM data WHERE data.catcategory.id=category.catcategory.id )");

    Что касается построения SQL запроса с избавлением UPDATE для родительских категорий… то тут, по сути, можно ввести вспомогательное поле в таблице category, например:

    is_parent enum('y','n') not null

    В таком случае, для UPDATE можно сделать дополнительное условие:

    $sql_result = $db->q("UPDATE category SET category.count = ( SELECT COUNT(*) FROM data WHERE data.catcategory.id=category.catcategory.id ) WHERE category.is_parent='y'");

    Нюанс в том, что потребуется как-то проверить кто родитель, а кто нет. С учетом использования моего класса это не проблема, там по count($cats->childrens[$cat_paren]) можно распознать. Но опять же это дополнительный гемор.

    Главное не искать готового решения, а понять суть. Слишком много вариантов…

  • Комментарий #2509 author: wmas Reply
    publish: Четверг Ноя 26, 2009 at 3:48 пп

    2Александр: дописал шаг 5, думаю ответ на твой вопрос. По крайней мере это вариант с наименьшим количеством SQL-запросов.


Popular links

Copyright © since 2006 Курилка.co.ua,
powered by WordPress