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;
Best Answer
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 outerSELECT
, you would have to addORDER BY
to the outer query as well.In your particular case, the order of rows is not guaranteed, even for the simple query:
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: