Быстрая выгрузка результатов опроса из 1С-Битрикс и как передать в Excel произвольное количество столбцов SQL-запросом

Итак, у нас есть часто применяемый в государственных структурах 1С-Битрикс и опрос с 30 или 50 вопросами и несколькими десятками тысяч ответов. При попытке экспортировать результаты опроса силами стандартных механизмов 1С-Битрикс все это выполняется от получаса и более, что является не очень рабочим сценарием. Конечно, можно запустить процесс формирования выгрузки фоном, и в конце высылать на почту сформированный xls-файлик… Но практика показала, что при формировании больших xls-файлов силами php серверу еще частенько и памяти не хватает… Окончательным «гвоздем» в подобные решения стало то, что автор стати не владеет PHP, но владеет SQL. Отсюда и родилась мысль написать SQL-запрос, который можно выполнить через Workbench или DBeaver (кто его ставит — не забудьте сразу снять галочку, что пустые строки разделяют запросы), и его результаты загрузить в Excel. В дальнейшем этот запрос можно будет запускать и из PHP с дальнейшим формированием файла с выгрузкой.

Первой же проблемой, с которой столкнулся автор, стало то, что мы заранее не знаем сколько столбцов в результатах опроса, потому что в общем случае мы не знаем количество вопросов. Можно, конечно, каждый раз переписывать запрос по какому-то шаблону, но лень — двигатель прогресса, который решил сделать следующим образом. Из-за того, что нам в подавляющем количестве случаев результаты все равно нужны в Excel, мы формируем в результатах запроса одно текстовое поле, которое будет строкой csv-файла. При этом пришлось пойти на небольшие допущения. Пришлось внести изменения в данные в процессе выгрузки: двойные кавычки (их используем в качестве ограничителя строк в csv) заменяем на одинарные, а переносы строк просто удаляем.

Второй проблемой стало то, что автор не сумел «запихать» всю логику в один запрос без использования CTE. А CTE до определенного момента не поддерживались в mySQL. Поэтому в своё время была написана хранимая процедура, формирующую временную таблицу и работающая уже с ней.

Ниже привожу два варианта SQL-кода. Первый представляет собой одиночный запрос с использованием CTE (может использоваться в относительно новых версиях СУБД mySQL и MariaDB). Если ваша СУБД не поддерживает CTE, то используйте второй вариант с созданием хранимой процедуры. Подставляете в нужные места идентификаторы опроса и сроки для выгрузки и используете в соответствии с комментариями (особое внимание на сохранение в ANSI). Лично я копипасчу результаты выполнения запроса из DBeaver в Notepad++ и сохраняю в csv-файл. Такой csv-файл Excel корректно открывает по умолчанию по двум кликам. Но если этого не произошло — используйте функционал импорта из текста в соответствии с изображением:

В общем, запускайте запросы, встраивайте их в свой код, формируйте ответы и радуйте своих заказчиков.

Запрос с CTE

/*запрос позволяет вытащить результаты любого голосования в Битрикс Управление Сайтом в форме csv  Внесите идентификатор опроса в двух местах в запросе (сейчас поставлено 24), укажите период  за который нужна выгрузка, выполните запрос, сохраните результаты в csv-файле (в ANSI) и отройте его в Excel  В текстах будут удалены переносы и табуляции, а двойные кавычки заменены на одинарные  */ with  cte_headers (id, uuser, ddate, answers) as ( -- формируем строку, которая станет заголовком столбцов в csv.  select 'ID',-- потом склеим её с результатами через union 'Посетитель (ID)' , 'Дата', -- меняем двойные кавычки на одинарные (чтобы корректно работал разделитель). Здесь и далее ячейки в csv будут обернуты двойными кавычками и разделены точкой с запятой (select group_concat(replace(coalesce(b_vote_question.question,''),'"',Char(39)) separator '";"')            from b_vote_question      where b_vote_question.vote_id=24  -- внести идентификатор опроса     order by  b_vote_question.c_sort)), cte_b_vote_event_id as ( -- выбираем факты голосования, которые нам нужны select id, date_vote, vote_user_id  from b_vote_event  where b_vote_event.vote_id=24 and -- внести идентификатор опроса b_vote_event.date_vote>'2017-01-01 00:00:00' and  -- внести дату начала выборки b_vote_event.date_vote<'2023-01-01 00:00:00'),   -- внести дату окончания выборки cte_vote_data (id, uuser, ddate, answers) as( -- выбираем все ответы, "склеиваем" их в CSV. Ячейка ограничена двойными кавычками. Разделитель - точка с запятой select cte_b_vote_event_id.id as id,  coalesce(concat(b_user.login,' ', coalesce(b_user.name,''),' ',coalesce(b_user.last_name,''),' (',b_user.id,')'),'неавторизованный пользователь') as user,  cte_b_vote_event_id.date_vote as ddate, (select group_concat(coalesce((select group_concat(trim(concat(replace(coalesce(b_vote_answer.message,' '),'"',Char(39)),' ',replace(coalesce(b_vote_event_answer.message,' '),'"',Char(39)) )) separator ' && ') -- && разделяет если ответов несколько from b_vote_event_answer -- так как ответ может быть внесен пользователем "руками" или выбран из готового, то просто конкатим эти строки left join b_vote_answer on b_vote_answer.id=b_vote_event_answer.answer_id where b_vote_event_answer.event_question_id=b_vote_event_question.id  ),' ') separator '";"') from b_vote_question left join b_vote_event_question on b_vote_event_question.question_id=b_vote_question.id and b_vote_event_question.event_id=cte_b_vote_event_id.id               where b_vote_question.vote_id=24) as content -- внести идентификатор опроса from  cte_b_vote_event_id left join b_vote_user on b_vote_user.id=cte_b_vote_event_id.vote_user_id left join b_user on b_user.id=b_vote_user.auth_user_id order by id desc)               -- в итоговом запросе склеиваем заголовки с ответами и убираем переносы,  чтобы не "ломался" csv-файл select replace(replace(concat('"',id,'";"',uuser,'";"',ddate,'";"',answers,'"'),char(10),''),char(13),'')  from cte_headers union select replace(replace(concat('"',id,'";"',uuser,'";"',ddate,'";"',answers,'"'),char(10),''),char(13),'')  from cte_vote_data

