Query error in the "best selling products" question in dashboard after sandboxing

[heypoom]

Data sandboxing is implemented in #30, but the "Best Selling Products" (https://shoppy.hosted.staging.metabase.com/question/140-best-selling-products-crash-on-sandboxing) query was running into errors when under sandboxing.

When not under sandboxing, the question itself loads without issues. It is using the "Orders + Products" model (see https://shoppy.hosted.staging.metabase.com/model/139-orders-products) which joins together Orders and Products.

When under sandboxing, a query processor error was thrown.

Models

This queries uses the Orders + Products model, which joins together Orders, Products and Product Categories entity.

SQL for this question

Here is the SQL from the "SQL for this question" panel

SELECT
  "product_categories__via__category_id"."name" AS "product_categories__via__category_id__name",
  "products__via__product_id"."title" AS "products__via__product_id__title",
  COUNT(*) AS "count"
FROM
  (
    SELECT
      "source"."id" AS "id",
      "source"."created_at" AS "created_at",
      "source"."product_id" AS "product_id",
      "source"."quantity" AS "quantity",
      "source"."total_price" AS "total_price",
      "source"."discount_applied" AS "discount_applied",
      "source"."customer_id" AS "customer_id",
      "Products"."category_id" AS "Products__category_id",
      "Products"."id" AS "Products__id",
      "Products"."created_at" AS "Products__created_at",
      "Products"."title" AS "Products__title",
      "Products"."description" AS "Products__description",
      "Products"."image_url" AS "Products__image_url",
      "Products"."price" AS "Products__price",
      "Products"."discount" AS "Products__discount",
      "Products"."shop_id" AS "Products__shop_id",
      "Products"."Product Categories - Category__id" AS "Products__Product Categories - Category__id",
      "Products"."Product Categories - Category__shop_id" AS "Products__Product Categories - Category__shop_id",
      "Products"."Product Categories - Category__created_at" AS "Products__Product Categories - Category__created_at",
      "Products"."Product Categories - Category__name" AS "Products__Product Categories - Category__name",
      "Products"."Product Categories - Category__description" AS "Products__Product Categories - Category__description",
      "Product Categories - Category_2"."id" AS "Product Categories - Category_2__id",
      "Product Categories - Category_2"."shop_id" AS "Product Categories - Category_2__shop_id",
      "Product Categories - Category_2"."created_at" AS "Product Categories - Category_2__created_at",
      "Product Categories - Category_2"."name" AS "Product Categories - Category_2__name",
      "Product Categories - Category_2"."description" AS "Product Categories - Category_2__description"
    FROM
      (
        SELECT
          "public"."orders"."id" AS "id",
          "public"."orders"."created_at" AS "created_at",
          "public"."orders"."product_id" AS "product_id",
          "public"."orders"."quantity" AS "quantity",
          "public"."orders"."total_price" AS "total_price",
          "public"."orders"."discount_applied" AS "discount_applied",
          "public"."orders"."customer_id" AS "customer_id"
        FROM
          "public"."orders"
      ) AS "source"
     
LEFT JOIN (
        SELECT
          "public"."products"."id" AS "id",
          "public"."products"."created_at" AS "created_at",
          "public"."products"."title" AS "title",
          "public"."products"."description" AS "description",
          "public"."products"."image_url" AS "image_url",
          "public"."products"."price" AS "price",
          "public"."products"."category_id" AS "category_id",
          "public"."products"."discount" AS "discount",
          "public"."products"."shop_id" AS "shop_id",
          "Product Categories - Category"."id" AS "Product Categories - Category__id",
          "Product Categories - Category"."shop_id" AS "Product Categories - Category__shop_id",
          "Product Categories - Category"."created_at" AS "Product Categories - Category__created_at",
          "Product Categories - Category"."name" AS "Product Categories - Category__name",
          "Product Categories - Category"."description" AS "Product Categories - Category__description"
        FROM
          "public"."products"
          LEFT JOIN "public"."product_categories" AS "Product Categories - Category" ON "public"."products"."category_id" = "Product Categories - Category"."id"
      ) AS "Products" ON "source"."product_id" = "Products"."id"
      LEFT JOIN "public"."product_categories" AS "Product Categories - Category_2" ON "Products"."category_id" = "Product Categories - Category_2"."id"
  ) AS "source"
  LEFT JOIN "public"."product_categories" AS "product_categories__via__category_id" ON "source"."Products__category_id" = "product_categories__via__category_id"."id"
  LEFT JOIN "public"."products" AS "products__via__product_id" ON "source"."product_id" = "products__via__product_id"."id"
GROUP BY
  "product_categories__via__category_id"."name",
  "products__via__product_id"."title"
ORDER BY
  "count" DESC,
  "product_categories__via__category_id"."name" ASC,
  "products__via__product_id"."title" ASC
LIMIT
  10

Query Log & Stacktrace

The full query log and stacktrace is linked here.

The failing request is POST https://shoppy.hosted.staging.metabase.com/api/dashboard/17/dashcard/131/card/140/query, which returns the error in the above link.

TL;DR: column source.products__via__product_id__title does not exist

How to reproduce in the Metabase app via the Shoppy instance

• Request access to the Shoppy instance
• Create a new user account (e.g. yourname+shopadmin@metabase.com) and assign yourself a "Shop Admin" role, and set user attributes.
• Visit the best selling products question at https://shoppy.hosted.staging.metabase.com/question/140-best-selling-products-crash-on-sandboxing
• The error should be there as per the above image.

[heypoom]

@bshepherdson I added the problematic queries as well as more info on the models to the issues. I've also invited you to the Shoppy instance to be able to look more into the queries.

The account with +shopadmin@metabase.com will give you sandboxed access to be able to replicate the query error, while the @metabase.com account gives you full admin permission.

Let me know if you need anything else - thanks a ton!