Ответы пользователя по тегу SQL
  • Как правильно организовать ссылку на значение в связанной таблице?

    @alexalexes
    Нет смысла. Вы не для красоты в таблице Book завели свойство author_id, чтобы связывать по нему строку из таблицы Author, тем самым получать доступ ко всем свойствам по идентификатору author_id.
    PS: А вы уверены что у книжки строго один автор? Или это для вас просто учебный пример?
    Ответ написан
  • Как сделать, чтобы неподходящие под условие не забивали лимит?

    @alexalexes
    Вариант А.
    Вы не можете гарантировать, что любой файл изображений, сведения о которых храните в базе, будет доступен физически в любой момент.
    Придется неопределенное число раз постучаться в базу и протестировать каждую запись о файле, существует ли он физически. При тесте формируем список мертвых и живых файлов. После того, как протестировали нужное кол-во живых файлов, можно делать итоговый запрос с оглядкой на список мертвых файлов.
    $need_count = 10;  // сколько требуется файлов для выборки
    $alive_count  = 0;  // сколько живых файлов
    $is_need_repeat = true; // требуется повторить попытку получить живые файлы
    $death_list = []; // сюда накапливаем список id мертвых файлов
    $alive_list = []; // сюда накапливаем список id живых файлов
    while($is_need_repeat) // Если можно делать итерационную попытку и пока не набрали нужное количество живых файлов
    {
    // Этот запрос, чтобы прощупать целостность файлов, достаточно получить только те атрибуты, которые позволяют проверить его путь и запомнить id.
    $database->setQuery("
        SELECT id
        from блаблабла
        WHERE блаблабла 
                   ".(count($depth_list) > 0 ? : ' and id not in ('.join(',',$death_list).') ' : '')." -- отсеиваем мертвые файлы из запроса, они нам не нужны
                   ".(count($alive_list) > 0 ? : ' and id not in ('.join(',',$alive_list).') ' : '')." -- отсеиваем живые файлы из запроса, мы их уже проверяли
        ORDER блаблабла
    limit 0,".($need_count - $alive_count)); // делаем лимит по оптимистичному сценарию, как будто можем получить список файлов, и все они будут живые, но только то кол-во, которое недостает
    while($row = mysql_fetch_assoc($request))
    {
      if(file_exists('/www/ПУТЬ/'.$row['id'].'_100.jpg'))
      {
        $alive_list[] = $row['id']; // файл живой, заносим его id в список
      }
      else
      {
        $death_list[] = $row['id']; // файл мертвый, заносим его id в список
      }
      $curr_alive_count = count($alive_list);
      $is_need_repeat = $curr_alive_count > 0 && $curr_alive_count > $alive_count && $curr_alive_count < $need_count; // необходимо продолжить попытки, если на текущей итерации получили хоть один живой файл, живых файлов на этой итерации оказалось больше, чем на предыдущей, и их кол-во не достаточно до необходимого
      $alive_count = $curr_alive_count; // вписываем кол-во живых файлов на текущей итерации для проверки в будущем цикле (чтобы сравнить результаты двух циклов)
    }
    }
    // теперь можно сделать нормальный запрос, исключив мертвые файлы:
    $database->setQuery("
        SELECT *
        from блаблабла
        WHERE блаблабла 
                   ".(count($depth_list) > 0 ? : ' and id not in ('.join(',',$death_list).') ' : '')." -- отсеиваем мертвые файлы из запроса
        ORDER блаблабла
    limit 0,".$need_count);

    Вариант Б.
    Вы можете гарантировать, что контролируете целостность файлов.
    Тогда в таблице изображений делаете колонку is_del. Когда удаляете файл, вы должны пометить запись о файле в базе как удаленную по этому атрибуту.
    Если вы все таки частично контролируете целостность, то в определенный период времени (например, запускать скрипт по cron раз в час, сутки) вам нужно пройтись по всему списку файлов в базе и проверить целостность каждого файла, и внести актуальную метку is_del.
    Тогда получать живые файлы будет чуть-чуть проще:
    $database->setQuery("
        SELECT *
        from блаблабла
        WHERE блаблабла 
               and is_del is null -- или нулю, в зависимости, что будет по умолчанию
        ORDER блаблабла
    limit 0,".$need_count);
    Ответ написан
    4 комментария
  • Как сделать поиск в двух таблицах?

    @alexalexes
    т.е. если в таблице wp_reviews запись с company_id=2001 не существует, то для таблицы wp_posts для строки с ID=2001 обновляем post_status на 'draft'

    Как бы, у вас уже на 80% запрос сформирован на естественном языке пригодный для прямого транслирования в SQL. Как-то стыдно не записать его прямым текстом:
    update wp_posts p
    set p.post_status = 'draft'
    where not exists (select 1 from wp_reviews r where r.связующий_ключ = p.связующий_ключ)

    Осталось определиться, как называются ключи в той и другой таблице, связывающие данные этих таблиц.
    PS: вероятно это: r.company_id = p.id.
    Ответ написан
  • Как сделать сортировку?

    @alexalexes
    Добавьте еще два параметра в ту часть запроса, которая отвечает за сортировку.
    order_column - по какой колонке сортировать, order_direct - в каком направлении.
    Пример с limit намекает, как это сделать.
    $limit = 'limit 0, 500';
    $order_column = 'added';
    $order_direct = 'desc';
    if(isset($_GET['order_col']))
    {
      if($_GET['order_col'] == 'updated')
        $order_column = 'updated'; // не вздумайте подставлять из GET название колонки, будет sql-инъекция!
    }
    if(isset($_GET['order_dir']))
    {
      if($_GET['order_dir'] == 'asc')
        $order_direct = 'asc'; // не вздумайте подставлять из GET название клаузы, будет sql-инъекция!  
    }
            if (isset($_GET['ajax2'])) $limit = 'limit '.(int)$_GET['offset'].', 30'; // тут застраховано от инъекции при помощи преобразования в int!
    
            $rows = fs::getObjects($sql, "order by c_object.".$order_column." ".$order_direct." ".$limit);
            $rowsCount = count(fs::getObjects($sql));
    
            if (isset($_GET['ajax2']) && !count($rows))
            {
              header("HTTP/1.0 404 Not Found");
              die;
            }

    ПС: Осталось вам дополнить вопрос, "а где мне найти шаблон формы с фильтром списка, чтобы прописать get-параметры order_col и order_dir".
    Ответ написан
    Комментировать
  • Как переделать запрос для получения нужного ответа?

    @alexalexes
    Как-то, так.
    SELECT
    city.id,
    city.name,
    country.name,
    salary,
    country.is_here
    FROM city
    LEFT JOIN country ON city.cid = country.id
    union all
    SELECT
    city.id,
    city.name,
    country.name,
    salary + 2000,
    1
    FROM city
    LEFT JOIN country ON city.cid = country.id
    where (country.id is null or country.is_here = 0)
    Ответ написан
    Комментировать
  • Как создать паттерн для REGEXP_LIKE для двух значений?

    @alexalexes
    Если в СУБД вы видите функции для работы с регулярными выражениями, то это еще не значит, что реализована вся их мощь.
    Например, для вашего случая, чтобы реализовать AND, нужно, чтобы исполнитель регулярок мог обрабатывать ретроспективные или опережающие проверки.
    За функциями Oracle такого функционала не замечено, в силу того, что эта СУБД не для полнотекстового поиска, а инструмент для быстрой работы с хорошо структурированными данными.
    Ковыряние в фрагментах текста - не конек реляционных СУБД, таким системам индексируемые данные подавай.
    Поэтому функционал регулярных функций ограничен, реализованы чуть лучше чем like.
    Ответ написан
    Комментировать
  • Когда нужно писать имя таблицы в запросе?

    @alexalexes
    Всегда обзывайте каждую таблицу в запросе кратким именем, и это краткое имя используйте в именовании колонок, тогда ни при каких условиях проблем с конфликтом имен не возникнет.
    SELECT t1.ProductCode, t2.Name
    FROM table_one as t1 -- t1 - краткий псевдоним таблицы table_one в пределах select-а
    LEFT OUTER JOIN table_two as t2 -- t2 - краткий псевдоним таблицы table_two в пределах select-а
    ON  t2.id = t1.id_client;
    Ответ написан
  • Создания уникального чата: sql, node, socket.io?

    @alexalexes
    Есть роль А И B. Только роль A может начать чат с ролью B

    Роли у пользователей заданы на уровне учетной записи и не меняются от чата к чату или определяются в момент создания чата?
    То есть при создании чата будут определена роль администратора (создателя) которая будет более привилегирована, чем тот, кому он пишет?
    Чат может содержать только 2х лиц

    Вы очень оптимистичны, выставляя такое ограничение. В следующей итерации разработки вам захочется сделать функционал группового чата. Тогда наличие колонок user_id, second_user_id вам встанет боком.
    В таблицу chat лучше добавить такие колонки:
    id - идент. чата
    date_create - дата создания
    id_user_creator - кто создал чат
    title - название чата (в этой итерации разработки можно не вставлять)
    Для участников чата лучше предусмотреть таблицу chat_participant:
    id - идент. участника
    id_chat - идент. чата из таблицы chat
    id_role - роль в чате (если она определяется в момент создания, для группового чата)
    id_user - пользователь чата
    id_last_read_message - идент. последнего прочитанного сообщения (самый простой вариант, как отмечать что прочитано, и потом определять, есть ли новые сообщения)
    date_include - дата вступления в чат (для группового чата)
    date_exclude - дата исключения из чата (для группового чата)
    Для сообщений чата - таблица chat_message:
    id - id сообщения
    id_partic/id_user - автор сообщения (можно реализовать как по ключу от таблицы участника, так и по таблице пользователей)
    date_create - дата создания сообщения
    date_update - дата обновления сообщения (для продвинутого функционала редактирования сообщений)
    date_delete - дата удаления сообщения (для продвинутого функционала редактирования сообщений)
    text_message - текст сообщения
    Если совсем хотите быть продвинуты в функционале редактирования сообщений, то вы захотите хранить историю изменения сообщений в таблице chat_message_history:
    id - идент. истории
    id_next - указатель на следующую запись истории
    id_message - идент. сообщения
    date_change - дата изменения сообщения
    id_user/id_partic - кто изменил
    text_message - состояние текста сообщения
    Ответ написан
    Комментировать
  • Можно ли удалить некоторый список товаров вместе с изображениями из интернет-магазина на Wordpress через SQL?

    @alexalexes
    Можно. Если файлы изображений хранятся в файловой системе сервера, то составляете запрос, который получает ftp-пути к каждому файлу, по id конкретного товара. Затем составляете запрос-выборку по id товарам, которые нужно удалить.
    1. Запускаете запрос, получающий выборку id товаров.
    2. Выбираете одну строку запроса шага 1.
    3. Запускаете запрос, получающий выборку ftp путей изображений.
    4. Получаете одну строку из запроса шага 3.
    5. Удаляете файл из файловой системы сервера.
    6. Идем на шаг 4, пока не кончится выборка изображений.
    7. Помечаем товар как удаленный (запрос на обновление).
    8. Идем на шаг 2, пока не кончится выборка товаров для удаления.
    Конец.
    Ответ написан
    Комментировать
  • Схлопывание одинаковых последовательных символов строки в один как пробелы в HTML?

    @alexalexes
    select string_agg(c.letter, '') as result
    from (select b.letter, lag(b.letter) over (order by rownum) letter_before
            from (select row_number() over () as rownum, letter
                    from (select unnest(string_to_array('vvvvvaassssyyyaaaaaaa', null)) as letter) as a
                  ) as b
          ) as c
    where c.letter_before is null or c.letter <> c.letter_before

    Можно сделать функцию получения уникальной последовательности символов без повторения в SQL.
    Запрос имеет следующие шаги:
    a) Получение выборки строк посимвольным разбиением слова.
    b) Выборка b содержит нумерацию символов rownum.
    c) С помощью функции lag и колонки rownum (для выбора направления сортировки) получаем предыдущий символ слова в текущей строке.
    d) Выбираем только те строки, которые содержат разные предыдущие символы и собираем символы обратно в строку result.
    Усовершенствованный вариант:
    select d.user_id, string_agg(d.letter, '') as result
    from (select c.*, case when c.letter_before is null or c.letter_after is null or c.letter <> c.letter_before or c.letter <> c.letter_after then 1 else 0 end marker 
    from
    (select b.user_id, b.rownum, b.letter, lag(b.letter) over (partition by b.user_id order by b.rownum) letter_before, lead(b.letter) over (partition by b.user_id order by b.rownum) letter_after
            from (select a.user_id, row_number() over (partition by a.user_id) as rownum, a.letter
                    from (select 1 user_id, unnest(string_to_array('vasyyyaaaaaa', null)) as letter
                          union all
                          select 2 user_id, unnest(string_to_array('pettyaaaa', null)) as letter
                         ) as a
                  ) as b
     ) c
    order by c.user_id, c.rownum) d
    where d.marker = 1
    group by d.user_id

    a) Содержит 2 пользователя.
    b) Добавлено слежение за передним символом lead.
    с) marker дает 1 в случае "головы" или "хвоста" одинаковой последовательности символов.
    d) из последовательности символов достается только голова или хвост последовательности (при наличии), что дает ограничение <3 повторений.
    На всех уровнях использование оконных функций позволяет получать значения функций в пределах user_id.
    PS: Данное решение демонстрирует, что можно работать в пределах стандартного синтаксиса SQL без использования хранимых процедур (с финтами ушами в виде оконных функций), но это не значит, что решение оптимально, возможно, стоит написать хранимку, с использованием классических циклов с курсорами по разбивке имени пользователя на символы.
    Ответ написан
    4 комментария
  • Перенос таблиц из одной бд в другую в postgresql?

    @alexalexes
    В общем случае, вам нужно подучить, чем отличаются
    а) запросы, которые модифицируют архитектуру базы (Create table, Alter Table, Create index и т.д.)
    б) от запросов, которые работают с данными текущей архитектуры (SELECT, INSERT, UPDATE, DELETE).

    Сделав экспорт базы в формате sql, вы обнаружите, что дамп состоит из запросов, которые сначала создают или модифицируют архитектуру базы, а зачем, идут запросы, которые вставляют данные.

    Например в db1 есть table_user с сотнями данных, а в db2 его нет. Нужно, чтобы table_user перешел в db2, но без данных, нужно чтобы она просто создалась.


    Для этой задачи достаточно взять дамп db1 базы или даже отдельной таблицы table_user, и выполнить только те запросы из дампа, которые не касаются вставки данных в table_user, и других таблиц объектов схемы.

    И второй вариант событий: в db1 и db2 есть table_user, но у db1 table_user появилось 20 новых колонок, как их передать в более легком виде db2 table_user?

    Тут посложней.
    Изучайте alter table add column и применяйте его для недостающих колонок. Не забудьте из дампа взять запросы, которые создают или модифицируют другие объекты, связанные с новыми колонками.
    Ответ написан
  • DELETE FROM list WHERE id = 'id строки в списке дел'. Как мне указать этот id?

    @alexalexes
    1. Подготовить текст запроса prepare-функцией.
    2. Присобачить нужные параметры запроса bind-функцией.
    3. Выполнить запрос execute-функцией.
    Запрос не выполняется? Посмотреть ошибки.
    PS: В любой непонятной ситуации читай документацию.
    Ответ написан
    Комментировать
  • Как объединить GROUP BY и JOIN?

    @alexalexes
    Чтобы найти читателей с последними взятыми книгами, нужно при помощи оконной функции row_number пронумеровать строки в пределах каждого читателя (задав партицию по айди читателя) в порядке убывания по дате taken_at. Из полученного результата взять строки, которые получили номера 1.
    select * from
    (select r.id as reader_id, r.first_name, r.last_name, b.id book_id, b.name, b.pub_date, row_number() over (partition by r.id order by l.taken_at desc) as rownum
    from readers r
    left join log_taking l on r.id = l.reader_id
    left join books b on b.id = l.book_id
    ) a
    where a.rownum = 1

    PS: Связка left join выведет всех читателей, в независимости, брали они книги или нет. Просто join даст список читателей, которые брали книги хоть один раз.
    PPS: Оконные функции доступны с MySQL версии 8.
    Если версия 5, то придется городить велосипед с пользовательскими переменными.
    Ответ написан
    4 комментария
  • Как скопировать данные из 2 таблиц в одну?

    @alexalexes
    Правильно построить выборку из нужных таблиц.
    INSERT into archive (title_product, title_category, price_product) 
    SELECT p.title, c.title, p.price
      from product p
       join category c on p.category_id = c.id
    Ответ написан
    1 комментарий
  • Как найти дни рождения в json наборе?

    @alexalexes
    Лучше детей писать в отдельную таблицу.
    А если очень хочется, то только так колхозить:
    -- MySQL
    select u.user_id, child_bdays.bday
      from user u,
            JSON_TABLE(u.children, '$[*]' COLUMNS (
                    bday VARCHAR(10)  PATH '$'
             )) child_bdays
           where STR_TO_DATE(child_bdays.bday, "%Y-%m-%d") >= DATE(now() - INTERVAL 7 DAY)
             and STR_TO_DATE(child_bdays.bday, "%Y-%m-%d") <= DATE(now() + INTERVAL 7 DAY);
    
    -- MariaDB
    select u.user_id, JSON_EXTRACT(u.children, concat('$[', idx_table.idx, ']')) as bday
      from user u,
         (select 0 idx union select 1 union select 2 union
          select 3 union select 4 union select 5 union
          select 6 union select 7 union select 8 union
          select 9 union select 10 union select 11) idx_table /* впомогательная выборка для генерации индексов в диапазоне 0...11 */
      where idx_table.idx < json_length(u.children) /* отсекаем обращения к несуществующим индексам элементов в JSON*/
        and STR_TO_DATE(JSON_EXTRACT(u.children, concat('$[', idx_table.idx, ']')), "%Y-%m-%d") >= DATE(now() - INTERVAL 7 DAY)
        and STR_TO_DATE(JSON_EXTRACT(u.children, concat('$[', idx_table.idx, ']')), "%Y-%m-%d") <= DATE(now() + INTERVAL 7 DAY)
    Ответ написан
  • SQL View против отдельных запросов с JOIN's?

    @alexalexes
    SQL View применяется в первую очередь, если некоторым пользователем нужно предоставить сводные сведения из закрытых для них таблиц, исключив доступ ко всем колонкам таблицы, либо предоставить только агрегированные сведения (count-ы, суммы, средние) и тд, не предоставляя доступ к исходной выборке.
    Делегировать сводные данные другому пользователю, но не давать исходники данных - это как раз про представление.
    В моем понимании, только в одном случае представление может поднять производительность. Если в выборке будут данные из редко изменяемых таблиц или колонок, а СУБД умеет отдельно кэшировать представление и имеется возможность настроить кэш, тогда можно сделать быстрые источники данных на представлениях, основанных на редко изменяемых таблицах. Самый яркий пример - получение древовидной структуры организации со сведениями о начальниках подразделений. Такой запрос отягощен рекурсивностью, а данные редко изменяются. Можно из такого запроса сделать вью и правильно закэшировать. И использовать структуру организации в других запросах станет проще, однотипно, и возможно, более производительнее.
    Ответ написан
    Комментировать
  • Как сделать запрос к самой базе данных (SQL)?

    @alexalexes
    MySQL. Если хоть раз открывали phpmyadmin и видели в списке схем information_schema. То это оно, что вы ищите. В этой схеме лежит весь конструктив базы. Туда и нужно обращаться, чтобы вытащить сведения об архитектуре.
    Select distinct table_schema, table_name
    from information_schema.colums
    where column_name = 'имя поля'

    Oracle. Тоже есть некоторые "супер-таблицы" со сведениями о структуре:
    SELECT table_name, column_name, data_type, data_length
    FROM USER_TAB_COLUMNS
    WHERE column_name = 'имя поля'

    Под каждую СУБД есть свои источники-таблицы структуры самой базы, но реализованы они по разному.
    Ответ написан
    Комментировать
  • Как заполнить столбец с foreign key?

    @alexalexes
    Это делается апдейтом с подзапросами.
    update table tb
          set tb.type_id = (select tp.id from types tp where tp.name = tb.type_name limit 1) -- выбираем подзапросом id из справочника по совпадению наименования поля в обновляемой таблице
     where tb.type_id is null -- страховка, что будем обновлять не установленные значения
         and exists(select 1 from types tp where tp.name = tb.type_name) -- будем ставить ключ, если есть наименование в справочнике types

    PS: Прежде чем выполнять update, убедитесь при помощи select с тем же where, что вы вставите нужные для вас значения в поля секции set. По принципу поговорки: "Семь раз select-ни - один раз update-ни".
    PPS: Желательно, избегать ситуаций, когда вам периодически нужно нормализовывать структуру данных, в идеале, при insert-е нужно сразу определять ключик, а поле наименования использовать только тогда, когда нужного наименования нет в справочнике.
    Ответ написан
  • Как оптимально сделать таблицу умножения?

    @alexalexes
    Вот решение на Oracle.
    Достоинства:
    1. Подобрано максимально короткое описание вспомогательной выборки factor от 1 до 10 без перечисления ее элементов или обращения к другим таблицам.
    2. Поскольку нужно получить декартово произведение вспомогательной выборки, и не хочется повторять ее описание, то используем with.
    3. Не нравится писать слово join? У декартового пересечения нет условий, значит с соединением таблиц справится обычная запятая.
    Недостатки:
    1. СУБД должна мочь в рекурсивные запросы ( ... connect by ... ).
    2. СУБД должна мочь в выносимые запросы (with ...).
    3. Скорость рекурсии несколько ниже чем просто перебор описания 10 строк.

    with factor as (select level as lvl from dual connect by level < 10)
    
    select to_char(f1.lvl) || 'x' || to_char(f2.lvl) || '=' || to_char(f1.lvl * f2.lvl) as multi
    from factor f1,
        factor f2
    Ответ написан
    Комментировать
  • Как в Oracle или Teradata разбить все строки таблицы на N равновеликие СЛУЧАЙНЫЕ выборки?

    @alexalexes
    Практически методом Монте-Карло помечаем числами из случайного интервала записи таблицы и берем интересуемую порцию по этой случайной метрике. Поскольку, рандом у нас по равномерному закону распределения работает, то и порцию данных вы получите примерно ожидаемой длины.
    select *
    from (
    select t.*, dbms_random.value(0, 100) rnd
      from table t
    ) A
    where A.rnd <= 30 -- выбираем примерно 30% случайных записей от ген. выборки
    Ответ написан