LINUX.ORG.RU

SQL: many to many relationship joins


0

2
create table items (
  id bigserial primary key,
  name varchar(255) not null
);
create table categories (
  id bigserial primary key,
  name varchar(255) not null unique
);
create table items_categories (
  item_id bigint not null references items(id),
  category_id bigint not null references categories(id)
);

Такое выдаст только одну из возможных категорий item'а:

select items.*, items_categories.category_id from items inner join items_categories on items.id = items_categories.item_id

можно ли как то получить все категории в одном запросе или надо делать отдельный запрос для получения категорий?


Т.е. я хотел сказать что оно выдаёт дублирующиеся item строки для каждой категории к которой относится item. Как бы получить ввиде ARRAY или какой ещё лучше путь решения вопроса?

psp13 ()
Ответ на: комментарий от schizoid

А если их там миллионы? Вы предлагаете их все выбрать в память? Я конечно понимаю что сейчас модно плевать на память но не до такой же степени! И ORM мне ваш не нужен, этот велосипед, бесполезная надстройка над SQL. Я то имел ввиду оптимизировать так что бы вместо 101 запроса иметь 1 при выборе 100 items. В postgres есть ROW, ARRAY думал как может с ними можно сделать.

psp13 ()
Ответ на: комментарий от psp13

Вобщем то что я хотел можно сделать так:

[code=sql] SELECT items.*, ARRAY(select category_id from categories where categories.item_id = items.id) AS cats from items [/code]

Только PHP получает cats как строку, т.е. её надо парсить. Можно ли заставить интерфейс с DB возвращать вложенный массив?

psp13 ()
Ответ на: комментарий от psp13

В postgres есть ROW, ARRAY думал как может с ними можно сделать.

В ОП-е нет ни слова про postgres. 'SQL' == 'ANSI SQL'.

А если их там миллионы?

Зачем тебе запрос в принципе на миллионы айтемов _единомоментно_?

Вы предлагаете их все выбрать в память?

Курсоры.

Я то имел ввиду оптимизировать так что бы вместо 101 запроса иметь 1 при выборе 100 items.

Делай два вместо 101. И вместо 1001 тоже будет два. Ну ты понел. И не забудь про ORDER BY.

schizoid ★★★ ()
Ответ на: комментарий от schizoid

Можно пример про курсоры? А то что то из документации не совсем ясно. Как переписать выше приведённый запрос с курсорами? Потому что коммент про парсинг массива в php я уже видел но мне кажется это некрасиво как то и скорее всего не эффективно.

psp13 ()
Ответ на: комментарий от psp13

Можно пример про курсоры? А то что то из документации не совсем ясно.

ORM это ненужный костыль, только молодость, только чистый SQL! Только вот, парни, а что и как мне с ним делать?

jessey ()
Ответ на: комментарий от psp13

А если их там миллионы? Вы предлагаете их все выбрать в память?

Зачем в память? В цикле обрабатывай, как тебе угодно. Если не надо всё - не выбирай всё в память.

Legioner ★★★★★ ()
Ответ на: комментарий от psp13

Вобще надо показать список items и для каждой items показать categories.

Самый простой вариант вообще:

$items = $db->fetchRows(
"SELECT * ".
"FROM items INNER JOIN items_categories ON items_categories.item_id = items.id ".
"WHERE items_categories.category_id = :cat_id: ".
"ORDER by items.name ASC, items.id ASC ".
"LIMIT :limit: OFFSET :offset:",
array(
  'cat_id' => $_GET['cat_id'],
  'limit' => $itemsPerPage,
  'offset' => ($page - 1) * $itemsPerPage
)
);
foreach ($items as $key => $item) {
  $items[$key]['categories'] = $db->fetchRows(
    "SELECT categories.id, categories.name ".
    "FROM items_categories LEFT OUTER JOIN categories ".
    "ON items_categories.category_id = categories.id ".
    "WHERE items_categories.item_id = :item_id:",
    array('item_id' => $item['id'])
  );
}

Можно ли как то оптимизировать в один запрос курсорами или ещё чем?

psp13 ()
Ответ на: комментарий от jessey

ORM это ненужный костыль, только молодость, только чистый SQL! Только вот, парни, а что и как мне с ним делать?

Какая ORM библиотека сможет это оптимизировать? Если правильно помню ActiveRecord так и слала запросы в цикле. Сейчас проверю ещё раз.

psp13 ()
Ответ на: комментарий от psp13

Хотел щас посмотреть как это делает ActiveRecord но похоже забыл как ей пользоваться:

irb(main):034:0> i = Items.first
  Items Load (0.4ms)  SELECT "items".* FROM "items" LIMIT 1
