복붙노트

[RUBY-ON-RAILS] 배열의 PostgreSQL의 경우 모든

RUBY-ON-RAILS

배열의 PostgreSQL의 경우 모든

IN을 사용하지 않는 하나의 경우 - 배열의 모든 요소가 일치해야 조항을 달성 할 수있는 가장 쉽고 빠른 방법은 무엇입니까? 결국은 MongoDB를의 $ 모든처럼 행동해야한다.

conversation_users는이 conversation_id 사이에 테이블을 조인하고 내가 마음에 이런 일이 USER_ID 그룹 대화에 대한 생각 :

WHERE (conversations_users.user_id ALL IN (1,2))

UPDATE 16.07.12

스키마 및 사례에 대한 자세한 정보를 추가 :

UPDATE 23.07.12

내 질문은 사람의 정확한 일치를 찾는 것에 대해입니다. 따라서:

쿼리하는 경우 (1,2,3) 사이의 대화는 (1,2)와 일치하지 않습니다

해결법

  1. ==============================

    1.가정하면 테이블이 좋은 연습을 다음과 중복 행, 다음을 수행해야합니다 간단한 쿼리와 같은 무언가를 방지하기 위해, 즉 제약 조건을 정의 고유 복합 키가 가입 할 수 있습니다.

    가정하면 테이블이 좋은 연습을 다음과 중복 행, 다음을 수행해야합니다 간단한 쿼리와 같은 무언가를 방지하기 위해, 즉 제약 조건을 정의 고유 복합 키가 가입 할 수 있습니다.

    select conversation_id from conversations_users where user_id in (1, 2)
group by conversation_id having count(*) = 2

    그것은 마지막에 숫자 2가 user_ids 목록의 길이입니다 점에 유의하는 것이 중요합니다. 그건 분명히 USER_ID 목록 길이를 변경하는 경우 변경해야합니다. 당신은, 당신의 중복을 포함하지 않는 테이블을 조인 성능에 약간의 비용으로 변화 "COUNT (*)"을 "수 (별개의 USER_ID)"을 가정 할 수 없습니다.

    이 쿼리는 대화도 추가 사용자를 포함하더라도 지정된 모든 사용자를 포함하는 모든 대화를 찾습니다.

    당신이 사용자의 정확히 지정된 세트 만 대화를 원하는 경우, 하나의 접근 방법은 아래와 같이 WHERE 절에 중첩 된 하위 쿼리를 사용하는 것입니다. 참고, 첫 번째와 마지막 줄은 가운데 두 줄이 새로운, 원래 쿼리와 동일합니다.

    select conversation_id from conversations_users where user_id in (1, 2)
   and conversation_id not in
   (select conversation_id from conversations_users where user_id not in (1,2))
