CODEWITHSUNDEEP
sqlarrayspostgresql
user3255076
user3255076

Reputation: 3

SQL creating a concatenated string of names from an array of ids

I am developing reports using Streamtask with xReporter. Recently, our databases were updated to allow for multiple projects to be assigned to a single task. To achieve this, a string containing a a set of CSV index values was used to replace the old single index ie: '[1,2,3,4]' instead of 1. As a result we now have to update a slew of reports.

To get around this, I replace the original selection statement:

(select prj.name from project_table prj where prj.id=t.project_id) as project,

with this line:

(select prj.name from project_table prj where prj.id = ANY((string_to_array(btrim(task_table.projects, '[]'), ','))::integer[])) as project,

which turns the string (successfully) into an array of integer values.

The problem is that I must be able to take this array of indices and retrieve each project name associated with each index, and concatenate them together into a string containing a list of the project names.

Because I have to implement this in an xReporter report, I believe I will need an SQL-only solution to my problem.

Upvotes: 0

Views: 99

Answers (1)

user330315
user330315

Reputation:

Storing comma separated values in a relation database is a really horrible anti-pattern.

Postgres has some really nifty functions to workaround this design-error which makes the query actually quite easy (but probably not really fast)

select prj.name, tsk.id as task_id
from project_table prj
  join ( 
     select id, regexp_split_to_table(projects, ',')::int as pid
     from task_table
  ) tsk on tsk.pid = prj.id
order by prj.id

You original statement looks like you were really embedding that into a bigger statement, so I'm not sure how suitable the join is. But you could always create a normalized view on the mis-designed table using the above approach (the inner derived table).

Here is a SQLFiddle: http://sqlfiddle.com/#!15/6f9bb/1

Upvotes: 2

Related Questions