Skip to content

The easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:

4.7 KB
Raw Blame
import {
  Q4_SQL_NAME,
  SNIPPET_CONTENT,
  SNIPPET_NAME,
  SQL_QUERY,
  XV_DATABASE_NAME,
} from "../constants";

import * as X from "./helpers";

const { H } = cy;

describe("Cross-version questions - sql", () => {
  it(
    "setup: creates a sql question with a snippet and a field filter variable",
    { tags: ["@source"] },
    () => {
      cy.signIn("admin", { setupCache: true });

      cy.request("POST", "/api/native-query-snippet", {
        content: SNIPPET_CONTENT,
        name: SNIPPET_NAME,
      });

      cy.visit("/");
      H.newButton("SQL query").click();

      cy.log("-- Pick the database to query --");
      // With more than one database and none previously used, the picker
      // opens on its own, so there is no trigger to click here
      cy.findByTestId("native-query-top-bar").should(
        "contain",
        "Select a database",
      );
      X.selectFromPopover(XV_DATABASE_NAME);
      cy.findByTestId("selected-database").should(
        "have.text",
        XV_DATABASE_NAME,
      );

      cy.log("-- Create a SQL question --");
      H.NativeEditor.type(SQL_QUERY, {
        focus: true,
        allowFastSet: true,
      }).blur();

      cy.log("-- Map the variable to a field filter --");
      cy.findByTestId("variable-type-select").click();
      X.selectFromPopover("Field Filter");
      H.popover().within(() => {
        cy.findByRole("heading", { name: XV_DATABASE_NAME }).should(
          "be.visible",
        );
        cy.findByText("Reviews").click();
      });
      X.selectFromPopover("Rating");

      H.NativeEditor.clickOnRun();
      X.assertRowCount("1,112");

      cy.log("-- Hide the PK column --");
      // Native results keep the raw column names, and the QA sample data's are lower-case
      cy.findByTestId("visualization-root")
        .findByTestId("table-header")
        .contains("product_id")
        .click();
      H.popover().icon("eye_crossed_out").click();

      cy.log("-- Assert on the preview values in a table --");
      cy.findByTestId("visualization-root").within(() => {
        cy.findByTestId("table-header")
          .should("contain", "reviewer")
          .and("contain", "rating")
          .and("not.contain", "product_id");

        cy.findByTestId("table-body")
          .should("contain", "xavier")
          .and("contain", "4")
          .and("contain", "clement")
          .and("contain", "5");
      });

      cy.log("-- Assert that the filter is mapped to a correct value --");
      cy.findByTestId("parameter-widget").should("contain", "Rating");

      X.saveQuestion(Q4_SQL_NAME);
    },
  );

  it("verify: sql question can be filtered", { tags: ["@target"] }, () => {
    // As a result of PR #77133, cards created in >=v63 fail to load on older
    // versions. We explicitly don't guarantee that things created in a new version of
    // Metabase will work when rolled back to an older version so skipping this test for
    // versions below v63 aligns with our support commitments.
    cy.request("/api/session/properties").then(({ body }) => {
      const major = Number(body.version.tag.match(/^v[01]\.(\d+)\./)?.[1]);
      cy.skipOn(major < 63);
    });

    cy.signIn("admin", { skipCache: true });

    X.visitRootCollectionAndWait();

    cy.findAllByTestId("collection-entry-name")
      .filter(`:contains(${Q4_SQL_NAME})`)
      .click();

    cy.log("-- Assert that the columns are preserved --");
    X.assertRowCount("1,112");
    cy.findByTestId("visualization-root").within(() => {
      cy.findByTestId("table-header")
        .should("contain", "reviewer")
        .and("contain", "rating")
        .and("not.contain", "product_id");

      cy.findByTestId("table-body")
        .should("contain", "xavier")
        .and("contain", "4")
        .and("contain", "clement")
        .and("contain", "5");
    });

    cy.log("-- Apply the filter --");
    cy.findByTestId("native-query-editor-container").within(() => {
      cy.findByText("This question is written in SQL.").should("be.visible");

      cy.findByTestId("parameter-widget").should("contain", "Rating").click();
    });
    H.popover().within(() => {
      cy.findByText("Perfecto").click();
      cy.findByText("Meh").click();

      cy.button("Add filter").click();
    });

    cy.findByTestId("qb-header").findByLabelText("Get Answer").click();

    cy.log("-- Assert that the filter values are correctly applied --");
    X.assertRowCount("442");
    cy.findByTestId("parameter-widget")
      .should("contain", "Rating")
      .and("contain", "2 selections");
    cy.findByTestId("table-body")
      .and("contain", "clement")
      .and("contain", "5")
      .should("contain", "clement")
      .and("contain", "3")
      .should("not.contain", "xavier")
      .and("not.contain", "4");
  });
});