Forem: Leonid Yakovlev

How to Build Bidirectional Infinite Scroll in React

Leonid Yakovlev — Sun, 08 Mar 2026 18:00:40 +0000

Most infinite scroll tutorials cover one direction: down. Detect the bottom, load more, done. But real-world apps need to scroll both ways too: chat history, log viewers, timelines. And scrolling up introduces a problem that scrolling down never has.

In this guide I'll build a bidirectional infinite scroll from scratch. I'm using React and @tanstack/react-virtual, but the technique itself is just math on scroll offsets. It works the same way in Vue, Svelte, or vanilla JS.

Live Demo | Source Code

The problem, visually

Imagine a list of 1000 items. The user is looking at item #50. You prepend 200 items above.

What you expect: the user still sees item #50.
What actually happens: the scroll position stays at the same pixel offset. But item #50 is now at a different pixel offset (it shifted down by the height of 200 new items). The user sees item #250. The content jumped.

BEFORE PREPEND          AFTER PREPEND (broken)

┌─────────────┐         ┌─────────────┐
│ item 48     │         │ item 248 ←── wait, what?
│ item 49     │         │ item 249    │
│ item 50  ◄──│── user  │ item 250    │
│ item 51     │  sees   │ item 251    │
│ item 52     │  this   │ item 252    │
└─────────────┘         └─────────────┘
scrollTop: 2200px       scrollTop: 2200px (same!)
                        but item 50 is now at 11000px

Virtualization, data loading, rendering — all standard. Fixing this jump is the only non-obvious part.

The stack

React + TypeScript + Vite
@tanstack/react-virtual (renders only visible items, important for 1000+ rows)
Tailwind CSS

I also added react-chartjs-2 for a bar chart synced to scroll position, but that's separate from the scroll logic.

Step 1: the data hook

We need a data source that can load in both directions. In a real app this would be an API. For the demo, I generate mock log events:

export function useLogData() {
  const [days, setDays] = useState<DayData[]>(() => generateDays(startDate, 30));
  const prependCountRef = useRef(0);

  const loadEarlier = useCallback(() => {
    setDays(prev => {
      const newDays = generateDays(earlierDate, 15);
      // Remember how many items we're about to prepend
      prependCountRef.current = newDays.reduce(
        (sum, d) => sum + d.events.length, 0
      );
      return [...newDays, ...prev];
    });
  }, []);

  const loadLater = useCallback(() => {
    setDays(prev => [...prev, ...generateDays(laterDate, 15)]);
  }, []);

  return { days, allEvents, loadEarlier, loadLater, prependCountRef };
}

prependCountRef stores how many items were just prepended. We'll need this number in a moment.

Step 2: the virtualized list

With @tanstack/react-virtual, we render only ~20 visible items out of thousands:

const virtualizer = useVirtualizer({
  count: events.length,
  getScrollElement: () => parentRef.current,
  estimateSize: () => 44,  // estimated row height in px
  overscan: 10,            // extra items rendered above/below
});

The scroll container holds a tall empty div (total height of all items), and inside it, only visible items are absolutely positioned with transform: translateY(). Standard virtualization.

Step 3: triggering loads in both directions

On every scroll, we check if the user is near the edges:

const handleScroll = useCallback(() => {
  const items = virtualizer.getVirtualItems();
  if (items.length === 0) return;

  const firstVisible = items[0];
  const lastVisible = items[items.length - 1];

  // Near the top? Load earlier data
  if (firstVisible.index <= 5) {
    loadEarlier();
  }
  // Near the bottom? Load later data
  if (lastVisible.index >= events.length - 5) {
    loadLater();
  }
}, [virtualizer]);

loadLater (appending) just works. The virtualizer sees more items, extends the container height, the user keeps scrolling.

loadEarlier (prepending) breaks everything. This is where the jump happens.

Step 4: fixing the jump with scroll anchoring

After prepending, shift the scroll position down by exactly the height of the added items:

useEffect(() => {
  const prepended = prependCountRef.current;
  if (prepended > 0 && events.length > prevCountRef.current) {
    const currentOffset = virtualizer.scrollOffset ?? 0;
    const addedHeight = prepended * 44; // items × estimateSize
    virtualizer.scrollToOffset(currentOffset + addedHeight, { align: 'start' });
    prependCountRef.current = 0;
  }
  prevCountRef.current = events.length;
}, [events.length]);

BEFORE PREPEND          AFTER PREPEND (fixed)

┌─────────────┐         ┌─────────────┐
│ item 48     │         │ item 48     │ ← same!
│ item 49     │         │ item 49     │
│ item 50  ◄──│── user  │ item 50  ◄──│── still here
│ item 51     │  sees   │ item 51     │
│ item 52     │  this   │ item 52     │
└─────────────┘         └─────────────┘
scrollTop: 2200px       scrollTop: 11000px (adjusted!)

The user sees no change. The 200 new items are above the viewport, loaded silently.

Why a ref, not state? prependCountRef is set inside setDays (during the state update) and read in the useEffect (after the update). A ref bridges these two moments without triggering an extra render.

Step 5: dynamic row heights

If rows can expand (clicking a log entry to see details), the virtualizer needs to know the actual height, not the estimate:

export const LogItem = memo(function LogItem({ event, virtualIndex, measureRef, start }) {
  const [expanded, setExpanded] = useState(false);
  const nodeRef = useRef<HTMLDivElement | null>(null);

  const setRef = useCallback((node: HTMLDivElement | null) => {
    nodeRef.current = node;
    measureRef(node); // tell virtualizer to measure this node
  }, [measureRef]);

  // Re-measure BEFORE paint when expand/collapse changes
  useLayoutEffect(() => {
    if (nodeRef.current) measureRef(nodeRef.current);
  }, [expanded]);

  return (
    <div ref={setRef} data-index={virtualIndex}
         style={{ transform: `translateY(${start}px)` }}>
      {/* row content */}
      {expanded && <pre>{JSON.stringify(event.details, null, 2)}</pre>}
    </div>
  );
});

Two things to watch for:

data-index is how @tanstack/react-virtual identifies which virtual item a DOM node belongs to. Without it, measureElement doesn't know which row it's measuring.
useLayoutEffect, not useEffect. useEffect runs after the browser paints, so the user would see one frame where expanded content overlaps the next row. useLayoutEffect runs before paint, so the measurement happens invisibly.

Result

Scroll down, new days load. Scroll up, older days load, no jump. Click a chart bar, the list scrolls to that day. Expand a log entry, rows below shift correctly.

Starts with ~2000 items, grows infinitely in both directions. The virtualizer keeps DOM node count at ~20-30 regardless.

Try the demo | Source Code

TL;DR

The whole technique is two lines:

const addedHeight = prependedCount * estimatedRowHeight;
virtualizer.scrollToOffset(currentOffset + addedHeight, { align: 'start' });

Track how many items you prepend. After prepending, add their height to the scroll offset.

🚀 Building 3D Tarot & 3D Astrology Chart with React Three.js

Leonid Yakovlev — Tue, 18 Mar 2025 07:51:20 +0000

We’re excited to introduce AstroTarot.online – a unique platform that blends technology and esotericism! The highlight of our project is the 3D Tarot experience and an interactive 3D Astrology Chart, powered by React Three.js.

🔮 How We Built the 3D Tarot Experience

We wanted users to not just see the cards but to truly interact with them in a 3D space. With React Three.js, we achieved:

Realistic 3D tarot cards with custom textures.
Smooth flip animations and dynamic lighting.
Drag-and-drop functionality for an immersive reading experience.

✨ Creating the 3D Astrology Chart

A natal chart is a complex diagram requiring precision. With Three.js, we:

Plotted planets in a fully interactive 3D space.
Used WebGL shaders for animated visual effects.
Implemented smooth zoom and rotation to explore the chart from any angle.

🛠 Tech Stack

React – our frontend framework.
React Three.js – for 3D rendering.
Zustand – for state management.
Astro API – for planetary position calculations.

Want to try it? 🚀 Visit AstroTarot.online and explore the world of 3D Tarot & Astrology!

We’d love to hear your thoughts – drop a comment below! 👇

Personalised Tarot Reading service based on AI and 3d interactive

Leonid Yakovlev — Mon, 03 Apr 2023 17:01:47 +0000

🎉 Exciting news! We're thrilled to announce the upcoming launch of our new online interactive tarot card reading service. Our platform is currently under development, but you can visit our website at https://www.lumi-tarot.com/ for a sneak peek and try out some of our features.

🛠️ We utilized Next.js for server-side rendering, React Three Fiber for creating stunning 3D visuals with WebGL, Prisma ORM for efficient database management, and ChatGPT - an AI model trained by OpenAI, for personalized tarot card readings. Our development process was streamed on Twitch, and we're excited to share our learnings with the community.

🔮 Our service is designed to help you discover and learn tarot readings in a fun and engaging way, while providing personalized answers to your most pressing questions. Whether you're a beginner or an experienced tarot reader, our user-friendly and intuitive platform makes it easy to get started.

