Помогите составить запрос

0

1

есть следующие таблицы

mysql> describe properties;
+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| id                   | int(11)      | NO   | PRI | NULL    | auto_increment |
| agent_id             | int(11)      | YES  | MUL | NULL    |                |
...
| created_at           | datetime     | NO   |     | NULL    |                |
| updated_at           | datetime     | NO   |     | NULL    |                |
+----------------------+--------------+------+-----+---------+----------------+

mysql> describe property_owners;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| property_id | int(11)  | YES  | MUL | NULL    |                |
| owner_id    | int(11)  | YES  | MUL | NULL    |                |
| created_at  | datetime | NO   |     | NULL    |                |
| updated_at  | datetime | NO   |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

примечание: owner_id здесь это fk на следующею таблицу

mysql> describe people;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| name           | varchar(255) | YES  |     | NULL    |                |
| notes          | text         | YES  |     | NULL    |                |
| person_role_id | int(11)      | YES  | MUL | NULL    |                |
| created_at     | datetime     | NO   |     | NULL    |                |
| updated_at     | datetime     | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

mysql> describe phones;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| number     | varchar(255) | YES  |     | NULL    |                |
| person_id  | int(11)      | YES  | MUL | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Нужно одним запросом окромя всех данных с таблицы properties собирать данные (имя и телефоны) всех ее owner`ов.

вот пример (кусками) как я сделал для агента

        properties = Property.scoped.select(                                    
            <<-QUERY                                                            
                properties.*,                                                   
                ...
                people.name AS agent_name,                                      
                GROUP_CONCAT(phones.number SEPARATOR ', ') as agent_phone,      
            QUERY                                                               
        )                                                                       
        ...
        .joins('LEFT  JOIN people ON properties.agent_id = people.id')          
        .joins('LEFT  JOIN phones ON people.id = phones.person_id')             
        .group('phones.person_id')
        ...                                              
        .order('created_at DESC')

Вся сложность для меня в том что owner`ов можеть быть несколько и у каждого может быть разное количество телефонов (или даже его не быть)

