Array distinct unnest ::TEXT[] about purescript-selda HOT 3 CLOSED

I'm sorry I meant sth like this:

-- SELECT DISTINCT unnest(tag_list) FROM article ORDER BY 1
selectDistinctTagLists = distinct $ selectFrom article \a -> do
  let tagList = unnest a.tag_list
  orderBy asc tagList -- or `orderBy asc (lit 1)` should work
  pure { tagList }

singleton = generate_series 1 1

selectFrom singleton \_ ->
  pure
    { tags: toTextArray selectDistinctTagLists
    }

So that is should be
FROM generate_series(1,1) because we do selectFrom singleton
and the nested query should be between ARRAY ( and )::text[]
and no subquery alias should appear

AS tags appears because I named it so after the pure

from purescript-selda.

What about breaking it down into several operations:

SELECT ARRAY(SELECT DISTINCT unnest(tag_list) FROM article ORDER BY 1)::TEXT[] AS tags -- 0. original query
SELECT                                                                         AS tags -- 1. selectValues
       ARRAY(                                                        )::TEXT[]         -- 2. toTextArray
             SELECT DISTINCT unnest(tag_list) FROM article ORDER BY 1                  -- 3. use: distict, orderBy

1. selectValues #46
   I'll provide this function after we merge scope-as-backend, for now, consider the workaround:

• create a dummy table singleton that contains just one value (e.g. use generate_series 1 1)
• selectValues r = selectFrom singleton \_ -> r

2. toTextArray should take a query with just one column (of type String, right?) and wrap it with ARRAY( ... ):TEXT[] treating it as a DB expression Col s String

toTextArray
  ∷ ∀ s
  . (∀ z. FullQuery z { x ∷ Col z String })
  → Col s String
toTextArray subQ = Col $ Any do
  q ← showQuery subQ
  pure $ "ARRAY(" <> q <>")::TEXT[]"

3. SELECT DISTINCT unnest(tag_list) FROM article ORDER BY 1
   this query should be expressible in selda, distinct and orderBy are available, unnest(_) implement like a custom pg function from the guide

I have not tested it myself, but it would be sth that I'd try first.
I hope it helps! 😄

from purescript-selda.

I actually tried that before resulting in

SELECT
  ARRAY (sub_q0.tagList)::text[] AS tagList
FROM ( SELECT DISTINCT
    UNNEST(article_0.tag_list) AS tagList
  FROM
    article article_0
  ORDER BY
    1 ASC) sub_q0;

ERROR: syntax error at or near "sub_q0"
LINE 1: SELECT ARRAY (sub_q0.tagList)::TEXT[] AS tagList

from purescript-selda.