0

This is my VIEW declaration

CREATE OR REPLACE VIEW inv_groups_vw AS 
 SELECT gg.id,
    gg.sku,
    gg.name,
    gg.description,
        CASE
            WHEN count(i.*) = 0 THEN '[]'::json
            ELSE json_agg(i.*)
        END AS items
   FROM ( SELECT g.id,
            g.sku,
            g.name,
            g.description,
            m.item_id
           FROM inv_items g
      LEFT JOIN inv_items_group_members m ON g.id = m.group_id
     WHERE g.is_group = true
     ORDER BY m.item_index) gg
   LEFT JOIN inv_items i ON gg.item_id = i.id AND i.is_group = false
  GROUP BY gg.id, gg.sku, gg.name, gg.description;

And peforming SELECT * FROM inv_groups_vw result

{
   "id": 3
   "sku": "PVC-1861-J",
   "name": "PVC 18 oz (jaune)",
   "description": "Rouleau de PVC 18 onces de couleur jaune",
   "items": [
      {
         "id":1,
         "sku":"PVC-186150-J",
         "name":"PVC 18 oz 61 in 50 vg (jaune)",
         "description":"...",
         "is_group":false
      }, {
         "id":2,
         "sku":"PVC-1861100-J",
         "name":"PVC 18 oz 61 in 100 vg (jaune)",
         "description":"...",
         "is_group":false
      }
   ]
}

However, if I execute SELECT * FROM inv_groups_vw WHERE sku = 'PVC-1861-J'; result

{
   "id": 3
   "sku": "PVC-1861-J",
   "name": "PVC 18 oz (jaune)",
   "description": "Rouleau de PVC 18 onces de couleur jaune",
   "items": [
      {
         "id":2,
         "sku":"PVC-1861100-J",
         "name":"PVC 18 oz 61 in 100 vg (jaune)",
         "description":"...",
         "is_group":false
      }, {
         "id":1,
         "sku":"PVC-186150-J",
         "name":"PVC 18 oz 61 in 50 vg (jaune)",
         "description":"...",
         "is_group":false
      }
   ]
}

Why is ORDER BY m.item_index not respected?

Update

This may seem to work, but is it really?

CREATE OR REPLACE VIEW inv_groups_vw AS 
 SELECT gr.id,
    gr.sku,
    gr.name,
    gr.description,
    json_agg(gr.item) AS items
   FROM ( SELECT gg.id,
            gg.sku,
            gg.name,
            gg.description,
                CASE
                    WHEN i.id IS NULL THEN '{}'::json
                    ELSE row_to_json(i.*)
                END AS item
           FROM ( SELECT g.id,
                    g.sku,
                    g.name,
                    g.description,
                    m.item_id,
                    m.item_index
                   FROM inv_items g
              LEFT JOIN inv_items_group_members m ON g.id = m.group_id
             WHERE g.is_group
             ORDER BY m.item_index) gg
      LEFT JOIN inv_items i ON gg.item_id = i.id AND i.is_group = false
     ORDER BY gg.item_index) gr
  GROUP BY gr.id, gr.sku, gr.name, gr.description;
1
  • An order by in a derived table (aka sub-select) doesn't make sense. Jul 4 '14 at 15:36
2

The effect of ORDER BY is only mandatory on the same query level.

Your view acts as derived table and if you just SELECT * FROM inv_groups_vw, the order is preserved.
However, when adding another WHERE clause to the outer SELECT, you would have to add ORDER BY to the outer query as well.

In your particular case, the order of rows is not guaranteed, even for the simple query:

SELECT * FROM inv_groups_vw

Since the only ORDER BY in the view is in a subquery itself. It's an implementation detail that the sort order happens to be preserved. That can change any time without notice.

More importantly, your objective seems to be to order the values in items, which is a related, but different issue.

Complete rewrite

After feedback from your comments I think this should be best to fulfill your requirement:

items (i.e. array_agg(i)) are REQUIRED to be ordered by item_index.

CREATE OR REPLACE VIEW inv_groups_vw AS 
SELECT g.id,
       g.sku,
       g.name,
       g.description,
       CASE
          WHEN count(i.id) = 0 THEN '[]'::json
          ELSE json_agg(i ORDER BY m.item_index)
       END AS items
FROM   inv_items                    g
LEFT   JOIN inv_items_group_members m ON m.group_id = g.id
LEFT   JOIN inv_items               i ON i.id = m.item_id
                                     AND NOT i.is_group
WHERE  g.is_group
GROUP  BY g.id;    -- assuming g.id is the pk!
6
  • Ok, so is there any way to garantee a sort order from what I have? The items (i.e. array_agg(i)) are REQUIRED to be ordered by item_index. Jul 4 '14 at 15:08
  • @YanickRochon: I added a bit (updated) to address that. Jul 4 '14 at 15:17
  • The view you're suggesting does not aggregate items anymore and return a separate row from every result; items needs to be an array of all i.* Jul 4 '14 at 15:18
  • If I wrap your query inside another query, move the GROUP BY to that outer query, use row_to_json instead of array_agg and use array_agg in the outer query, it seems to preserve the order... Jul 4 '14 at 15:24
  • @YanickRochon: OK, can't get away that easily. I completely rewrote & simplified. BTW, you are ordering by item_index. Did you actually want id instead or additionally? Jul 4 '14 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.