Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
WITH gadgetprefs AS (select gup.* from gadgetuserpreference gup JOIN portletconfiguration pc ON gup.portletconfiguration=pc.id WHERE pc.gadget_xml~'createdvsresolved-gadget.xml'),
        filterprefs AS (select * from gadgetprefs WHERE userprefkey='projectOrFilterId'),
        cumulativeprefs AS (select * from gadgetprefs WHERE userprefkey='isCumulative'),
        relevantprefs AS (select portletconfiguration, filterprefs.userprefvalue AS projectOrFilterId, cumulativeprefs.userprefvalue AS isCumulative FROM filterprefs JOIN cumulativeprefs USING (portletconfiguration))
	SELECT * From relevantprefs;
┌──────────────────────┬───────────────────┬──────────────┐
│ portletconfiguration │ projectorfilterid │ iscumulative │
├──────────────────────┼───────────────────┼──────────────┤
│                10801 │ filter-10601      │ true         │
│                10803 │ filter-10601      │ false        │
│                10900 │ project-10800     │ true         │
└──────────────────────┴───────────────────┴──────────────┘
(3 rows)

The same, but breaking using regexp_replace() to break projectorfilterid into its constituent parts:

...

Code Block
WITH gadgetprefs AS (select gup.* from gadgetuserpreference gup JOIN portletconfiguration pc ON gup.portletconfiguration=pc.id WHERE pc.gadget_xml~'createdvsresolved-gadget.xml'),
        filterprefs AS (select * from gadgetprefs WHERE userprefkey='projectOrFilterId'),
        cumulativeprefs AS (select * from gadgetprefs WHERE userprefkey='isCumulative'),
        relevantprefs AS (select portletconfiguration, filterprefs.userprefvalue AS projectOrFilterId, cumulativeprefs.userprefvalue AS isCumulative FROM filterprefs JOIN cumulativeprefs USING (portletconfiguration)),
        prefs AS (select portletconfiguration, projectOrFilterId, regexp_replace(projectOrFilterId, '-\d+', '') AS type, regexp_replace(projectOrFilterId, '(filter|project)-', '') AS id, iscumulative FROM relevantprefs) 
   SELECT *, CASE t.userprefkey
							WHEN 'operation' THEN (CASE prefs.isCumulative WHEN 'true' THEN 'cumulative' ELSE 'count' END)
							WHEN 'id' THEN prefs.id
							WHEN 'name' THEN CASE type WHEN 'project' THEN (SELECT pname FROM project WHERE id=prefs.id::integer) WHEN 'filter' THEN prefs.projectOrFilterId END
							WHEN 'type' THEN prefs.type
						END AS userprefvalue
		FROM prefs,
		(VALUES ('name'), ('operation'), ('id'), ('type')) AS t(userprefkey);
┌──────────────────────┬───────────────────┬─────────┬───────┬──────────────┬─────────────┬───────────────┐
│ portletconfiguration │ projectorfilterid │  type   │  id   │ iscumulative │ userprefkey │ userprefvalue │
├──────────────────────┼───────────────────┼─────────┼───────┼──────────────┼─────────────┼───────────────┤
│                10801 │ filter-10601      │ filter  │ 10601 │ true         │ name        │ filter-10601  │
│                10801 │ filter-10601      │ filter  │ 10601 │ true         │ operation   │ cumulative    │
│                10801 │ filter-10601      │ filter  │ 10601 │ true         │ id          │ 10601         │
│                10801 │ filter-10601      │ filter  │ 10601 │ true         │ type        │ filter        │
│                10803 │ filter-10601      │ filter  │ 10601 │ false        │ name        │ filter-10601  │
│                10803 │ filter-10601      │ filter  │ 10601 │ false        │ operation   │ count         │
│                10803 │ filter-10601      │ filter  │ 10601 │ false        │ id          │ 10601         │
│                10803 │ filter-10601      │ filter  │ 10601 │ false        │ type        │ filter        │
│                10900 │ project-10800     │ project │ 10800 │ true         │ name        │ CrowdFlower   │
│                10900 │ project-10800     │ project │ 10800 │ true         │ operation   │ cumulative    │
│                10900 │ project-10800     │ project │ 10800 │ true         │ id          │ 10800         │
│                10900 │ project-10800     │ project │ 10800 │ true         │ type        │ project       │
└──────────────────────┴───────────────────┴─────────┴───────┴──────────────┴─────────────┴───────────────┘
(12 rows)

...

Inserting data: dealing with IDs

...