👉 With our service, you can choose to receive answers from our AI-powered tarot card reader or from our team of experienced tarot readers, who are available to provide personalized insights and guidance. We believe that everyone can benefit from tarot readings, and our platform is designed to make it accessible to all.

💪 We are working hard to provide the best possible experience for our users, and we would love to hear your feedback! So go ahead and give our platform a try at https://www.lumi-tarot.com/, and let us know what you think. Your feedback is crucial in helping us improve and provide the best possible experience for our users.

Thank you for your support, and we look forward to helping you on your journey of self-discovery and learning! 🌟

Would you like to know more about our building process? Let us know in the comments below!

Angular Dashboard 🅰️ with Material

Leonid Yakovlev — Tue, 27 Oct 2020 15:37:17 +0000

Angular 🅰️ is the web framework of choice for many professional developers. According to Stack Overflow Developer Survey 2020, only just about ~10 % of developers prefer React to Angular.

Material is the reference implementation of Material Design components for Angular. It provides a lot of ready-to-use components to build web applications, including dashboards, fast and easy.

In this guide, we'll learn how to build a full-stack dashboard with KPIs, charts, and a data table. We'll go from data in the database to the interactive, filterable, and searchable dashboard.

We're going to use Cube.js for our analytics API. It removes all the hustle of building the API layer, generating SQL, and querying the database. It also provides many production-grade features like multi-level caching for optimal performance, multi-tenancy, security, and more.

Below you can see an animated image of the application we're going to build. Also, check out the live demo and the full source code available on GitHub.

Analytics Backend with Cube.js

We're going to build the dashboard for an e-commerce company that wants to track its overall performance and orders' statuses. Let's assume that the company keeps its data in an SQL database. So, in order to display that data on a dashboard, we're going to create an analytics backend.

First, we need to install the Cube.js command-line utility (CLI). For convenience, let's install it globally on our machine.

$ npm install -g cubejs-cli

Then, with the CLI installed, we can create a basic backend by running a single command. Cube.js supports all popular databases, and the backend will be pre-configured to work with a particular database type:

$ cubejs create <project name> -d <database type>

We’ll use a PostgreSQL database. Please make sure you have PostgreSQL installed.

To create the backend, we run this command:

$ cubejs create angular-dashboard -d postgres

Now we can download and import a sample e-commerce dataset for PostgreSQL:

$ curl http://cube.dev/downloads/ecom-dump.sql > ecom-dump.sql
$ createdb ecom
$ psql --dbname ecom -f ecom-dump.sql

Once the database is ready, the backend can be configured to connect to the database. To do so, we provide a few options via the .env file in the root of the Cube.js project folder (angular-dashboard):

CUBEJS_DB_NAME=ecom
CUBEJS_DB_TYPE=postgres
CUBEJS_API_SECRET=secret

Now we can run the backend!

In development mode, the backend will also run the Cube.js Playground. It's a time-saving web application that helps to create a data schema, test out the charts, etc. Run the following command in the Cube.js project folder:

$ node index.js

Next, open http://localhost:4000 in your browser.

We'll use the Cube.js Playground to create a data schema. It's essentially a JavaScript code that declaratively describes the data, defines analytical entities like measures and dimensions, and maps them to SQL queries. Here is an example of the schema which can be used to describe users’ data.

cube('Users', {
  sql: 'SELECT * FROM users',

  measures: {
    count: {
      sql: `id`,
      type: `count`
    },
  },

  dimensions: {
    city: {
      sql: `city`,
      type: `string`
    },

    signedUp: {
      sql: `created_at`,
      type: `time`
    },

    companyName: {
      sql: `company_name`,
      type: `string`
    },
  },
});

Cube.js can generate a simple data schema based on the database’s tables. If you already have a non-trivial set of tables in your database, consider using the data schema generation because it can save time.

For our backend, we select the line_items, orders, products, and users tables and click “Generate Schema.” As the result, we'll have 4 generated files in the schema folder—one schema file per table.

Once the schema is generated, we can build sample charts via web UI. To do so, navigate to the “Build” tab and select some measures and dimensions from the schema.

The "Build" tab is a place where you can build sample charts using different visualization libraries and inspect every aspect of how that chart was created, starting from the generated SQL all the way up to the JavaScript code to render the chart. You can also inspect the Cube.js query encoded with JSON which is sent to Cube.js backend.

Frontend application

Creating a complex dashboard from scratch usually takes time and effort. Fortunately, Angular provides a tool that helps to create an application boilerplate code with just a few commands. Adding the Material library and Cube.js as an analytical API is also very easy.

Installing the libraries

So, let's use Angular CLI and create the frontend application inside the angular-dashboard folder:

npm install -g @angular/cli  # Install Angular CLI
ng new dashboard-app         # Create an app
cd dashboard-app             # Change the folder
ng serve                     # Run the app

Congratulations! Now we have the dashboard-app folder in our project. This folder contains the frontend code that we're going to modify and evolve to build our analytical dashboard.

Now it's time to add the Material library. To install the Material library to our application, run:

ng add @angular/material

Choose a custom theme and the following options:

Set up global Angular Material typography styles? - Yes
Set up browser animations for Angular Material? - Yes

Great! We'll also need a charting library to add charts to the dashboard. Chart.js is the most popular charting library, it's stable and feature-rich. So...

It's time to add the Chart.js library. To install it, run:

npm install ng2-charts
npm install chart.js

Also, to be able to make use of ng2-charts directives in our Angular application we need to import ChartsModule. For that, we add the following import statement in the app.module.ts file:

+ import { ChartsModule } from 'ng2-charts';

The second step is to add ChartsModule to the imports array of the @NgModule decorator as well:

@NgModule({
  declarations: [
    AppComponent
  ],
  imports: [
    BrowserModule,
    BrowserAnimationsModule,
+    ChartsModule
  ],
  providers: [],
  bootstrap: [AppComponent]
})

Finally, it's time to add Cube.js. This is the final step that will let our application access the data in our database via an analytical API is to install Cube.js client libraries for Angular. Run:

npm install --save @cubejs-client/ngx
npm install --save @cubejs-client/core

Now we can add CubejsClientModule to your app.module.ts file:

...
+ import { CubejsClientModule } from '@cubejs-client/ngx';

+ const cubejsOptions = {
+   token: 'YOUR-CUBEJS-API-TOKEN',
+   options: { apiUrl: 'http://localhost:4200/cubejs-api/v1' }
+ };

@NgModule({
  ...
  imports: [
     ...
+    CubejsClientModule.forRoot(cubejsOptions)
  ],
  ...
})
export class AppModule { }

CubejsClientModule provides CubejsClient which you can inject into your components or services to make API calls and retrieve data:

import { CubejsClient } from '@cubejs-client/ngx';

export class AppComponent {
  constructor(private cubejs:CubejsClient){}

  ngOnInit(){
    this.cubejs.load({
      measures: ["some_measure"]
    }).subscribe(
      resultSet => {
        this.data = resultSet.chartPivot();
      },
      err => console.log('HTTP Error', err)
    );
  }
}

So far so good! Let's make it live.

Creating the first chart

Let's create a generic bar-chart component using Angular CLI. Run:

$ ng g c bar-chart  # Oh these single-letter commands!

This command will add four new files to our app because this is what Angular uses for its components:

src/app/bar-chart/bar-chart.component.html
src/app/bar-chart/bar-chart.component.ts
src/app/bar-chart/bar-chart.component.scss
src/app/bar-chart/bar-chart.component.spec.ts

Open bar-chart.component.html and replace the content of that file with the following code:

<div>
  <div style="display: block">
    <canvas baseChart
                        height="320"
            [datasets]="barChartData"
            [labels]="barChartLabels"
            [options]="barChartOptions"
            [legend]="barChartLegend"
            [chartType]="barChartType">
    </canvas>
  </div>
</div>

Here we’re using the baseChart directive which is added to a canvas element. Furthermore, the datasets, labels, options, legend, and chartType attributes are bound to class members which are added to the implementation of the BarChartComponent class in bar-chart-component.ts:

import { Component, OnInit, Input } from "@angular/core";
import { CubejsClient } from '@cubejs-client/ngx';
import {formatDate, registerLocaleData} from "@angular/common"
import localeEn from '@angular/common/locales/en';

registerLocaleData(localeEn);

@Component({
  selector: "app-bar-chart",
  templateUrl: "./bar-chart.component.html",
  styleUrls: ["./bar-chart.component.scss"]
})

export class BarChartComponent implements OnInit {
  @Input() query: Object;
  constructor(private cubejs:CubejsClient){}

