0

I need to implement this data structure (i'm using PostgreSQL 9.5):

  • collection#1
    • image#1 (images in the root of collection)
    • image#2
    • group#1 (group in collection)
      • image#1
      • image#2
    • group#2
      • image#1
  • collection#2...

images can live in the root of any Collection and in Groups. Both Groups and Images must be ordered in context of parent Group/Collection.

I have following tables (i am ready to change them in any way in order to get the best results):

CREATE TABLE Collections (
  col_id SERIAL PRIMARY KEY,
  col_name VARCHAR(100)
);

CREATE TABLE Groups (
  group_id SERIAL PRIMARY KEY,
  group_collection INT REFERENCES Collections (col_id) ON DELETE CASCADE,
  group_name VARCHAR(100),
  group_index SERIAL -- Index of group in a collection
);

CREATE TABLE Images (
  image_id SERIAL PRIMARY KEY,
  image_collection INT REFERENCES Collections (col_id) ON DELETE CASCADE,
  image_group INT REFERENCES Groups (group_id) ON DELETE CASCADE,
  image_url VARCHAR(512),
  image_index SERIAL -- Index of image in a group
);

There is no problem to SELECT the content of any Collection, but i have problems with adding, removing and reordering of Images and Groups. Of course i want to keep unique index (ordering) in any Group/Collection (order can have any breaks inside like [1, 4, 10] but cannot have repeated values like [1, 1, 3]).

Update with sample data. Here is the link to sqlfiddle with sample data and queries. I'm using this SELECT request and it is ok (it gives me required output):

SELECT
  Groups.*,
  json_agg(Images.*) as images
FROM Groups
RIGHT JOIN Images
  ON Groups.group_id = Images.image_group
WHERE Groups.group_collection = 1 OR Images.image_collection = 1
GROUP BY Groups.group_id;

My main problem is in reordering Images (especially moving Image from one Group to a specific place into another Group).

Note! Sorry for (maybe) intersecting concepts of order and group as SQL world also has these concepts but i have no idea how to tell the same things with other words.

Peter Gerasimenko
  • 1,906
  • 15
  • 13
  • Please add some sample data (e.g. as `insert into`) and the expected output based on that sample data (as formatted text) –  Dec 03 '15 at 14:15
  • @a_horse_with_no_name thanks for your response! I have added a link to sqlfiddle in my update. – Peter Gerasimenko Dec 04 '15 at 13:27
  • Perhaps something like this: http://stackoverflow.com/questions/8607998/using-a-sort-order-column-in-a-database-table/8608085#8608085 – xQbert Dec 04 '15 at 13:37

0 Answers0