> "Ondřej" == Ondřej Bouda writes:
Ondřej> What about subqueries?
Ondřej> SELECT
Ondřej> items.*,
Ondřej> (SELECT array_agg(color_name) FROM colors WHERE item_id =
Ondřej> items.id) AS color_names,
Ondřej> (SELECT array_agg(image_file) FROM images WHERE item_id =
Ondřej> i
Dne 11.11.2018 v 17:20 Rob Nikander napsal(a):
> I have tables `items`, `colors`, and `images`. Items have many
colors, and many images.
>
> I want a query to list items, with their colors and images. Each
result row is an item, so the colors and images must be aggregated into
arrays or json.
> On Nov 11, 2018, at 12:54 PM, Andrew Gierth
> wrote:
> …
Thank you that is very helpful.
Could the CTE’s theoretically be optimized in a future version of PG, to work
like the subqueries? I like to use them to give names to intermediate results,
but I’ll stay away from them for now.
Rob
> "Andrew" == Andrew Gierth writes:
Andrew> Unfortunately, the planner isn't smart enough yet to know that
Andrew> these two are equivalent,
oops, I edited the second one before posting in a way that made them not
be equivalent: adding a "group by x0.item_id" in both subqueries in
method 2
> "Rob" == Rob Nikander writes:
Rob> I want a query to list items, with their colors and images. Each
Rob> result row is an item, so the colors and images must be aggregated
Rob> into arrays or json.
Rob> If there were only one other table, it’s a simple join and group…
Rob> select
Hi,
I’ve run into this pattern a few times, and I usually get a little confused.
I’m wondering if there are some common solutions or techniques.
Simplified example:
I have tables `items`, `colors`, and `images`. Items have many colors, and many
images.
I want a query to list items, with their