  public barChartOptions = {
    responsive: true,
    maintainAspectRatio: false,
    legend: { display: false },
    cornerRadius: 50,
    tooltips: {
      enabled: true,
      mode: 'index',
      intersect: false,
      borderWidth: 1,
      borderColor: "#eeeeee",
      backgroundColor: "#ffffff",
      titleFontColor: "#43436B",
      bodyFontColor: "#A1A1B5",
      footerFontColor: "#A1A1B5",
    },
    layout: { padding: 0 },
    scales: {
      xAxes: [
        {
          barThickness: 12,
          maxBarThickness: 10,
          barPercentage: 0.5,
          categoryPercentage: 0.5,
          ticks: {
            fontColor: "#A1A1B5",
          },
          gridLines: {
            display: false,
            drawBorder: false,
          },
        },
      ],
      yAxes: [
        {
          ticks: {
            fontColor: "#A1A1B5",
            beginAtZero: true,
            min: 0,
          },
          gridLines: {
            borderDash: [2],
            borderDashOffset: [2],
            color: "#eeeeee",
            drawBorder: false,
            zeroLineBorderDash: [2],
            zeroLineBorderDashOffset: [2],
            zeroLineColor: "#eeeeee",
          },
        },
      ],
    },
  };

  public barChartLabels = [];
  public barChartType = "bar";
  public barChartLegend = true;
  public barChartData = [];

  ngOnInit() {
    this.cubejs.load(this.query).subscribe(
      resultSet => {
        const COLORS_SERIES = ['#FF6492', '#F3F3FB', '#FFA2BE'];
        this.barChartLabels = resultSet.chartPivot().map((c) => formatDate(c.category, 'longDate', 'en'));
        this.barChartData = resultSet.series().map((s, index) => ({
          label: s.title,
          data: s.series.map((r) => r.value),
          backgroundColor: COLORS_SERIES[index],
          fill: false,
        }));
      },
      err => console.log('HTTP Error', err)
    );
  }
}

Okay, we have the code for our chart, let's show it in the app. We can use an Angular command to generate a base grid. Run:

ng generate @angular/material:dashboard dashboard-page

So, now we have a folder with the dashboard-page component. Open app.component.html and insert this code:

<app-dashboard-page></app-dashboard-page>

Now it's time to open dashboard-page/dashobard-page.component.html and add our component like this:

<div class="grid-container">
  <h1 class="mat-h1">Dashboard</h1>
+  <mat-grid-list cols="2" rowHeight="450px">
-    <mat-grid-tile *ngFor="let card of cards | async" [colspan]="card.cols" [rowspan]="card.rows">
+    <mat-grid-tile *ngFor="let card of cards" [colspan]="card.cols" [rowspan]="card.rows">
      <mat-card class="dashboard-card">
        <mat-card-header>
          <mat-card-title>
            <button mat-icon-button class="more-button" [matMenuTriggerFor]="menu" aria-label="Toggle menu">
              <mat-icon>more_vert</mat-icon>
            </button>
            <mat-menu #menu="matMenu" xPosition="before">
              <button mat-menu-item>Expand</button>
              <button mat-menu-item>Remove</button>
            </mat-menu>
          </mat-card-title>
        </mat-card-header>
        <mat-card-content class="dashboard-card-content">
          <div>
+            <app-bar-chart [query]="card.query" *ngIf="card.chart === 'bar'"></app-bar-chart>
          </div>
        </mat-card-content>
      </mat-card>
    </mat-grid-tile>
  </mat-grid-list>
</div>

And the last edit will be in dashboard-page.component.ts:

import { Component, OnInit } from "@angular/core";
import { BehaviorSubject } from "rxjs";

@Component({
  selector: "app-dashboard-page",
  templateUrl: "./dashboard-page.component.html",
  styleUrls: ["./dashboard-page.component.scss"]
})
export class DashboardPageComponent implements OnInit {
  private query = new BehaviorSubject({
    measures: ["Orders.count"],
    timeDimensions: [{ dimension: "Orders.createdAt", granularity: "month", dateRange: "This year" }],
    dimensions: ["Orders.status"],
    filters: [{ dimension: "Orders.status", operator: "notEquals", values: ["completed"] }]
  });
  cards = [];

  ngOnInit() {
    this.query.subscribe(data => {
      this.cards[0] = {
        chart: "bar", cols: 2, rows: 1,
        query: data
      };
    });
  }
}

Nice work! 🎉 That's all we need to display our first chart with the data loaded from Postgres via Cube.js.

In the next part, we'll make this chart interactive by letting users change the date range from "This year" to other predefined values.

Interactive Dashboard with Multiple Charts

In the previous part, we've created an analytical backend and a basic dashboard with the first chart. Now we're going to expand the dashboard so it provides the view of key performance indicators of our e-commerce company.

Custom Date Range

As the first step, we'll let users change the date range of the existing chart.

For that, we'll need to make a change to the dashboard-page.component.ts file:

// ...

export class DashboardPageComponent implements OnInit {
  private query = new BehaviorSubject({
    measures: ["Orders.count"],
    timeDimensions: [{ dimension: "Orders.createdAt", granularity: "month", dateRange: "This year" }],
    dimensions: ["Orders.status"],
    filters: [{ dimension: "Orders.status", operator: "notEquals", values: ["completed"] }]
  });
+  changeDateRange = (value) => {
+    this.query.next({
+      ...this.query.value,
+      timeDimensions: [{ dimension: "Orders.createdAt", granularity: "month", dateRange: value }]
+    });
+  };

  cards = [];

  ngOnInit() {
    this.query.subscribe(data => {
      this.cards[0] = {
        chart: "bar", cols: 2, rows: 1,
        query: data
      };
    });
  }
}

And another one to the dashobard-page.component.html file:

<div class="grid-container">
  <h1 class="mat-h1">Dashboard</h1>
  <mat-grid-list cols="3" rowHeight="450px">
    <mat-grid-tile *ngFor="let card of cards" [colspan]="card.cols" [rowspan]="card.rows">
      <mat-card class="dashboard-card">
        <mat-card-header>
          <mat-card-title>
            <button mat-icon-button class="more-button" [matMenuTriggerFor]="menu" aria-label="Toggle menu">
              <mat-icon>more_vert</mat-icon>
            </button>
            <mat-menu #menu="matMenu" xPosition="before">
+            <button mat-menu-item  (click)="changeDateRange('This year')">This year</button>
+            <button mat-menu-item  (click)="changeDateRange('Last year')">Last year</button>
            </mat-menu>
          </mat-card-title>
        </mat-card-header>
        <mat-card-content class="dashboard-card-content">
          <div>
            <app-bar-chart [query]="card.query" *ngIf="card.chart === 'bar'"></app-bar-chart>
          </div>
        </mat-card-content>
      </mat-card>
    </mat-grid-tile>
  </mat-grid-list>
</div>

Well done! 🎉 Here's what our dashboard application looks like:

KPI Chart

The KPI chart can be used to display business indicators that provide information about the current performance of our e-commerce company. The chart will consist of a grid of tiles, where each tile will display a single numeric KPI value for a certain category.

First, let's add the countUp package to add the count-up animation to the values on the KPI chart. Run the following command in the dashboard-app folder:

npm i ngx-countup @angular/material/progress-bar

We weed to import these modules:

+ import { CountUpModule } from 'ngx-countup';
+ import { MatProgressBarModule } from '@angular/material/progress-bar'

@NgModule({
  imports: [

//    ...

+    CountUpModule,
+    MatProgressBarModule

  ],
  ...
})

Second, let's add an array of cards we're going to display to the dashboard-page.component.ts file:

export class DashboardPageComponent implements OnInit {

// ...

+  public KPICards = [
+    {
+      title: 'ORDERS',
+      query: { measures: ['Orders.count'] },
+      difference: 'Orders',
+      duration: 1.25,
+    },
+    {
+      title: 'TOTAL USERS',
+      query: { measures: ['Users.count'] },
+      difference: 'Users',
+      duration: 1.5,
+    },
+    {
+      title: 'COMPLETED ORDERS',
+      query: { measures: ['Orders.percentOfCompletedOrders'] },
+      progress: true,
+      duration: 1.75,
+    },
+    {
+      title: 'TOTAL PROFIT',
+      query: { measures: ['LineItems.price'] },
+      duration: 2.25,
+    },
+  ];

// ...

}

The next step is create the KPI Card component. Run:

ng generate component kpi-card

Edit this component's code:

import { Component, Input, OnInit } from "@angular/core";
import { CubejsClient } from "@cubejs-client/ngx";

@Component({
  selector: 'app-kpi-card',
  templateUrl: './kpi-card.component.html',
  styleUrls: ['./kpi-card.component.scss']
})
export class KpiCardComponent implements OnInit {
  @Input() query: object;
  @Input() title: string;
  @Input() duration: number;
  @Input() progress: boolean;
  constructor(private cubejs:CubejsClient){}
  public result = 0;
  public postfix = null;
  public prefix = null;

  ngOnInit(): void {
    this.cubejs.load(this.query).subscribe(
      resultSet => {
        resultSet.series().map((s) => {
          this.result = s['series'][0]['value'].toFixed(1);
          const measureKey = resultSet.seriesNames()[0].key;
          const annotations = resultSet.tableColumns().find((tableColumn) => tableColumn.key === measureKey);
          const format = annotations.format || (annotations.meta && annotations.meta.format);
          if (format === 'percent') {
            this.postfix = '%';
          } else if (format === 'currency') {
            this.prefix = '$';
          }
        })
      },
      err => console.log('HTTP Error', err)
    );
  }

}