group by conversation_id having count(*) = 2

    데이터베이스가 지원하는 경우 동등하게, 당신은 세트 차이 연산자를 사용할 수 있습니다. 여기에 오라클 구문의 예입니다. (포스트 그레스 또는 DB2의 경우를 제외하고 "를 키워드"마이너스 "를 변경할 수 있습니다.)

    select conversation_id from conversations_users where user_id in (1, 2)
  group by conversation_id having count(*) = 2
minus
  select conversation_id from conversations_users where user_id not in (1,2)

    좋은 쿼리 최적화 동일하게 마지막 두 변형을 치료하지만, 확실하게 특정 데이터베이스를 확인해야합니다. 예를 들어, 오라클 11GR2 쿼리 계획은 마이너스 연산자를 적용하기 전에 대화 ID의 두 세트를 정렬하지만, 마지막 쿼리의 정렬 단계를 건너 뜁니다. 두 쿼리 계획은 행, 코어, 캐시, 인덱스 등의 수와 같은 여러 요인에 따라 빠르게 할 수 있도록

  2. ==============================

    2.나는 배열로 해당 사용자를 축소하고있다. 나는이 더 읽기 쉽게하기 위해 CTE합니다 (WITH 절에서 일을) 사용하고 있습니다.

    나는 배열로 해당 사용자를 축소하고있다. 나는이 더 읽기 쉽게하기 위해 CTE합니다 (WITH 절에서 일을) 사용하고 있습니다.

    => select * from conversations_users ;
 conversation_id | user_id
-----------------+---------
               1 |       1
               1 |       2
               2 |       1
               2 |       3
               3 |       1
               3 |       2
(6 rows)       

=> WITH users_on_conversation AS (
  SELECT conversation_id, array_agg(user_id) as users
  FROM conversations_users
  WHERE user_id in (1, 2) --filter here for performance                                                                                      
  GROUP BY conversation_id
)
SELECT * FROM users_on_conversation
WHERE users @> array[1, 2];
 conversation_id | users
-----------------+-------
               1 | {1,2}
               3 | {1,2}
(2 rows)

    EDIT (일부 자원)

  3. ==============================

    3.IN과 수 ()와 @ 알렉스 '대답은 아마 가장 간단한 해결책이지만,이 PL / pgSQL의 기능이 빠를 것으로 예상 :

    IN과 수 ()와 @ 알렉스 '대답은 아마 가장 간단한 해결책이지만,이 PL / pgSQL의 기능이 빠를 것으로 예상 :

    CREATE OR REPLACE FUNCTION f_conversations_among_users(_user_arr int[])
  RETURNS SETOF conversations AS
$BODY$
DECLARE
    _sql text := '
    SELECT c.*
    FROM   conversations c';
    i int;
BEGIN

FOREACH i IN ARRAY _user_arr LOOP
    _sql  := _sql  || '
    JOIN   conversations_users x' || i || ' USING (conversation_id)';
END LOOP;

_sql  := _sql  || '
    WHERE  TRUE';

FOREACH i IN ARRAY _user_arr LOOP
    _sql  := _sql  || '
    AND    x' || i || '.user_id = ' || i;
END LOOP;

/* uncomment for conversations with exact list of users and no more
_sql  := _sql  || '
    AND    NOT EXISTS (
        SELECT 1
        FROM   conversations_users u
        WHERE  u.conversation_id = c.conversation_id
        AND    u.user_id <> ALL (_user_arr)
        )
*/

-- RAISE NOTICE '%', _sql;
RETURN QUERY EXECUTE _sql;

END;
$BODY$ LANGUAGE plpgsql VOLATILE;

    요구:

    SELECT * FROM f_conversations_among_users('{1,2}')

    동적 빌드 함수 형태의 질의를 수행한다 :

    SELECT c.*
FROM   conversations c
JOIN   conversations_users x1 USING (conversation_id)
JOIN   conversations_users x2 USING (conversation_id)
...
WHERE  TRUE
AND    x1.user_id = 1
AND    x2.user_id = 2
...

    이 양식은 관계 부문에 대한 쿼리의 광범위한 테스트에서 최고의 수행.

    또한 앱에서 쿼리를 만들 수있다,하지만 난 당신이 하나 개의 배열 매개 변수를 사용할 것을 전제로했다. 또한,이 빠른 어쨌든 아마입니다.

    어느 쿼리는 다음이 빨리하는 등의 인덱스가 필요합니다

    CREATE INDEX conversations_users_user_id_idx ON conversations_users (user_id);

    (USER_ID, conversation_id)에 다중 열 기본 (또는 고유) 키는 물론이지만, (당신은 잘 할 수 있습니다처럼!) (conversation_id, USER_ID)이 열등한 것 하나. 당신은 dba.SE에이 관련 질문에서 위의 링크에서 짧은 근거, 또는 포괄적 인 평가를 찾을 수

    나는 또한 당신이 conversations.conversation_id에 기본 키가 가정합니다.

    당신은 @ 알렉스 '쿼리 ANALYZE EXPLAIN와 성능 테스트 및이 기능을 실행하고 결과를보고 할 수 있습니까?

    참고 두 솔루션은 대화를 찾을 수 있다는 곳 배열 참여할 적어도 사용자 - 추가 사용자와의 대화를 포함하여. 당신이 사람들을 제외 할 경우, 내 기능의 추가 절을 주석을 해제 (또는 다른 쿼리에 추가).

    당신이 함수의 기능에 대한 자세한 설명이 필요하면 말해.

  4. ==============================

    4.이 액티브 객체를 유지합니다.

    이 액티브 객체를 유지합니다.

    아래 예제에서는 배열의 모든 코드와 연결된 시간 시트를 알고 싶어요.

    codes = [8,9]

Timesheet.joins(:codes).select('count(*) as count, timesheets.*').
           where('codes.id': codes).
           group('timesheets.id').
           having('count(*) = ?', codes.length)

    당신과 함께 작업의 전체 액티브 오브젝트가 있어야합니다. 당신이 진정한 범위 싶은 경우에, 당신은 당신의 위의 예제를 사용하고 .pluck과 결과에 전달할 수 있습니다 (: ID).

  5. ==============================

    5.모든 가능한 값으로 매핑 테이블을 생성하고 이것을 사용

    모든 가능한 값으로 매핑 테이블을 생성하고 이것을 사용

    select 
    t1.col from conversations_users as t1 
    inner join mapping_table as map on t1.user_id=map.user_id
group by 
    t1.col  
having  
    count(distinct conversations_users.user_id)=
    (select count(distinct user_id) from mapping)
  6. ==============================

    6.

    select id from conversations where not exists(
    select * from conversations_users cu 
    where cu.conversation_id=conversations.id 
    and cu.user_id not in(1,2,3)        
)

    이것은 쉽게 레일 범위로 할 수있다.

  7. ==============================

    7.난 당신이 정말 임시 테이블 덤비는 시작하고 싶지 않은 추측하고있다.

    난 당신이 정말 임시 테이블 덤비는 시작하고 싶지 않은 추측하고있다.

    귀하의 질문은 당신이 상위에 사용자 또는 대화 정확히 세트 대화를 원하는 여부에 분명했다. 다음은 상위 집합입니다 :

    with users as (select user_id from users where user_id in (<list>)
              ),
     conv  as (select conversation_id, user_id
               from conversations_users
               where user_id in (<list>)
              )
select distinct conversation_id
from users u left outer join
     conv c
     on u.user_id = c.user_id
where c.conversation_id is not null

    이 쿼리가 잘 작동하기 위해서는, 당신이 사용자와 conversations_users 모두 USER_ID에 인덱스가 있다고 가정합니다.

    정확한 설정하십시오. . .

    with users as (select user_id from users where user_id in (<list>)
              ),
     conv  as (select conversation_id, user_id
               from conversations_users
               where user_id in (<list>)
              )
select distinct conversation_id
from users u full outer join
     conv c
     on u.user_id = c.user_id
where c.conversation_id is not null and u.user_id is not null
  8. ==============================

    8.@ 알렉스 블랙 모어의 대답에 따라, 상응하는 회화 클래스가 될 것이다 당신에 4 범위를 레일 :

    @ 알렉스 블랙 모어의 대답에 따라, 상응하는 회화 클래스가 될 것이다 당신에 4 범위를 레일 :

    # Conversations exactly with users array
scope :by_users, -> (users) { 
                           self.by_any_of_users(users)
                             .group("conversations.id")
                             .having("COUNT(*) = ?", users.length) -
                           joins(:conversations_users)
                             .where("conversations_users.user_id NOT IN (?)", users)
}
# generates an IN clause
scope :by_any_of_users, -> (users) { joins(:conversations_users).where(conversations_users: { user_id: users }).distinct }

    대신 레일을하고의를 최적화 할 수 있습니다 주 - ( "NOT IN") 당신이 어디에요을 할 수있는 (마이너스)하지만 읽기 정말 복잡 할 것입니다.

  9. ==============================

    9.알렉스 블랙 모어의 답변에 따라

    알렉스 블랙 모어의 답변에 따라

    select conversation_id
from conversations_users cu
where user_id in (1, 2)
group by conversation_id 
having count(distinct user_id) = 2

    나는 (이 추가적으로 사용자 무시) USER_1 및 user_2를 포함하는 대화의 conversation_id을 찾는, 같은 목표와 대안 쿼리를 발견했다

    select *
from conversations_users cu1
where 2 = (
    select count(distinct user_id)
    from conversations_users cu2
    where user_id in (1, 2) and cu1.conversation_id = cu2.conversation_id
)

    이 포스트 그레스 쿼리 문을 설명을 통해 수행하는 분석에 따라 느리게, 그리고 난 더 조건이 최소한의 conversations_users의 각 행에 대해 하위 쿼리는 상관 서브 쿼리와 같이 실행 얻을 것이다, 평가 beign이 있기 때문에 그것이 사실 것 같아요. 이 쿼리를 가진 possitive 점은 그래서 당신이 conversations_users 테이블의이 추가적으로 필드를 선택할 수 있습니다, 그룹화되지 않은 것입니다. 어떤 경우에는 (광산 등)은 편리 할 수 ​​있습니다.

    10. from https://stackoverflow.com/questions/11468572/postgresql-where-all-in-array by cc-by-sa and MIT license

'RUBY-ON-RAILS' 카테고리의 다른 글

[RUBY-ON-RAILS] 클립에 여러 파일을 업로드  (0) 2020.02.20
[RUBY-ON-RAILS] 왜 액세스 액티브에 사용 "자기"/ 모델 속성 레일?  (0) 2020.02.20
[RUBY-ON-RAILS] 레일 방법은 어디 그 DateTime 개체로`datetime_select`에서 변환 데이터?  (0) 2020.02.20
[RUBY-ON-RAILS] URL에 표시 3 UTF-8 쿼리 문자열은 레일?  (0) 2020.02.20
[RUBY-ON-RAILS] 레이크 dB 대 마이그레이션 : 마이그레이션 DB 레일  (0) 2020.02.20
,

티스토리툴바