Array distinct unnest ::TEXT[]

[jim108dev]

Hi!
I want all tags (array) in every article.

SELECT ARRAY(SELECT DISTINCT unnest(tag_list) FROM article ORDER BY 1)::TEXT[] AS tags

I have tried a bunch of stuff which always results in double FROM and ::TEXT[] not in the right place. This non-selda-solution is the best I came up with.

CREATE VIEW TAG AS
SELECT
  ARRAY ( SELECT DISTINCT
      unnest(tag_list)
    FROM
      article
    ORDER BY
      1)::text[];

type TagTable
  = Table
      ( array :: Array Tag
      )

tagTable :: TagTable
tagTable = Table { name: "tag" }

findTags :: Pool -> Aff (Array Tag)
findTags pool =
  withConnection pool
    ( \conn ->
        runSelda conn
          $ do
              logQuery $ selectFrom tagTable pure
              query $ selectFrom tagTable pure
    )
    >>= validateTags

If you know something better, let me know.

[Kamirus]

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 SELECT values without FROM clause #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

purescript-selda/src/Selda/Query.purs

Lines 126 to 129 in aa6047a

	distinct
	∷ ∀ s r
	. FullQuery s { | r }
	→ FullQuery s { | r }

purescript-selda/src/Selda/Query.purs

Line 157 in aa6047a

orderBy ∷ ∀ s a. Order → Col s a → Query s Unit

---

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

[jim108dev]

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

[Kamirus]

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