And the component's template:

<mat-card class="dashboard-card">
  <mat-card-header class="dashboard-card__header">
    <mat-card-title>
      <h3 class="kpi-title">{{title}}</h3>
    </mat-card-title>
  </mat-card-header>
  <mat-card-content class="dashboard-card-content kpi-result">
    <span>{{prefix}}</span>
    <span [countUp]="result" [options]="{duration: duration}">0</span>
    <span>{{postfix}}</span>
    <mat-progress-bar [color]="'primary'" class="kpi-progress" *ngIf="progress" value="{{result}}"></mat-progress-bar>
  </mat-card-content>
</mat-card>

The final step is to add this component to our dashboard page. To do so, open dashboard-page.component.html and replace the code:

<div class="grid-container">
  <div class="kpi-wrap">
    <mat-grid-list cols="4" rowHeight="131px">
      <mat-grid-tile *ngFor="let card of KPICards" [colspan]="1" [rowspan]="1">
        <app-kpi-card class="kpi-card"
                      [query]="card.query"
                      [title]="card.title"
                      [duration]="card.duration"
                      [progress]="card.progress"
        ></app-kpi-card>
      </mat-grid-tile>
    </mat-grid-list>
  </div>
  <div>
    <mat-grid-list cols="5" rowHeight="510px">
      <mat-grid-tile *ngFor="let card of cards" [colspan]="card.cols" [rowspan]="card.rows">
        <mat-card class="dashboard-card">
          <mat-card-header class="dashboard-card__header">
            <mat-card-title>
              <h3>Last sales</h3>
              <button mat-icon-button class="more-button" [matMenuTriggerFor]="menu" aria-label="Toggle menu">
                <mat-icon>more_vert</mat-icon>
              </button>
              <mat-menu #menu="matMenu" xPosition="before">
                <button mat-menu-item  (click)="changeDateRange('This year')">This year</button>
                <button mat-menu-item  (click)="changeDateRange('Last year')">Last year</button>
              </mat-menu>
            </mat-card-title>
          </mat-card-header>
          <mat-card-content class="dashboard-card-content">
            <div>
              <app-bar-chart [query]="card.query" *ngIf="card.chart === 'bar'"></app-bar-chart>
            </div>
          </mat-card-content>
        </mat-card>
      </mat-grid-tile>
    </mat-grid-list>
  </div>
</div>

The only thing left is to adjust the Cube.js schema. While doing it, we'll learn an important aspect of Cube.js...

Let's learn how to create custom measures in the data schema and display their values. In the e-commerce business, it's crucial to know the share of completed orders. To enable our users to monitor this metric, we'll want to display it on the KPI chart. So, we will modify the data schema by adding a custom measure (percentOfCompletedOrders) which will calculate the share based on another measure (completedCount).

Let's customize the "Orders" schema. Open the schema/Orders.js file in the root folder of the Cube.js project and make the following changes:

add the completedCount measure
add the percentOfCompletedOrders measure

cube(`Orders`, {
  sql: `SELECT * FROM public.orders`,

  // ...

  measures: {
    count: {
      type: `count`,
      drillMembers: [id, createdAt]
    },
    number: {
      sql: `number`,
      type: `sum`
    },
+    completedCount: {
+      sql: `id`,
+      type: `count`,
+      filters: [
+        { sql: `${CUBE}.status = 'completed'` }
+      ]
+    },
+    percentOfCompletedOrders: {
+      sql: `${completedCount} * 100.0 / ${count}`,
+      type: `number`,
+      format: `percent`
+    }
  },

  // ...

Great! 🎉 Now our dashboard has a row of nice and informative KPI metrics:

Doughnut Chart

Now, using the KPI chart, our users are able to monitor the share of completed orders. However, there are two more kinds of orders: "processed" orders (ones that were acknowledged but not yet shipped) and "shipped" orders (essentially, ones that were taken for delivery but not yet completed).

To enable our users to monitor all these kinds of orders, we'll want to add one final chart to our dashboard. It's best to use the Doughnut chart for that, because it's quite useful to visualize the distribution of a certain metric between several states (e.g., all kinds of orders).

First, let's create the DoughnutChart component. Run:

ng generate component doughnut-chart

Then edit the doughnut-chart.component.ts file:

import { Component, Input, OnInit } from "@angular/core";
import { CubejsClient } from "@cubejs-client/ngx";

@Component({
  selector: "app-doughnut-chart",
  templateUrl: "./doughnut-chart.component.html",
  styleUrls: ["./doughnut-chart.component.scss"]
})
export class DoughnutChartComponent implements OnInit {
  @Input() query: Object;

  public barChartOptions = {
    legend: {
      display: false
    },
    responsive: true,
    maintainAspectRatio: false,
    cutoutPercentage: 80,
    layout: { padding: 0 },
    tooltips: {
      enabled: true,
      mode: "index",
      intersect: false,
      borderWidth: 1,
      borderColor: "#eeeeee",
      backgroundColor: "#ffffff",
      titleFontColor: "#43436B",
      bodyFontColor: "#A1A1B5",
      footerFontColor: "#A1A1B5"
    }
  };

  public barChartLabels = [];
  public barChartType = "doughnut";
  public barChartLegend = true;
  public barChartData = [];
  public value = 0;
  public labels = [];

  constructor(private cubejs: CubejsClient) {
  }

  ngOnInit() {
    this.cubejs.load(this.query).subscribe(
      resultSet => {
        const COLORS_SERIES = ["#FF6492", "#F3F3FB", "#FFA2BE"];
        this.barChartLabels = resultSet.chartPivot().map((c) => c.category);
        this.barChartData = resultSet.series().map((s) => ({
          label: s.title,
          data: s.series.map((r) => r.value),
          backgroundColor: COLORS_SERIES,
          hoverBackgroundColor: COLORS_SERIES
        }));
        resultSet.series().map(s => {
          this.labels = s.series;
          this.value = s.series.reduce((sum, current) => {
            return sum.value ? sum.value + current.value : sum + current.value
          });
        });
      },
      err => console.log("HTTP Error", err)
    );
  }

}

And the template in the doughnut-chart.component.html file:

<div>
  <canvas baseChart
          height="215"
          [datasets]="barChartData"
          [labels]="barChartLabels"
          [options]="barChartOptions"
          [legend]="barChartLegend"
          [chartType]="barChartType">
  </canvas>
  <mat-grid-list cols="3">
    <mat-grid-tile *ngFor="let card of labels" [colspan]="1" [rowspan]="1">
      <div>
        <h3 class="doughnut-label">{{card.category}}</h3>
        <h2 class="doughnut-number">{{((card.value/value) * 100).toFixed(1)}}%</h2>
      </div>
    </mat-grid-tile>
  </mat-grid-list>
</div>

The next step is to add this card to the dashboard-page.component.ts file:

export class DashboardPageComponent implements OnInit {

// ...

+  private doughnutQuery = new BehaviorSubject({
+    measures: ['Orders.count'],
+    timeDimensions: [
+      {
+        dimension: 'Orders.createdAt',
+      },
+    ],
+    filters: [],
+    dimensions: ['Orders.status'],
+  });

  ngOnInit() {
    ...
+    this.doughnutQuery.subscribe(data => {
+      this.cards[1] = {
+        hasDatePick: false,
+        title: 'Users by Device',
+        chart: "doughnut", cols: 2, rows: 1,
+        query: data
+      };
+    });
  }
}

And the last step is to use this template in the dashboard-page.component.html file:

<div class="grid-container">

// ...

    <mat-grid-list cols="5" rowHeight="510px">
      <mat-grid-tile *ngFor="let card of cards" [colspan]="card.cols" [rowspan]="card.rows">
        <mat-card class="dashboard-card">
          <mat-card-header class="dashboard-card__header">
            <mat-card-title>
              <h3>{{card.title}}</h3>
+             <div *ngIf="card.hasDatePick">
                <button mat-icon-button class="more-button" [matMenuTriggerFor]="menu" aria-label="Toggle menu">
                  <mat-icon>more_vert</mat-icon>
                </button>
                <mat-menu #menu="matMenu" xPosition="before">
                  <button mat-menu-item  (click)="changeDateRange('This year')">This year</button>
                  <button mat-menu-item  (click)="changeDateRange('Last year')">Last year</button>
                </mat-menu>
+             </div>
            </mat-card-title>
          </mat-card-header>
          <mat-card-content class="dashboard-card-content">
            <div>
              <app-bar-chart [query]="card.query" *ngIf="card.chart === 'bar'"></app-bar-chart>
+              <app-doughnut-chart [query]="card.query" *ngIf="card.chart === 'doughnut'"></app-doughnut-chart>
            </div>
          </mat-card-content>
        </mat-card>
      </mat-grid-tile>
    </mat-grid-list>
  </div>
</div>

Awesome! 🎉 Now the first page of our dashboard is complete:

Multi-Page Dashboard with Data Table

Now we have a single-page dashboard that displays aggregated business metrics and provides at-a-glance view of several KPIs. However, there's no way to get information about a particular order or a range of orders.

We're going to fix it by adding a second page to our dashboard with the information about all orders. However, we'll need a way to navigate between two pages. So, let's add a navigation side bar.

Navigation Side Bar

Now we need a router, so let's add a module for this. Run:

ng generate module app-routing --flat --module=app

And then edit the app-routing.module.ts file:

import { NgModule } from '@angular/core';
import { RouterModule, Routes } from '@angular/router';
import { DashboardPageComponent } from './dashboard-page/dashboard-page.component';
import { TablePageComponent } from './table-page/table-page.component';

const routes: Routes = [
  { path: '', component: DashboardPageComponent },
  { path: 'table', component: TablePageComponent },
];

@NgModule({
  imports: [RouterModule.forRoot(routes)],
  exports: [RouterModule]
})
export class AppRoutingModule { }

Now we need to add new modules to the app.module.ts file:

// ...

import { CountUpModule } from 'ngx-countup';
import { DoughnutChartComponent } from './doughnut-chart/doughnut-chart.component';
+ import { AppRoutingModule } from './app-routing.module';
+ import { MatListModule } from '@angular/material/list';

// ...

    CountUpModule,
    MatProgressBarModule,
+    AppRoutingModule,
+    MatListModule,
  ],
  providers: [],
  bootstrap: [AppComponent]
})
export class AppModule { }