было бы неплохо получить данные о owner`ах как 1 отдельный столбик в таком виде

mary - 123456, 234567; john - 0987654; robert - 112233, 223344, 334455

Ссылка

←	расписание для встреч людей

QChildEvent и способы обмануть плюсовый рантайм

→

Постановка нереляционная.

Пиши хранимые функции вида get_phones(owner_id) returns varchar, и цепляй их из запроса.

P.S.: структура базы г..но. Для «плывущих во времени» значений рекомендуют использовать пары атрибутов { дата_начала_действия_занчения, дата_окончания_действия_значения }. И всё равно задача по-прежнему нереляционная

~~no-dashi~~ ★★★★★
(11.03.13 10:10:34 MSK)

Ответ на: комментарий от no-dashi 11.03.13 10:10:34 MSK

ЯННП

здесь нет никаких

«плывущих во времени» значений

created_at & updated_at это фишка рельсов. для решения поставленной задачи они никакой роли не играют.

ZuBB ★★★★★
(11.03.13 12:16:51 MSK) автор топика

Ссылка

Я бы на твоем месте поменял бы структуру БД-шки, заменив таблицу phones на колонку phones в people и добавив:

class Person < ActiveRecord::Base
  serialize :phones, Array
end

т.к. телефоны это всё-таки часть информации о person'е.

А в запросе можно попробовать:

Property.includes(owner).select("properties.*, people.name ...")

или что-то вроде того (запрос не тестил, скажу честно).

В общем, ИМХО - нужно попроще со структурой БД и с запросами =)

NOX
(13.03.13 11:33:07 MSK)

Ответ на: комментарий от NOX 13.03.13 11:33:07 MSK

идею понял, очень здраво, спасибо :)

если вас не затруднит, не поделитесь ли ссылкой на пример (если таковая существует)?

ZuBB ★★★★★
(13.03.13 15:14:04 MSK) автор топика

Ответ на: комментарий от ZuBB 13.03.13 15:14:04 MSK

Пример чего? =)

NOX
(13.03.13 16:18:45 MSK)

Ссылка

Ответ на: комментарий от NOX 13.03.13 11:33:07 MSK

наконец то пришло время занятся этим вопросом. поэтому, если позволите, хотел бы еще раз вернутся к обсуждению вашего предолжения.

у вашего предложения я вижу следующие плюсы: -1 таблица, проще запросы к базе.

минус вижу только один: если мне будет нужно сделать поиск по телефонах, нужно будет писать костыли. (или возвращатся к теперешнему варианту)

мож я еще что-то упустил?

ИМХО - нужно попроще со структурой БД и с запросами

в целом я конечно согласен, но ±2 таблицы погоды не далают. а вот запросы уже мне не нравятся

SELECT properties.*,
       apartment_types.name AS apartment_type_name,
       building_materials.name AS building_material_name,
       districts.name AS district_name,
       price_types.name AS price_type_name,
       streets.name AS street_name,
       people.name AS agent_name,
       p.agent_phone,
       p3.owners AS owners_str,
       count(assets.id) AS photos
FROM `properties`
INNER JOIN `districts` ON `districts`.`id` = `properties`.`district_id`
INNER JOIN `streets` ON `streets`.`id` = `properties`.`street_id`
INNER JOIN `price_types` ON `price_types`.`id` = `properties`.`price_type_id`
LEFT JOIN apartment_types ON properties.apartment_type_id = apartment_types.id
LEFT JOIN building_materials ON properties.building_material_id = building_materials.id
LEFT JOIN people ON properties.agent_id = people.id
LEFT JOIN assets ON properties.id = assets.property_id
LEFT JOIN
  ( SELECT GROUP_CONCAT(p.number SEPARATOR ', ') AS agent_phone,
           person_id
   FROM phones p
   GROUP BY p.person_id ) p ON properties.agent_id = p.person_id
JOIN
  ( SELECT po.property_id,
           Group_concat(DISTINCT p2.person SEPARATOR '; ') AS owners
   FROM property_owners po
   JOIN
     ( SELECT po.property_id,
              Concat_ws(' - ', u2.name, u2.phones) AS person
      FROM property_owners po
      JOIN
        ( SELECT u.id,
                 u.name,
                 Group_concat(p.number SEPARATOR ', ') AS phones
         FROM people u
         LEFT JOIN phones p ON u.id = p.person_id
         GROUP BY p.person_id ) u2 ON po.owner_id = u2.id ) p2 ON po.property_id = p2.property_id
   GROUP BY po.property_id ) p3 ON properties.id = p3.property_id
WHERE (operation_type_id IN ('1'))
  AND (operation_status_id IN ('1'))
  AND (property_type_id IN ('1'))
GROUP BY properties.id
ORDER BY rooms ASC, apartment_type_id ASC, floor_num ASC, created_at DESC

поэтому я всеми руками за упрощение.

если есть годные советы - поделитесь пожалуйста. мож kelyar еще дельных советов настрогает

ZuBB ★★★★★
(04.04.13 17:58:51 MSK) автор топика

Ответ на: комментарий от ZuBB 04.04.13 17:58:51 MSK

В качестве решения для поиска по сериализованным данным могу навскидку предложить только PostgreSQL + HSTORE - https://github.com/engageis/activerecord-postgres-hstore

Еще один из вариантов упрощения DB - миграция на какой-нибудь NoSQL вроде MongoDB + Mongoid.

Еще можно поковырять код на предмет «откуда растут ноги у сложных запросов?». Если сложные запросы выполняют функцию поиска, это повод переходить на search engines (Solr + rails-sunspot), если бизнес-логика, то выносить её на application layer.

NOX
(05.04.13 12:03:28 MSK)

Ответ на: комментарий от NOX 05.04.13 12:03:28 MSK

спасибо за ответ

переход на любую другую базу пока не рассматриваю.

Еще можно поковырять код на предмет «откуда растут ноги у сложных запросов?». Если сложные запросы выполняют функцию поиска, это повод переходить на search engines (Solr + rails-sunspot), если бизнес-логика, то выносить её на application layer.

вообще то здесь у меня перемешалось и одно и другое. сам по себе поиск не сложный. лишнюю сложность как раз добавляет «бизнес-логика».

напомню

Нужно одним запросом окромя всех данных с таблицы properties собирать данные (имя и телефоны) всех ее owner`ов

нужность "всех данных с таблицы" (или не всех) как раз определяет поиск и его часть в запросе ничтожно мала

WHERE (operation_type_id IN ('1'))
  AND (operation_status_id IN ('1'))
  AND (property_type_id IN ('1'))

Сложность в том что вместе с обьектом нужно показать агента с его телефонами который может быть «прицеплен» к нему и owner`-а/-ов с ихними телефонами.

за это отвечает большой кусок

LEFT JOIN
  ( SELECT GROUP_CONCAT(p.number SEPARATOR ', ') AS agent_phone,
           person_id
   FROM phones p
   GROUP BY p.person_id ) p ON properties.agent_id = p.person_id
JOIN
  ( SELECT po.property_id,
           Group_concat(DISTINCT p2.person SEPARATOR '; ') AS owners
   FROM property_owners po
   JOIN
     ( SELECT po.property_id,
              Concat_ws(' - ', u2.name, u2.phones) AS person
      FROM property_owners po
      JOIN
        ( SELECT u.id,
                 u.name,
                 Group_concat(p.number SEPARATOR ', ') AS phones
         FROM people u
         LEFT JOIN phones p ON u.id = p.person_id
         GROUP BY p.person_id ) u2 ON po.owner_id = u2.id ) p2 ON po.property_id = p2.property_id
   GROUP BY po.property_id ) p3 ON properties.id = p3.property_id

пока весь этот код отрабатывает достаточно быстро (ActiveRecord: 0.2ms, на очень малом количестве данных). Но чисто визуально он мне не нравится и есть некое чувство что его можна улучшить. но пока не знаю как.

вот такое у меня «месиво» в коде. какие сейчас будут идеи?

ZuBB ★★★★★
(05.04.13 12:19:40 MSK) автор топика
Последнее исправление: ZuBB 05.04.13 12:21:12 MSK (всего исправлений: 2)

Ссылка

Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.

←	расписание для встреч людей

Development

QChildEvent и способы обмануть плюсовый рантайм

→

Похожие темы