Join result of crosstab to main query

Question

I'm trying to get this column structure:

| id | name | main_image | rating | shop_names | prices | urls | 2 | 3 |

My table structure looks like this

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INT REFERENCES categories(id) NOT NULL,
    name VARCHAR NOT NULL,
    main_image CHAR(60) NULL,
    rating float(2) DEFAULT 0.0 NOT NULL
);

CREATE TABLE shops (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE shops_and_products (
    shop_id INT REFERENCES shops(id) NOT NULL,
    product_id INT REFERENCES products(id) NOT NULL,
    cost INT NOT NULL,
    url VARCHAR NOT NULL
);

CREATE TABLE products_and_characteristics (
    product_id INT REFERENCES products(id) NOT NULL,
    characteristic_id INT REFERENCES characteristics(id) NOT NULL,
    value VARCHAR NOT NULL
);

So far my query look like this:

SELECT "pr"."id", "pr"."name", "pr"."main_image", "pr"."rating", 
        array_agg(sh.name) AS "shop_names", 
        array_agg(snp.cost) AS "prices", 
        array_agg(snp.url) AS "urls",
        "2", "3"
FROM "products" AS "pr" 
LEFT JOIN "shops_and_products" AS "snp" ON ("snp"."product_id" = "pr"."id")
LEFT JOIN "shops" AS "sh" ON ("sh"."id" = "snp"."shop_id")
LEFT JOIN crosstab('SELECT product_id, characteristic_id, value 
                    FROM products_and_characteristics
                    WHERE characteristic_id IN (2,3)
                    ORDER BY characteristic_id') 
          AS ct ("pr_id" int, "2" varchar, "3" varchar) 
          ON ("pr_id" = "pr"."id")
WHERE (("pr"."category_id" = 1) AND ("pr"."id" > -1))
GROUP BY "pr"."id"
ORDER BY "pr"."popularity" DESC

But I get this error while runnning it:

column "ct.2" must appear in the GROUP BY clause or be used in an aggregate function

Which is very odd to me, since there aren't supposed to be any arrays. How can I change my query to acomplish my goal?

Answer 1

It's typically best (cheapest, simplest), to join to additional tables after aggregating. Like:

SELECT *, "2", "3"
FROM  (
   SELECT pr.id, pr.name, pr.main_image, pr.rating
        , pr.popularity   -- not in your table definition (?)
        , array_agg(sh.name)  AS shop_names
        , array_agg(snp.cost) AS prices
        , array_agg(snp.url)  AS urls
   FROM   products                 pr 
   LEFT   JOIN shops_and_products  snp ON snp.product_id = pr.id
   LEFT   JOIN shops               sh  ON sh.id = snp.shop_id
   WHERE  pr.category_id = 1
   AND    pr.id > -1
   GROUP  BY pr.id
   ) sub
LEFT  JOIN crosstab(
     'SELECT product_id, characteristic_id, value 
      FROM   products_and_characteristics
      WHERE  characteristic_id IN (2,3)
      ORDER  BY characteristic_id'
   ) AS ct (id int, "2" text, "3" text) USING (id)
ORDER  BY popularity DESC;  -- not in your table definition (?)

Aside: I wouldn't use numbers as column names. I'd use legal names like char2 ...