Получить элементы массива не существующие в таблице

0

1

1) Есть таблица пользователей, в которой, к примеру, только 1 поле «username».
2) Имеется массив с именами пользователей ['user1', 'user2', 'user3', ...].

Нужно с помощью sql запроса получить список имен пользователей из массива, которых нет в таблице, причём, имена должны быть не чувствительны к регистру. Я кое-что наговнокодил, интересует, может есть иной способ сделать это, как-нибудь, по-красивей?

Ссылка

←	Разделение сборки

Почему драйверы и прочую системщину пишут, в основном, на C, а не на C++?

→

Забить во временную таблицу, связать (left join), смотреть, где null. Все имена перевести в ucase/lcase.

crutch_master ★★★★★
(22.01.15 12:30:15 MSK)

Ссылка

Если можно сделать без left join надо делать без left join oracle

SQL> create table users (username varchar(255));

Table created.

SQL> insert into users(username) values ('user1');

1 row created.

SQL> insert into users(username) values ('user2');

1 row created.

SQL> select name from (select 'user1' as name from dual union select 'user3' from dual) where name not in(select username from users);

NAME
-----
user3

vtVitus ★★★★★
(22.01.15 13:06:33 MSK)

Ответ на: комментарий от vtVitus 22.01.15 13:06:33 MSK

На mysql такой вариант будет очень сильно тормозить, но для pg может и сойдет.

crutch_master ★★★★★
(22.01.15 13:42:04 MSK)

MINUS, например.

anonymous
(22.01.15 13:49:46 MSK)

Ссылка

Спасибо за ответы, решил делать через временную таблицу(у меня на локалхосте sqlite, в продакшене postgresql, rails приложение).

Получился такой говнокод: (черновой вариант)

users = ['user1', 'user3']

tmp_user = Class.new(ActiveRecord::Base)
tmp_user.table_name = "tmp_users_#{SecureRandom.hex(16)}"
tmp_user.connection.create_table(tmp_user.table_name, temporary: true) { |t| t.string :username }
tmp_user.create(users.map { |u| { username: u } })

invalid_users = tmp_user.where("LOWER(#{tmp_user.table_name}.username) NOT IN (SELECT LOWER(username) FROM users)")
invalid_users.each { |u| puts u.inspect }

tmp_user.connection.drop_table(tmp_user.table_name)
tmp_user = nil # FIXME

(0.4ms)  CREATE TEMPORARY TABLE "tmp_users_39a8afd8ba18b7b36b1d18ea51b9ded9" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "username" varchar) 
(0.1ms)  begin transaction
SQL (0.2ms)  INSERT INTO "tmp_users_39a8afd8ba18b7b36b1d18ea51b9ded9" ("username") VALUES (?)  [["username", "user1"]]
(0.1ms)  commit transaction
(0.1ms)  begin transaction
SQL (0.1ms)  INSERT INTO "tmp_users_39a8afd8ba18b7b36b1d18ea51b9ded9" ("username") VALUES (?)  [["username", "user3"]]
(0.1ms)  commit transaction
Load (0.2ms)  SELECT "tmp_users_39a8afd8ba18b7b36b1d18ea51b9ded9".* FROM "tmp_users_39a8afd8ba18b7b36b1d18ea51b9ded9" WHERE (LOWER(tmp_users_39a8afd8ba18b7b36b1d18ea51b9ded9.username) NOT IN (SELECT LOWER(username) FROM users))
#<#<Class:0x007f9fb09f1238> id: 2, username: "user3">
(0.2ms)  DROP TABLE "tmp_users_39a8afd8ba18b7b36b1d18ea51b9ded9"

neversleep ★★
(22.01.15 14:10:15 MSK) автор топика

Ответ на: комментарий от crutch_master 22.01.15 13:42:04 MSK

Глупость.

vtVitus ★★★★★
(22.01.15 14:20:42 MSK)

Ссылка

Ответ на: комментарий от neversleep 22.01.15 14:10:15 MSK

ИМНО временные таблицы не нужны, но это можно существенно улучшить для постгреса. У Постгреса честные транзакции, даже на уровне изменения структуры таблиц, соответственно можешь всё делать в одной транзакции и её не коммитать, а откатывать в конце. открываешь транзакцию создаёшь временную таблицу, фигачешь туда данные, делаешь select, rollback транзакции - диск не будет использован вообще. Но только в постргесе.

vtVitus ★★★★★
(22.01.15 14:27:40 MSK)

Ответ на: комментарий от vtVitus 22.01.15 14:27:40 MSK

Спасибо за совет, сделал всё в одной транзакции.

users = ['user1', 'user3']

tmp_user = Class.new(ActiveRecord::Base)
tmp_user.table_name = "tmp_#{SecureRandom.hex(16)}"
tmp_user.transaction do
  tmp_user.connection.create_table(tmp_user.table_name, id: false, temporary: true) { |t| t.string :username }
  tmp_user.create(users.map { |u| { username: u } })

  tmp_users = tmp_user.where("LOWER(username) NOT IN (SELECT LOWER(username) FROM users)")
  tmp_users.each { |u| puts u.inspect }

  raise ActiveRecord::Rollback
end

(0.5ms)  begin transaction
(0.3ms)  CREATE TEMPORARY TABLE "tmp_d743ebc145fe16c2f340c940e0c2de84" ("username" varchar) 
SQL (0.1ms)  INSERT INTO "tmp_d743ebc145fe16c2f340c940e0c2de84" ("username") VALUES (?)  [["username", "user1"]]
SQL (0.1ms)  INSERT INTO "tmp_d743ebc145fe16c2f340c940e0c2de84" ("username") VALUES (?)  [["username", "user3"]]
Load (0.2ms)  SELECT "tmp_d743ebc145fe16c2f340c940e0c2de84".* FROM "tmp_d743ebc145fe16c2f340c940e0c2de84" WHERE (LOWER(username) NOT IN (SELECT LOWER(username) FROM users))
#<#<Class:0x00000003cec9a0> username: "user3">
(0.2ms)  rollback transaction

neversleep ★★
(22.01.15 14:51:34 MSK) автор топика