Запрос без CTE (если в вашей СУБД CTE не поддерживается)

/*скрипт позволяет вытащить результаты любого голосования в Битрикс Управление Сайтом в форме csv  Внесите идентификатор опроса в двух местах в запросе (сейчас поставлено 24), укажите период  за который нужна выгрузка, выполните запрос, сохраните результаты в csv-файле (в ANSI) и отройте его в Excel  В текстах будут удалены переносы и табуляции, а двойные кавычки заменены на одинарные    */  /* При вызове всего этого скрипта из кода необходимо проверять существоваение хранимки get_b_votes и только если она есть - выполнять дальнейший код, что бы не пересекаться с другими сессиями */ SET @@session.group_concat_max_len = 100000; /*этот параметр можно выставить в кофигурации mysql, он ограничивает максимальную длину, возвращаемую GROUP_CONCAT*/ SET SQL_SAFE_UPDATES = 0; DROP PROCEDURE IF EXISTS get_b_votes; delimiter $$ create procedure get_b_votes( in _vote_id int, in _date_from timestamp, in _date_to timestamp ) begin /*Создаем курсор и обвязку для формирования осташейся части таблицы*/     Declare done int default 0;     Declare now_vote_event_id int;       Declare vote_event_cursor Cursor for (select id from b_vote_event where b_vote_event.vote_id=_vote_id and b_vote_event.DATE_VOTE<_date_to and b_vote_event.DATE_VOTE>_date_from order by id desc);     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;  /*Создаем временные таблицу, содержимое которых потом вернем*/     DROP TABLE IF EXISTS get_b_votes_temp_table;     CREATE TEMPORARY TABLE get_b_votes_temp_table (     `id` varchar(255),     `user` varchar(255),     `ddate` varchar(255),     `content` longtext     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;     DROP TABLE IF EXISTS get_b_votes_return_table;     CREATE TEMPORARY TABLE get_b_votes_return_table (     `content` longtext     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  /*Формируем первую строку таблицы (заголовки)*/         insert into get_b_votes_temp_table (id, user, ddate, content)     (     select 'ID','Посетитель (ID)' ,'Дата',         (select group_concat(replace(coalesce(b_vote_question.question,''),'"',Char(39)) separator '","')            from b_vote_question      where b_vote_question.vote_ID=_vote_id      order by  b_vote_question.c_sort));  /*Формируем оставшуюся часть таблицы*/         open vote_event_cursor;      read_loop:LOOP         FETCH vote_event_cursor INTO now_vote_event_id;         IF done THEN LEAVE read_loop;  END IF;             insert into get_b_votes_temp_table (id,user,ddate,content)                    select                  b_vote_event.id as id,                  coalesce(concat(b_user.login,' ', coalesce(b_user.name,''),' ',coalesce(b_user.last_name,''),' (',b_user.id,')'),'неавторизованный пользователь') as user,                  b_vote_event.date_vote as ddate,                    (select group_concat(coalesce((select group_concat(trim(concat(replace(coalesce(b_vote_answer.message,' '),'"',Char(39)),' ',replace(coalesce(b_vote_event_answer.message,' '),'"',Char(39)) )) separator ' && ') -- && разделяет если ответов несколько                                                 from b_vote_event_answer                                                 left join b_vote_answer on b_vote_answer.ID=b_vote_event_answer.answer_id                                                 where b_vote_event_answer.event_question_ID=b_vote_event_question.id                                                  ),' ') separator '","')                 from b_vote_question                 left join b_vote_event_question on b_vote_event_question.question_ID=b_vote_question.id and b_vote_event_question.EVENT_ID=now_vote_event_id                                where b_vote_question.VOTE_ID=_vote_id) as content                 from  b_vote_event                     left join b_vote_user on b_vote_user.id=b_vote_event.vote_user_id                     left join b_user on b_user.id=b_vote_user.auth_user_id                     where b_vote_event.id=now_vote_event_id;      END LOOP; /*закрываем курсор*/              close vote_event_cursor;     /*Проводим окончательную обработку - сливаем все в один столбец*/         insert into get_b_votes_return_table (content) select replace(replace(concat('"',get_b_votes_temp_table.id,'","',get_b_votes_temp_table.user,'","',get_b_votes_temp_table.ddate,'","',get_b_votes_temp_table.content,'"'),char(10),''),char(13),'') from get_b_votes_temp_table; /*Возвращаем что получилось*/         select * from get_b_votes_return_table as result;   end $$ delimiter ; /*это - пример вызова созданной процедуры. Сюда нужно подставлять индентификатор опроса (зд. опрос битрикса а не хайлоад-блок) и сроки для выборки с... по...*/ call get_b_votes(24,'2017-01-01 00:00:00','2023-05-01 00:00:00');  DROP PROCEDURE IF EXISTS get_b_votes;

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

P.S. Автор не занимается продвижением или критикой 1С-Битрикс, а взаимодействует с ним таким, какой он есть. Также автор не претендует на звание гуру SQL и оформления кода.