The last step is to set the app.component.html file to this code:

<style>
  * {
    box-sizing: border-box;
  }
  .toolbar {
    position: relative;
    top: 0;
    left: 0;
    right: 0;
    height: 60px;
    display: flex;
    align-items: center;
    background-color: #43436B;
    color: #D5D5E2;
    font-size: 16px;
    font-style: normal;
    font-weight: 400;
    line-height: 26px;
    letter-spacing: 0.02em;
    text-align: left;
    padding: 0 1rem;
  }
  .spacer {
    flex: 1;
  }

  .toolbar img {
    margin: 0 16px;
  }
  .root {
    width: 100%;
    display: flex;
    position: relative;
  }
  .component {
    width: 82.2%;
    min-height: 100vh;
    padding-top: 1rem;
    background: #F3F3FB;
  }
  .divider {
    width: 17.8%;
    background: #fff;
    padding: 1rem;
  }
  .nav-link {
    text-decoration: none;
    color: #A1A1B5;
  }
  .nav-link:hover .mat-list-item {
    background-color: rgba(67, 67, 107, 0.04);
  }
  .nav-link .mat-list-item {
    color: #A1A1B5;
  }
  .nav-link.active-link .mat-list-item {
    color: #7A77FF;
  }
</style>
<!-- Toolbar -->
<div class="toolbar" role="banner">
  <span>Angular Dashboard with Material</span>
  <div class="spacer"></div>
  <div class="links">
    <a
      aria-label="Cube.js on github"
      target="_blank"
      rel="noopener"
      href="https://github.com/cube-js/cube.js/tree/master/examples/angular-dashboard-with-material-ui"
      title="Cube.js on GitHub"
    >GitHub</a>
    <a
      aria-label="Cube.js on Slack"
      target="_blank"
      rel="noopener"
      href="https://slack.cube.dev/"
      title="Cube.js on Slack"
    >Slack</a>
  </div>
</div>
<div class="root">
  <div class="divider">
    <mat-list>
      <a class="nav-link"
         routerLinkActive="active-link"
         [routerLinkActiveOptions]="{exact: true}"
         *ngFor="let link of links" [routerLink]="[link.href]"
      >
        <mat-list-item>
          <mat-icon mat-list-icon>{{link.icon}}</mat-icon>
          <div mat-line>{{link.name}}</div>
        </mat-list-item>
      </a>
    </mat-list>
  </div>
  <div class="component">
    <router-outlet class="content"></router-outlet>
  </div>
</div>

To make everything finally work, let's add links to our app.component.ts:

import { Component } from '@angular/core';

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.scss'],
})
export class AppComponent {
+  public links = [
+    {name: 'Dashboard', href: '/', icon: 'dashboard'},
+    {name: 'Orders', href: '/table', icon: 'assignment'}
+    ];
  title = 'dashboard-app';
}

Wow! 🎉 Here's our navigation side bar which can be used to switch between different pages of the dashboard:

Data Table for Orders

To fetch data for the Data Table, we'll need to customize the data schema and define a number of new metrics: amount of items in an order (its size), an order's price, and a user's full name.

First, let's add the full name in the "Users" schema in the schema/Users.js file:

cube(`Users`, {
  sql: `SELECT * FROM public.users`,

    // ...

  dimensions: {    

        // ...

    firstName: {
      sql: `first_name`,
      type: `string`
    },

    lastName: {
      sql: `last_name`,
      type: `string`
    },

+    fullName: {
+      sql: `CONCAT(${firstName}, ' ', ${lastName})`,
+      type: `string`
+    },

    age: {
      sql: `age`,
      type: `number`
    },

    createdAt: {
      sql: `created_at`,
      type: `time`
    }
  }
});

Then, let's add other measures to the "Orders" schema in the schema/Orders.js file.

For these measures, we're going to use the subquery feature of Cube.js. You can use subquery dimensions to reference measures from other cubes inside a dimension. Here's how to defined such dimensions:

cube(`Orders`, {
  sql: `SELECT * FROM public.orders`,

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true,
+      shown: true
    },

    status: {
      sql: `status`,
      type: `string`
    },

    createdAt: {
      sql: `created_at`,
      type: `time`
    },

    completedAt: {
      sql: `completed_at`,
      type: `time`
    },

+    size: {
+      sql: `${LineItems.count}`,
+      subQuery: true,
+      type: 'number'
+    },
+
+    price: {
+      sql: `${LineItems.price}`,
+      subQuery: true,
+      type: 'number'
+    }
  }
});

Now we're ready to add a new page. Let's creating the table-page component. Run:

ng generate component table-page

Edit the table-page.module.ts file:

import { Component, OnInit } from '@angular/core';
import { BehaviorSubject } from "rxjs";

@Component({
  selector: 'app-table-page',
  templateUrl: './table-page.component.html',
  styleUrls: ['./table-page.component.scss']
})
export class TablePageComponent implements OnInit {
  public _query = new BehaviorSubject({
    "limit": 500,
    "timeDimensions": [
      {
        "dimension": "Orders.createdAt",
        "granularity": "day"
      }
    ],
    "dimensions": [
      "Users.id",
      "Orders.id",
      "Orders.size",
      "Users.fullName",
      "Users.city",
      "Orders.price",
      "Orders.status",
      "Orders.createdAt"
    ]
  });
  public query = {};

  constructor() { }

  ngOnInit(): void {
    this._query.subscribe(query => {
      this.query = query;
    });
  }

}

And set the template to these contents:

<div class="table-warp">
  <app-material-table [query]="query"></app-material-table>
</div>

Note that this component contains a Cube.js query. Later, we'll modify this query to enable the filtering of the data.

Also, let's create the material-table component. Run:

ng generate component material-table

Add it to the app.module.ts file:

+ import { MatTableModule } from '@angular/material/table'

  imports: [

// ...

+    MatTableModule
  ],
  providers: [],
  bootstrap: [AppComponent]
})
export class AppModule { }

And edit the material-table.module.ts file:

import { Component, OnInit, Input } from "@angular/core";
import { CubejsClient } from "@cubejs-client/ngx";

@Component({
  selector: "app-material-table",
  templateUrl: "./material-table.component.html",
  styleUrls: ["./material-table.component.scss"]
})
export class MaterialTableComponent implements OnInit {
  @Input() query: object;

  constructor(private cubejs: CubejsClient) {
  }
  public dataSource = [];
  displayedColumns = ['id', 'size', 'name', 'city', 'price', 'status', 'date'];

  ngOnInit(): void {
    this.cubejs.load(this.query).subscribe(
      resultSet => {
        this.dataSource = resultSet.tablePivot();
      },
      err => console.log("HTTP Error", err)
    );
  }

}

Then set its template to these contents:

<table style="width: 100%; box-shadow: none"
       mat-table
       matSort
       [dataSource]="dataSource"
       class="table mat-elevation-z8"
>

  <ng-container matColumnDef="id">
    <th mat-header-cell *matHeaderCellDef mat-sort-header> Order ID</th>
    <td mat-cell *matCellDef="let element"> {{element['Orders.id']}} </td>
  </ng-container>

  <ng-container matColumnDef="size">
    <th mat-header-cell *matHeaderCellDef> Orders size</th>
    <td mat-cell *matCellDef="let element"> {{element['Orders.size']}} </td>
  </ng-container>

  <ng-container matColumnDef="name">
    <th mat-header-cell *matHeaderCellDef> Full Name</th>
    <td mat-cell *matCellDef="let element"> {{element['Users.fullName']}} </td>
  </ng-container>

  <ng-container matColumnDef="city">
    <th mat-header-cell *matHeaderCellDef> User city</th>
    <td mat-cell *matCellDef="let element"> {{element['Users.city']}} </td>
  </ng-container>

  <ng-container matColumnDef="price">
    <th mat-header-cell *matHeaderCellDef> Order price</th>
    <td mat-cell *matCellDef="let element"> {{element['Orders.price']}} </td>
  </ng-container>

  <ng-container matColumnDef="status">
    <th mat-header-cell *matHeaderCellDef> Status</th>
    <td mat-cell *matCellDef="let element"> {{element['Orders.status']}} </td>
  </ng-container>

  <ng-container matColumnDef="date">
    <th mat-header-cell *matHeaderCellDef> Created at</th>
    <td mat-cell *matCellDef="let element"> {{element['Orders.createdAt'] | date}} </td>
  </ng-container>

  <tr mat-header-row *matHeaderRowDef="displayedColumns"></tr>
  <tr mat-row *matRowDef="let row; columns: displayedColumns;"></tr>
  <!--<mat-paginator [pageSizeOptions]="[5, 10, 25, 100]"></mat-paginator>-->