=> #<Items id: 1, name: "item #1", created_at: "2012-03-08 16:27:32", updated_at: "2012-03-08 16:27:32">
irb(main):035:0> i.categories
NameError: uninitialized constant Items::Category
        from /usr/local/lib64/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/base.rb:1341:in `compute_type'
        from /usr/local/lib64/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/reflection.rb:173:in `send'
        from /usr/local/lib64/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/reflection.rb:173:in `klass'
        from /usr/local/lib64/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/associations/association.rb:118:in `klass'
        from /usr/local/lib64/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/associations/association.rb:166:in `find_target?'
        from /usr/local/lib64/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/associations/collection_association.rb:327:in `load_target'
        from /usr/local/lib64/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/associations/collection_proxy.rb:51:in `__send__'
        from /usr/local/lib64/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/associations/collection_proxy.rb:51:in `load_target'
        from /usr/local/lib64/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/associations/collection_proxy.rb:89:in `method_missing'
        from /usr/lib64/ruby/1.8/irb.rb:310:in `output_value'
        from /usr/lib64/ruby/1.8/irb.rb:159:in `eval_input'
        from /usr/lib64/ruby/1.8/irb.rb:271:in `signal_status'
        from /usr/lib64/ruby/1.8/irb.rb:155:in `eval_input'
        from /usr/lib64/ruby/1.8/irb.rb:154:in `eval_input'
        from /usr/lib64/ruby/1.8/irb.rb:71:in `start'
        from /usr/lib64/ruby/1.8/irb.rb:70:in `catch'
        from /usr/lib64/ruby/1.8/irb.rb:70:in `start'
        from /usr/local/lib64/ruby/gems/1.8/gems/railties-3.1.3/lib/rails/commands/console.rb:45:in `start'
        from /usr/local/lib64/ruby/gems/1.8/gems/railties-3.1.3/lib/rails/commands/console.rb:8:in `start'
        from /usr/local/lib64/ruby/gems/1.8/gems/railties-3.1.3/lib/rails/commands.rb:40
        from script/rails:6:in `require'
        from script/rails:6irb(main):036:0>

Что не так? Или она может только one-to-many relationship а в остальных случаях find_by_sql?

psp13 ()
Ответ на: комментарий от psp13

Тогда делать запрос наоборот. Выбирать все категории и потом уже в этот запрос подставлять значения из items подзапросом.

xpahos ★★★★★ ()
Ответ на: комментарий от psp13

Может тогда сделать «WHERE items_categories.item_id IN (id1, id2, ..., id100)». И потом раскладывать по массивам $item['categories'] в соответствии со значением project_id? Или эффективнее сделать это в stored function?

psp13 ()
Ответ на: комментарий от xpahos

Тогда делать запрос наоборот. Выбирать все категории и потом уже в этот запрос подставлять значения из items подзапросом.

Не понял, можно подробнее? Если сделать так:

select categories.*, ARRAY(select * from items_categories where items_categories.category_id = categories.id) from categories

То опять же ввиде строки которую надо парсить возвратится ARRAY.

psp13 ()
Ответ на: комментарий от xpahos

Да это понятно. Проблема в интерфейсе из PHP. Например

$q = pg_query("SELECT items.*, ARRAY(select category_id from categories where categories.item_id = items.id) AS cats from items");
$row = pg_fetch_assoc($q);

в $row['cats'] будет строка вида «{1,2,3}» вместо вложенного массива. Её надо парсить, а это неэффективно и error-prone.

psp13 ()
Ответ на: комментарий от psp13

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

xpahos ★★★★★ ()
Ответ на: комментарий от psp13

Вобщем результат не утешительный. Для этой страницы ab показывает 24 req/second в то время как к самой сложной не считая этой около 350 req/sec. Как же оптимизировать? Может создать в items поле cats cat_type[] т.е. массив и тригером туда писать при добавлении в items_categories? Если всё так плохо с 3 существующими записями что же будет в продакшене?

psp13 ()
Ответ на: комментарий от psp13

Хотя не так страшно, перепутал. Просто запускал ab с другой машины и такое резкое падение производительности было из за медленной сети. Сейчас получилось около 250 req/sec. Однако неизвестно что будет при больших объёмах данных, завтра буду тестировать - заполню рандомно пару сотен тысяч записей.

psp13 ()
Ответ на: комментарий от xpahos

судя по описанию/отзывам jmeter может точнее сэмулировать нагрузку

psp13 ()
Ответ на: комментарий от psp13

этот велосипед, бесполезная надстройка над SQL.

NameError: uninitialized constant Items::Category

Что не так?

разобрался бы с начала, прежде чем ругать.
у тебя модели неправильно описаны - класс категории оно ищет внутри класса Items (и модель надо было назвать Item)

kelyar ★★★★★ ()

Объяснять лень, кину код.


class Post(models.Model):
    tags = models.ManyToManyField("Tag")

class Tag(models.Model):
    name = models.CharField(max_length=42, unique=True)

...

post_queryset = list(Post.objects.filter())
post_tags = Post.tags.through.objects.filter(
        post__in=post_queryset).select_related('tag')

post_tags_dict = {}
for pt in post_tags:
    post_pk = pt.post_id
    if post_pk not in post_tags_dict:
        post_tags_dict[post_pk] = []
    post_tags_dict[post_pk].append(pt.tag)

for post in post_queryset:
    post.post_tags = post_tags_dict.get(post.pk, ())


В итоге в 2 запроса собираем m2m.
Apkawa ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.