Selecting sections based on matches in another table

Question

Having the following table ( conversations):

 id | record_id  |  is_response  |         text         |
 ---+------------+---------------+----------------------+
 1  |     1      |      false    | in text 1            |
 2  |     1      |      true     | response text 3      |
 3  |     1      |      false    | in text 2            |
 4  |     1      |      true     | response text 2      |
 5  |     1      |      true     | response text 3      |
 6  |     2      |      false    | in text 1            |
 7  |     2      |      true     | response text 1      |
 8  |     2      |      false    | in text 2            |
 9  |     2      |      true     | response text 3      |
 10 |     2      |      true     | response text 4      |

And another help table ( responses):

 id |         text         |
 ---+----------------------+
 1  | response text 1      |
 2  | response text 2      |
 3  | response text 4      |

I am looking for an SQL query to output the following:

  record_id |       context
  ----------+-----------------------+---------------------
       1    | in text 1 response text 3 in text 2 response text 2
  ----------+-----------------------+---------------------
       2    | in text 1 response text 1
  ----------+-----------------------+---------------------
       2    | in text 2 response text 3 response text 4

So, each time it is_responseis equal true, and text in the table of answers to aggregate the conversation context to this point, ignoring part of the conversation, this does not end with the answer in the pool.

In the above example, the live text of the answer is 3 in record_id1.

I tried the following complex SQL, but it breaks, sometimes aggregating the text incorrectly:

with context as(
    with answers as (

       SELECT record_id, is_response, id as ans_id
        , max(id)
          OVER (PARTITION BY record_id ORDER BY id
          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS previous_ans_id
       FROM (select * from conversations where text in (select text from responses)) ans
       ),
     lines as (
      select answers.record_id, con.id, COALESCE(previous_ans_id || ',' || ans_id, '0') as block, con.text as text from answers, conversations con where con.engagement_id = answers.record_id and ((previous_ans_id is null and con.id <= ans_id) OR (con.id > previous_ans_id and con.id <= ans_id)) order by engagement_id, id asc
      )

      select record_id, block,replace(trim(both ' ' from string_agg(text, E' ')) ,'  ',' ') ctx from lines group by record_id, block order by record_id,block
      )

select * from context

I am sure there is a better way.

Answer 1

Here is my trick:

SELECT
    record_id,
    string_agg(text, ' ' ORDER BY id) AS context
FROM (
    SELECT
        *,
        coalesce(sum(incl::integer) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS grp
    FROM (
        SELECT *, is_response AND text IN (SELECT text FROM responses) as incl
        FROM conversations
         ) c
     ) c1
GROUP BY record_id, grp
HAVING bool_or(incl)
ORDER BY max(id);

conversations , , , . , , , , , , . record_id .

Answer 2

:

SELECT record_id, string_agg(text, ' ') As context
FROM  (
   SELECT c.*, count(r.text) OVER (PARTITION BY c.record_id ORDER BY c.id DESC) AS grp
   FROM   conversations  c
   LEFT   JOIN responses r ON r.text = c.text AND c.is_response
   ORDER  BY record_id, id
   ) sub
WHERE  grp > 0  -- ignore conversation part that does not end with a response
GROUP  BY record_id, grp
ORDER  BY record_id, grp;

count() . r.text NULL, LEFT JOIN to responses :

• ,

grp ( "group" ) . , , grp. SELECT.

, . ( ) grp = 0 SELECT.

:

• reset PostgreSQL