</table>

Time to add pagination!

Again, let's add modules to app.module.ts:

+ import {MatPaginatorModule} from "@angular/material/paginator";
+ import {MatProgressSpinnerModule} from "@angular/material/progress-spinner";

@NgModule({
  ...
  imports: [
+    MatPaginatorModule,
+    MatProgressSpinnerModule
  ],
  ...
})
export class AppModule { }

Then, let's edit the template:

+ <div class="example-loading-shade"
+      *ngIf="loading">
+   <mat-spinner></mat-spinner>
+ </div>

+ <div class="example-table-container">
  <table style="width: 100%; box-shadow: none"
         mat-table
         matSort
         [dataSource]="dataSource"
         class="table mat-elevation-z8"
  >

// ...

  </table>
+ </div>
+ <mat-paginator [length]="length"
+               [pageSize]="pageSize"
+               [pageSizeOptions]="pageSizeOptions"
+               (page)="pageEvent.emit($event)"
+ ></mat-paginator>

The styles...

/* Structure */
.example-container {
  position: relative;
  min-height: 200px;
}

.example-table-container {
  position: relative;
  max-height: 75vh;
  overflow: auto;
}

table {
  width: 100%;
}

.example-loading-shade {
  position: absolute;
  top: 0;
  left: 0;
  bottom: 56px;
  right: 0;
  background: rgba(0, 0, 0, 0.15);
  z-index: 1;
  display: flex;
  align-items: center;
  justify-content: center;
}

.example-rate-limit-reached {
  color: #980000;
  max-width: 360px;
  text-align: center;
}

/* Column Widths */
.mat-column-number,
.mat-column-state {
  max-width: 64px;
}

.mat-column-created {
  max-width: 124px;
}

.table th {
  background: #F8F8FC;
  color: #43436B;
  font-weight: 500;
  line-height: 1.5rem;
  border-bottom: 1px solid #eeeeee;
  &:hover {
    color: #7A77FF;
    cursor: pointer;
  }
}
.table thead {
  background: #F8F8FC;
}

And the component:

import { Component, Input, Output } from "@angular/core";
import { CubejsClient } from "@cubejs-client/ngx";
import { EventEmitter } from '@angular/core';

@Component({
  selector: "app-material-table",
  templateUrl: "./material-table.component.html",
  styleUrls: ["./material-table.component.scss"]
})
export class MaterialTableComponent {
  constructor(private cubejs: CubejsClient) {}
  @Input() set query(query: object) {
    this.loading = true;
    this.cubejs.load(query).subscribe(
      resultSet => {
        this.dataSource = resultSet.tablePivot();
        this.loading = false;
      },
      err => console.log("HTTP Error", err)
    );
    this.cubejs.load({...query, limit: 50000, offset: 0}).subscribe(
      resultSet => {
        this.length = resultSet.tablePivot().length;
      },
      err => console.log("HTTP Error", err)
    );
  };
  @Input() limit: number;
  @Output() pageEvent = new EventEmitter();
  loading = true;
  length = 0;
  pageSize = 10;
  pageSizeOptions: number[] = [5, 10, 25, 100];
  dataSource = [];
  displayedColumns = ['id', 'size', 'name', 'city', 'price', 'status', 'date'];
}

The last edits will be to the table-page-component.ts file:

import { Component, OnInit } from '@angular/core';
import { BehaviorSubject } from "rxjs";

@Component({
  selector: 'app-table-page',
  templateUrl: './table-page.component.html',
  styleUrls: ['./table-page.component.scss']
})
export class TablePageComponent implements OnInit {
  public limit = 50;
  public page = 0;
  public _query = new BehaviorSubject({
    "limit": this.limit,
    "offset": this.page * this.limit,
    "timeDimensions": [
      {
        "dimension": "Orders.createdAt",
        "granularity": "day"
      }
    ],
    "dimensions": [
      "Users.id",
      "Orders.id",
      "Orders.size",
      "Users.fullName",
      "Users.city",
      "Orders.price",
      "Orders.status",
      "Orders.createdAt"
    ],
    filters: []
  });
  public query = null;
  public changePage = (obj) => {
    this._query.next({
      ...this._query.value,
      "limit": obj.pageSize,
      "offset": obj.pageIndex * obj.pageSize,
    });
  };
  public statusChanged(value) {
    this._query.next({...this._query.value,
      "filters": this.getFilters(value)});
  };
  private getFilters = (value) => {
    return [
      {
        "dimension": "Orders.status",
        "operator": value === 'all' ? "set" : "equals",
        "values": [
          value
        ]
      }
    ]
  };

  constructor() { }

  ngOnInit(): void {
    this._query.subscribe(query => {
      this.query = query;
    });
  }

}

And the related template:

<div class="table-warp">
  <app-material-table [query]="query" [limit]="limit" (pageEvent)="changePage($event)"></app-material-table>
</div>

Voila! 🎉 Now we have a table which displays information about all orders:

However, its hard to explore this orders using only the controls provided. To fix this, we'll add a comprehensive toolbar with filters and make our table interactive.

For this, let's create the table-filters component. Run:

ng generate component table-filters

Set the module contents:

import { Component, EventEmitter, OnInit, Output } from "@angular/core";

@Component({
  selector: 'app-table-filters',
  templateUrl: './table-filters.component.html',
  styleUrls: ['./table-filters.component.scss']
})
export class TableFiltersComponent implements OnInit {
  @Output() statusChanged = new EventEmitter();
  statusChangedFunc = (obj) => {
    this.statusChanged.emit(obj.value);
  };

  constructor() { }

  ngOnInit(): void {
  }

}

And the template...

<mat-button-toggle-group class="table-filters"
                         (change)="statusChangedFunc($event)">
  <mat-button-toggle value="all">All</mat-button-toggle>
  <mat-button-toggle value="shipped">Shipped</mat-button-toggle>
  <mat-button-toggle value="processing">Processing</mat-button-toggle>
  <mat-button-toggle value="completed">Completed</mat-button-toggle>
</mat-button-toggle-group>

With styles...

.table-filters {
  margin-bottom: 2rem;
  .mat-button-toggle-appearance-standard {
    background: transparent;
    color: #43436b;
  }
}
.mat-button-toggle-standalone.mat-button-toggle-appearance-standard, .mat-button-toggle-group-appearance-standard.table-filters {
  border: none;
  -webkit-border-radius: 0;
  -moz-border-radius: 0;
  border-radius: 0;
  border-bottom: 1px solid #7A77FF;
}
.mat-button-toggle-checked {
  border-bottom: 2px solid #7A77FF;
}
.mat-button-toggle-group-appearance-standard .mat-button-toggle + .mat-button-toggle {
  border-left: none;
}

The last step will be to add it to the table-page.component.html file:

 <div class="table-warp">
+  <app-table-filters (statusChanged)="statusChanged($event)"></app-table-filters>
  <app-material-table [query]="query" [limit]="limit" (pageEvent)="changePage($event)"></app-material-table>
 </div>

Perfect! 🎉 Now the data table has a filter which switches between different types of orders:

However, orders have other parameters such as price and dates. Let's create filters for these parameters and enable sorting in the table.

Edit the table-filters component:

import { Component, EventEmitter, OnInit, Output } from "@angular/core";

@Component({
  selector: 'app-table-filters',
  templateUrl: './table-filters.component.html',
  styleUrls: ['./table-filters.component.scss']
})
export class TableFiltersComponent implements OnInit {
  @Output() statusChanged = new EventEmitter();
  @Output() dateChange = new EventEmitter();
  @Output() sliderChanged = new EventEmitter();

  statusChangedFunc = (obj) => {
    this.statusChanged.emit(obj.value);
  };
  changeDate(number, date) {
    this.dateChange.emit({number, date});
  };
  formatLabel(value: number) {
    if (value >= 1000) {
      return Math.round(value / 1000) + 'k';
    }
    return value;
  }
  sliderChange(value) {
    this.sliderChanged.emit(value);
  }

  constructor() { }

  ngOnInit(): void {
  }

}

And its template:

