PostgreSql работа с массивами
Недавно осознала, что в БД есть массивы. Тип данных массив.
Как я без них обходилась??
Например.
Есть файлы: таблица файлов (картинок в частности). И есть Объект с картинками: Таблица объекта.
Раньше, связь объекта и файлов я делала через таблицу промежуточную ИЛИ прям в таблице файлов указывала тип и ид к которому он прикреплен. Так как файлы могут быть прикреплены к разным объектам, типы объектов разрастались, а для добычи файлов требовался ещё один запрос.
Теперь, мне пришла мысль, почему бы в объекте не создать поле типа массив. Так как мне из таблицы файлов требуется только путь в повседневной работе, то создаю в таблице объекта поле масив varchar[1]. Ну а если требуется таки полная информация храню иды файлов.
В отличии от прежнего способа поиск файлов будет не через запрос join, а по идам (Primary Key) файлов. На практике не проверяла, но по идее запрос както проще выглядит.
К сожалению опыта не хватает казать Есть ли тут реальная оптимизация и не ухудшила ли я положение, НО работать проще стало.
Основные инструменты.
SQL запросы
INSERT into tbl (imgs) VALUES (ARRAY['1.jpg','2.jpg']);
UPDATE tbl SET imgs = array_append(img, 'aaa.png');
SELECT array_length(imgs, 1) as count_images FROM tbl;
Для поиска значения в массиве поля, лично я использую any
SELECT * from tbl WHERE '1.jpg' = ANY imgs;
И вот ещё одна полезная функция Postgresql unnest
Таким образом можно привести запрос к привычному мне виду
Select * from tbl WHERE '1.jpg' in unnest(imgs)
Запишу сюда же и то, как сделать обратное преобразование Есть в постгресе агрегатные функции array_agg(выражение)
Если в таблицы с одним ключом есть несколько записей, то эти записи можно выбрать как одно поле как массив, со значениями этих строк.... Не буду повторять тут этот пост
И полезная php функция
/**
* Преобразование формат массива PostgreSql в масив PHP
* Например, {1,2,3} в array(1,2,3);
* @param $pgArray
* @return array
*/
function PGArrayToPHPArray($pgArray)
{
$ret = array();
$stack = array(&$ret);
$pgArray = substr($pgArray, 1, -1);
$pgElements = explode(",", $pgArray);
//ArrayDump($pgElements);
foreach($pgElements as $elem)
{
if(substr($elem,-1) == "}")
{
$elem = substr($elem,0,-1);
$newSub = array();
while(substr($elem,0,1) != "{")
{
$newSub[] = $elem;
$elem = array_pop($ret);
}
$newSub[] = substr($elem,1);
$ret[] = array_reverse($newSub);
}
else
$ret[] = $elem;
}
return $ret;
}
Как я без них обходилась??
Например.
Есть файлы: таблица файлов (картинок в частности). И есть Объект с картинками: Таблица объекта.
Раньше, связь объекта и файлов я делала через таблицу промежуточную ИЛИ прям в таблице файлов указывала тип и ид к которому он прикреплен. Так как файлы могут быть прикреплены к разным объектам, типы объектов разрастались, а для добычи файлов требовался ещё один запрос.
Теперь, мне пришла мысль, почему бы в объекте не создать поле типа массив. Так как мне из таблицы файлов требуется только путь в повседневной работе, то создаю в таблице объекта поле масив varchar[1]. Ну а если требуется таки полная информация храню иды файлов.
В отличии от прежнего способа поиск файлов будет не через запрос join, а по идам (Primary Key) файлов. На практике не проверяла, но по идее запрос както проще выглядит.
К сожалению опыта не хватает казать Есть ли тут реальная оптимизация и не ухудшила ли я положение, НО работать проще стало.
Основные инструменты.
SQL запросы
INSERT into tbl (imgs) VALUES (ARRAY['1.jpg','2.jpg']);
UPDATE tbl SET imgs = array_append(img, 'aaa.png');
SELECT array_length(imgs, 1) as count_images FROM tbl;
Для поиска значения в массиве поля, лично я использую any
SELECT * from tbl WHERE '1.jpg' = ANY imgs;
И вот ещё одна полезная функция Postgresql unnest
Таким образом можно привести запрос к привычному мне виду
Select * from tbl WHERE '1.jpg' in unnest(imgs)
Запишу сюда же и то, как сделать обратное преобразование Есть в постгресе агрегатные функции array_agg(выражение)
Если в таблицы с одним ключом есть несколько записей, то эти записи можно выбрать как одно поле как массив, со значениями этих строк.... Не буду повторять тут этот пост
И полезная php функция
/**
* Преобразование формат массива PostgreSql в масив PHP
* Например, {1,2,3} в array(1,2,3);
* @param $pgArray
* @return array
*/
function PGArrayToPHPArray($pgArray)
{
$ret = array();
$stack = array(&$ret);
$pgArray = substr($pgArray, 1, -1);
$pgElements = explode(",", $pgArray);
//ArrayDump($pgElements);
foreach($pgElements as $elem)
{
if(substr($elem,-1) == "}")
{
$elem = substr($elem,0,-1);
$newSub = array();
while(substr($elem,0,1) != "{")
{
$newSub[] = $elem;
$elem = array_pop($ret);
}
$newSub[] = substr($elem,1);
$ret[] = array_reverse($newSub);
}
else
$ret[] = $elem;
}
return $ret;
}
Можно попробовать навесить GIN индекс(эффективен на select но не update insert),или GIST(эффективен если маленькие таблицы). Или использовать "широкие" таблицы
ОтветитьУдалитьот 200 полей. Есть еще hstore(хэши, не для поиска) можно в одно поле поместить все, что нужно отобразить в документе и пользовать.
Можно короче и быстрее:
ОтветитьУдалитьfunction array_sql_to_php ( $str_sql )
{
$str_sql = str_replace ("{", "", $str_sql);
$str_sql = str_replace ("}", "", $str_sql);
$array = explode( ",", $str_sql );
return $array;
}
ну.. для частных случаев - да
УдалитьЧто будет, если массив будет таким в постгресе
{'str','str with symbol }',' unomas }'}
На выходе получится array('str','str with symbol ',' unomas ')
Хотя верно то array('str','str with symbol }',' unomas }')
Да согласен, для вложенных массивов не подойдет.
Удалитьне то чтобы для вложенных.. не пойдет, если в значении массива будет фигурная скобка :)
УдалитьПодскажите Ольга, сталкивались ли Вы с задачей поиска по элементам массива, если да, то как это реализовывали?
ОтветитьУдалитьДополнила пост примером селекта
УдалитьSELECT * from tbl WHERE '1.jpg' = ANY imgs;
тут (http://www.postgresql.org/docs/9.1/static/arrays.html) подробно написано в разделе 8.14.5. Searching in Arrays
И если вы используете поля типа массив, советую полистать информацию из и этой статьи http://postgresql.ru.net/gruber/ch13.html
Премного благодарен!
Удалить