Skip to content

🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.

1.6 KB
Raw Blame
import { FastifyReply, FastifyRequest } from "fastify";
import { getTimeStatement } from "../utils/timeWindow.js";
import { FilterParams } from "@rybbit/shared";
import { getFilterStatement } from "../utils/getFilterStatement.js";
import { analyticsRoute, runAnalyticsQuery } from "../utils/analyticsQuery.js";

export type GetOutboundLinksResponse = {
  url: string;
  count: number;
  lastClicked: string;
}[];

export interface GetOutboundLinksRequest {
  Params: {
    siteId: string;
  };
  Querystring: FilterParams<{}>;
}

export const buildOutboundLinksQuery = (query: GetOutboundLinksRequest["Querystring"], siteId: number) => {
  const { filters } = query;

  const timeStatement = getTimeStatement(query);
  const filterStatement = filters
    ? getFilterStatement(filters, siteId, timeStatement, { sessionLevelParams: ["channel"] })
    : "";

  return `
    SELECT
      JSONExtractString(toString(props), 'url') AS url,
      COUNT(*) AS count,
      toString(MAX(timestamp)) AS lastClicked
    FROM events
    WHERE
      site_id = {siteId:Int32}
      AND type = 'outbound'
      ${timeStatement}
      ${filterStatement}
    GROUP BY url
    ORDER BY count DESC
    LIMIT 1000
  `;
};

export const getOutboundLinks = analyticsRoute<GetOutboundLinksRequest>(
  "outbound links",
  async (req: FastifyRequest<GetOutboundLinksRequest>, res: FastifyReply) => {
    const site = req.params.siteId;

    const data = await runAnalyticsQuery<GetOutboundLinksResponse[number]>({
      query: buildOutboundLinksQuery(req.query, Number(site)),
      params: { siteId: Number(site) },
    });

    return res.send({ data });
  }
);