<mat-grid-list cols="4" rowHeight="131px">

  <mat-grid-tile>
    <mat-button-toggle-group class="table-filters"
                             (change)="statusChangedFunc($event)">
      <mat-button-toggle value="all">All</mat-button-toggle>
      <mat-button-toggle value="shipped">Shipped</mat-button-toggle>
      <mat-button-toggle value="processing">Processing</mat-button-toggle>
      <mat-button-toggle value="completed">Completed</mat-button-toggle>
    </mat-button-toggle-group>
  </mat-grid-tile>

  <mat-grid-tile>
    <mat-form-field class="table-filters__date-form" color="primary" (change)="changeDate(0, $event)">
      <mat-label>Start date</mat-label>
      <input #ref matInput [matDatepicker]="picker1" (dateChange)="changeDate(0, ref.value)">
      <mat-datepicker-toggle matSuffix [for]="picker1"></mat-datepicker-toggle>
      <mat-datepicker #picker1></mat-datepicker>
    </mat-form-field>
  </mat-grid-tile>

  <mat-grid-tile>
    <mat-form-field class="table-filters__date-form" color="primary" (change)="changeDate(1, $event)">
      <mat-label>Finish date</mat-label>
      <input #ref1 matInput [matDatepicker]="picker2" (dateChange)="changeDate(1, ref1.value)">
      <mat-datepicker-toggle matSuffix [for]="picker2"></mat-datepicker-toggle>
      <mat-datepicker #picker2></mat-datepicker>
    </mat-form-field>
  </mat-grid-tile>

  <mat-grid-tile>
    <div>
      <mat-label class="price-label">Price range</mat-label>
      <mat-slider
        color="primary"
        thumbLabel
        (change)="sliderChange($event)"
        [displayWith]="formatLabel"
        tickInterval="10"
        min="1"
        max="1200"></mat-slider>
    </div>
  </mat-grid-tile>
</mat-grid-list>

Again, add plenty of modules to the app.module.ts file:

// ...

import { TableFiltersComponent } from "./table-filters/table-filters.component";
import { MatButtonToggleModule } from "@angular/material/button-toggle";
+ import { MatDatepickerModule } from "@angular/material/datepicker";
+ import { MatFormFieldModule } from "@angular/material/form-field";
+ import { MatNativeDateModule } from "@angular/material/core";
+ import { MatInputModule } from "@angular/material/input";
+ import {MatSliderModule} from "@angular/material/slider";

// ...

    MatProgressSpinnerModule,
    MatButtonToggleModule,
+    MatDatepickerModule,
+    MatFormFieldModule,
+    MatNativeDateModule,
+    MatInputModule,
+    MatSliderModule
  ],
+  providers: [MatDatepickerModule],
  bootstrap: [AppComponent]
})
export class AppModule {
}

Edit the table-page.component.html file:

 <div class="table-warp">
  <app-table-filters (statusChanged)="statusChanged($event)"
                     (dateChange)="dateChanged($event)"
                     (sliderChanged)="sliderChanged($event)"
  ></app-table-filters>
  <app-material-table [query]="query"
                      [limit]="limit"
                      (pageEvent)="changePage($event)"
+                      (sortingChanged)="sortingChanged($event)"></app-material-table>
 </div>

And the table-page component:

import { Component, OnInit } from "@angular/core";
import { BehaviorSubject } from "rxjs";

@Component({
  selector: "app-table-page",
  templateUrl: "./table-page.component.html",
  styleUrls: ["./table-page.component.scss"]
})
export class TablePageComponent implements OnInit {
...
+  public limit = 50;
+  public page = 0;
+  public sorting = ['Orders.createdAt', 'desc'];
+  public startDate = "01/1/2019";
+  public finishDate = "01/1/2022";
+  private minPrice = 0;
  public _query = new BehaviorSubject({
+    "limit": this.limit,
+    "offset": this.page * this.limit,
+    order: {
+      [`${this.sorting[0]}`]: this.sorting[1],
+    },
+    "timeDimensions": [
+      {
+        "dimension": "Orders.createdAt",
+        "dateRange" : [this.startDate, this.finishDate],
+        "granularity": "day"
+      }
+    ],
    "dimensions": [
      "Users.id",
      "Orders.id",
      "Orders.size",
      "Users.fullName",
      "Users.city",
      "Orders.price",
      "Orders.status",
      "Orders.createdAt"
    ],
+    filters: []
  });
+  public changePage = (obj) => {
+    this._query.next({
+      ...this._query.value,
+      "limit": obj.pageSize,
+      "offset": obj.pageIndex * obj.pageSize
+    });
+  };

+  public sortingChanged(value) {
+    if (value === this.sorting[0] && this.sorting[1] === 'desc') {
+      this.sorting[0] = value;
+      this.sorting[1] = 'asc'
+    } else if (value === this.sorting[0] && this.sorting[1] === 'asc') {
+      this.sorting[0] = value;
+      this.sorting[1] = 'desc'
+    } else {
+      this.sorting[0] = value;
+    }
+    this.sorting[0] = value;
+    this._query.next({
+      ...this._query.value,
+      order: {
+        [`${this.sorting[0]}`]: this.sorting[1],
+      },
+    });
+  }

+  public dateChanged(value) {
+    if (value.number === 0) {
+      this.startDate = value.date
+    }
+    if (value.number === 1) {
+      this.finishDate = value.date
+    }
+    this._query.next({
+      ...this._query.value,
+      timeDimensions: [
+        {
+          dimension: "Orders.createdAt",
+          dateRange: [this.startDate, this.finishDate],
+          granularity: null
+        }
+      ]
+    });
+  }

+  public statusChanged(value) {
+    this.status = value;
+    this._query.next({
+      ...this._query.value,
+      "filters": this.getFilters(this.status, this.minPrice)
+    });
+  };

+  public sliderChanged(obj) {
+    this.minPrice = obj.value;
+    this._query.next({
+      ...this._query.value,
+      "filters": this.getFilters(this.status, this.minPrice)
+    });
+  };

+  private getFilters = (status, price) => {
+    let filters = [];
+    if (status) {
+      filters.push(
+        {
+          "dimension": "Orders.status",
+          "operator": status === "all" ? "set" : "equals",
+          "values": [
+            status
+          ]
+        }
+      );
+    }
+    if (price) {
+      filters.push(
+        {
+          dimension: 'Orders.price',
+          operator: 'gt',
+          values: [`${price}`],
+        },
+      );
+    }
+    return filters;
+  };

 ...
}

Now we need to propagate the changes to the material-table component:

// ...

export class MaterialTableComponent {

// ...

+  @Output() sortingChanged = new EventEmitter();

// ...

+  changeSorting(value) {
+    this.sortingChanged.emit(value)
+  }
}

And its template:

// ...

    <ng-container matColumnDef="id">
      <th matSort mat-header-cell *matHeaderCellDef mat-sort-header 
+.    (click)="changeSorting('Orders.id')"> Order ID</th>
      <td mat-cell *matCellDef="let element"> {{element['Orders.id']}} </td>
    </ng-container>

    <ng-container matColumnDef="size">
      <th mat-header-cell *matHeaderCellDef mat-sort-header 
+     (click)="changeSorting('Orders.size')"> Orders size</th>
      <td mat-cell *matCellDef="let element"> {{element['Orders.size']}} </td>
    </ng-container>

    <ng-container matColumnDef="name">
      <th mat-header-cell *matHeaderCellDef mat-sort-header 
+     (click)="changeSorting('Users.fullName')"> Full Name</th>
      <td mat-cell *matCellDef="let element"> {{element['Users.fullName']}} </td>
    </ng-container>

    <ng-container matColumnDef="city">
      <th mat-header-cell *matHeaderCellDef mat-sort-header 
+    (click)="changeSorting('Users.city')"> User city</th>
      <td mat-cell *matCellDef="let element"> {{element['Users.city']}} </td>
    </ng-container>

    <ng-container matColumnDef="price">
      <th mat-header-cell *matHeaderCellDef mat-sort-header 
+    (click)="changeSorting('Orders.price')"> Order price</th>
      <td mat-cell *matCellDef="let element"> {{element['Orders.price']}} </td>
    </ng-container>

    <ng-container matColumnDef="status">
      <th mat-header-cell *matHeaderCellDef mat-sort-header 
+    (click)="changeSorting('Orders.status')"> Status</th>
      <td mat-cell *matCellDef="let element"> {{element['Orders.status']}} </td>
    </ng-container>

    <ng-container matColumnDef="date">
      <th mat-header-cell *matHeaderCellDef mat-sort-header 
+     (click)="changeSorting('Orders.createdAt')"> Created at</th>
      <td mat-cell *matCellDef="let element"> {{element['Orders.createdAt'] | date}} </td>
    </ng-container>

// ...

Wonderful! 🎉 Now we have the data table that fully supports filtering and sorting:

And that's all! 😇 Congratulations on completing this guide! 🎉

Also, check the live demo and the full source code available on GitHub.

Now you should be able to create comprehensive analytical dashboards powered by Cube.js using Angular and Material to display aggregate metrics and detailed information.

Feel free to explore other examples of what can be done with Cube.js such as the Real-Time Dashboard Guide and the Open Source Web Analytics Platform Guide.

Vue Query Builder with Cube.js

Leonid Yakovlev — Thu, 16 Jul 2020 14:01:10 +0000

Quite commonly in our applications, we need to create interactive report builders to let users build custom reports and dashboards. This usually involves selecting the metrics, groupings, date ranges, filters, and chart types. To help developers build such interactive components, we've created a query builder component in Cube.js client libraries.

We've already covered how to use it in the React client library, and in this blog post, we'll talk about using it with Vue.js. The query builder component uses the scoped slots technique and lets developers implement their own render logic. This way it gives maximum flexibility for building a custom-tailored UI with minimal API. Below you can see the demo of the query builder component with Vuetify.

You can find the live demo of the example here and its source code is available on Github.

Setup a Demo Backend

if you already have Cube.js backend up and running you can skip this step.

Let's start by setting up a database with some sample data. We'll use PostgreSQL and our example e-commerce dataset for this tutorial. You can download and import it by running the following commands.

$ curl <http://cube.dev/downloads/ecom-dump.sql> > ecom-dump.sql
$ createdb ecom
$ psql --dbname ecom -f ecom-dump.sql

Next, install the Cube.js CLI if you don't have it already, and create a new project.

$ npm install -g cubejs-cli
$ cubejs create -d postgres vue-query-builder

Cube.js uses environment variables for configuration, which starts with CUBEJS_. To configure the connection to our database, we need to specify the DB type and name. In the Cube.js project folder replace the contents of .env file with the following:

CUBEJS_DB_NAME=ecom
CUBEJS_DB_TYPE=postgres
CUBEJS_API_SECRET=SECRET

Now that we have everything configured, let's start the Cube.js development server with the following command.

$ npm run dev

Navigate to http://localhost:4000 in your browser to access the Cube.js Playground. It is a development environment that generates the Cube.js schema, creates scaffolding for charts, and lets you test your queries and debug them. It also has its own query builder, which lets you generate charts with different charting libraries.

Now let’s move on to building our own query builder with Vue.js! 💻

Building a Query Builder

We're going to use Vue CLI to generate a new project. Run the following command to install Vue CLI if you don't have it already.

$ npm install -g @vue/cli

To a create a new project with Vue CLI run the following command inside the Cube.js project folder.

$ vue create dashboard-app

To render the UI for query builder we are going to use Vuetify, a Vue UI Library with material components. Let's add it to our project. Run the following command inside the dashboard-app folder.

$ vue add vuetify

To create our color scheme you can open src/plugins/vuetify.js and add this code:

import Vue from 'vue'
import Vuetify from 'vuetify/lib'

Vue.use(Vuetify)

export default new Vuetify({
+  theme: {
+    themes: {
+      light: {
+        primary: '#7A77FF',
+      },
+    },
+  },
})

Now we can start our frontend application.

$ yarn serve

You can check your newly created Vue app at http://localhost:8080. Next, let's install dependencies we'll need for building our Query Builder: Cube.js Vue client, Vue Chartkick, and Chart.js.

$ npm install --save vue-chartkick chart.js core-js @cubejs-client/core @cubejs-client/vue

Let's create a first simple Query Builder to allow users to select the metric from the dropdown list and then render it as a line chart over time.

Cube.js Query Builder component can load the list of available measures based on the data schema from the backend. We can access these measures as an availableMeasures slot prop. We'll render that list with the v-select component from Vuetify. Then when the user selects the measure, we're going to use a setMeasures slot prop to update the measures in our query and finally render the query result as a line chart with Vue Chartkick and Chart.js.

You can learn more about other slot props in the Query Builder component in the docs.

Replace the content of the src/HelloWord.vue file with the following.

<template>
  <v-container fluid class="text-center background pa-0">
    <query-builder :cubejs-api="cubejsApi" :query="query" style="width: 100%">
      <template v-slot:builder="{measures,setMeasures,availableMeasures}">
        <v-container fluid class="pa-4 pa-md-8 pt-6 background-white">
          <v-row>
            <v-col cols="12" md="2">
              <v-select
                multiple
                label="Measures"
                outlined
                hide-details
                :value="measures.map(i => (i.name))"
                @change="setMeasures"
                :items="availableMeasures.map(i => (i.name))"
              />
            </v-col>
          </v-row>
        </v-container>
      </template>
      <template v-slot="{ resultSet }">
        <div class="pa-4 pa-md-8" v-if="resultSet">
          <div class="border-light pa-4 pa-md-12">
            <line-chart legend="bottom" :data="series(resultSet)"></line-chart>
          </div>
        </div>
      </template>
    </query-builder>
  </v-container>
</template>

<script>
import cubejs from '@cubejs-client/core'
import { QueryBuilder } from '@cubejs-client/vue'

const cubejsApi = cubejs("CUBEJS-TOKEN", {
  apiUrl: `http://localhost:4000/cubejs-api/v1`
});

export default {
  name: 'HelloWorld',

  components: {
    QueryBuilder
  },
  data: () => {
    const query = {
      limit: 100,
      timeDimensions: [
        {
          dimension: 'LineItems.createdAt',
          granularity: 'day',
          dateRange: 'Last 30 days'
        }
      ],
    }

    return {
      cubejsApi,
      query
    }
  },
  methods: {
    series (resultSet) {
      return resultSet.series().map(series => ({
        name: series.key,
        data: series.series.map(row => [row.x, row.value])
      }))
    }
  }
}
</script>

<style scopped>
  .background {
    background: #F3F3FB;
    min-height: 100vh;
  }
  .background-white {
    background: #fff;
  }
  .border-light {
    background: #FFFFFF;
    border-radius: 8px;
  }
</style>

Now we can use updated HelloWorld component in our App component. Replace the content of the src/App.vue with the following.

<template>
  <v-app>
    <v-app-bar
      app
      color="#43436B"
      dark
    >
      <div class="d-flex align-center">
        <v-img
          alt="Vuetify Logo"
          class="shrink mr-2"
          contain
          src="<https://cube.dev/downloads/logo-full.svg>"
          transition="scale-transition"
        />
      </div>
    </v-app-bar>

    <v-main>
      <HelloWorld/>
    </v-main>
  </v-app>
</template>

<script>
import HelloWorld from './components/HelloWorld'

export default {
  name: 'App',

  components: {
    HelloWorld
  }
}
</script>

The last small change we need to make is to register the VueChartkick plugin. Update the src/main.js file.

import Vue from 'vue'
import App from './App.vue'
import vuetify from './plugins/vuetify'
+ import Chart from 'chart.js'
+ import VueChartkick from 'vue-chartkick'

Vue.config.productionTip = false
+ Vue.use(VueChartkick, { adapter: Chart })

new Vue({
  vuetify,
  render: h => h(App)
}).$mount('#app')

We've just built our first simple query builder 🎉. Navigate to http://localhost:8080/ in your browser and you should be able to test it out.

We can already plot the count of orders over time. But what if we want to see the breakdown of orders by the status? To do so, we'd need to introduce the dimensions dropdown to let users select the grouping option.

We'll use more slot props for this: dimensions, availableDimensions and setDimensions. They work they same as slot props for measures allowing us to list the available dimensions and update the list of selected ones.

Update src/HelloWorld.vue file.

<template>
  <v-container fluid class="text-center background pa-0">
    <query-builder :cubejs-api="cubejsApi" :query="query" style="width: 100%">
-    <template v-slot:builder="{measures,setMeasures,availableMeasures}"
+    <template 
+       v-slot:builder="{
+       measures,
+       setMeasures,
+       availableMeasures,
+       dimensions,
+       setDimensions,
+       availableDimensions
+       }"
+     >
        <v-container fluid class="pa-4 pa-md-8 pt-6 background-white">
            <v-row>
              <v-col cols="12" md="2">
                <v-select
                  multiple
                  label="Measures"
                  outlined
                  hide-details
                  :value="measures.map(i => (i.name))"
                  @change="setMeasures"
                  :items="availableMeasures.map(i => (i.name))"
                />
              </v-col>
+             <v-col cols="12" md="2">
+               <v-select
+                 multiple
+                 label="Dimensions"
+                 outlined
+                 hide-details
+                 :value="dimensions.map(i => (i.name))"
+                 @change="setDimensions"
+                 :items="availableDimensions.map(i => (i.name))"
+               />
+             </v-col>
            </v-row>
          </v-container>
        </template>
        <template v-slot="{ resultSet }">
            <div class="pa-4 pa-md-8" v-if="resultSet">
              <div class="border-light pa-4 pa-md-12">
                <line-chart legend="bottom" :data="series(resultSet)"></line-chart>
              </div>
            </div>
          </template>
      </query-builder>
  </v-container>
</template>

Refresh your browser and now you should be able to select the dimensions for grouping as well!

That's all for this tutorial. Congratulations on completing it! 🎉

There are other controls you can add to your query builder like filters, date range, and granularity, as well as chart type selector. You can find a more complicated example with all these controls here and its source code here on Github.

Please send any comments or feedback you might have to this Slack Community. Thank you and I hope you found this tutorial helpful!