Medusa Plugin: Relation Filter

Disclaimer: This plugin is a work in progress and was primarily developed for our internal use cases. While we are happy to share it with the community, we do not offer official support. We welcome contributions and pull requests!

A Medusa V2 plugin that provides a powerful service for performing complex, multi-level filtering and searching on entity relationships directly against the database.

This plugin is designed to work alongside Medusa's core query module, acting as a highly efficient pre-filtering step for complex scenarios.

Why Use This Plugin?

Filtering by deeply nested relationships in Medusa can be challenging. The standard approach often requires fetching large sets of data and performing intersections in your application layer, leading to the classic N+1 query problem and inefficient data handling.

For example, to find products made of "Organic Cotton" that are also "OEKO-TEX" certified, you might have to:

1. Query for all "OEKO-TEX" certifications.
2. Query for all "Organic Cotton" materials linked to those certifications.
3. Finally, query for all products linked to those materials.

This plugin solves this by offloading the complex filtering logic to a single, efficient database query. It uses a simple, two-step process:

1. Filter for IDs: Use the RelationFilterService to run a single, complex query that returns only the IDs of the matching top-level entities (e.g., product IDs).
2. Hydrate with remoteQuery: Use the returned IDs to fetch the full, structured data with Medusa's standard remoteQuery.

This approach is significantly more performant, avoids the N+1 problem, and keeps your application logic clean.

A Note on Performance & Alternatives

This plugin achieves its filtering capabilities by constructing SQL queries with multiple JOIN statements. While this is highly efficient for many use cases, performance can be impacted when filtering across a very large number of different tables (relations). For optimal performance, it is recommended to apply appropriate database indexing on the foreign keys and columns you filter by.

For enterprise-level search or extremely complex filtering scenarios, you might consider a dedicated search engine. These tools are purpose-built for high-performance searching and faceting:

• Meilisearch: The medusa-plugin-meilisearch is an excellent choice for integrating a fast, dedicated search service.
• Medusa's Index Module: Medusa's native Index Module provides another powerful way to handle search and indexing within the Medusa ecosystem.

This plugin serves as an excellent middle-ground, offering powerful relational filtering directly within your database without the need for additional infrastructure.

Features

• Solves the N+1 Problem: Eliminates the need for multiple queries and manual data intersection for complex filters.
• Multi-Level Relational Filtering: Filter entities based on the properties of their direct or linked relations (e.g., product -> variants -> attribute_values).
• Complex Logic: Supports $and and $or operators to build sophisticated filtering logic.
• Performance-Oriented: Returns only the IDs and count of matching entities, avoiding heavy data fetching until you need it. This is perfect for pagination.
• Dynamic Schema Awareness: Automatically discovers the relationships between your Medusa modules, including custom links, to build its query schema.
• Integrated Search: Supports a top-level q parameter for free-text search on the primary entity's searchable fields. Of course. Highlighting the developer experience benefits like type safety and autocomplete is a great idea.

Type Safety & Autocomplete

This plugin is built with TypeScript in mind to provide a superior developer experience. It extends Medusa's core RemoteQueryEntryPoints type graph, giving you full type safety and autocompletion directly in your code editor.

• Field Autocompletion: Get intelligent suggestions for all available fields and relations as you build your filters object.
• Operator Safety: Your editor will suggest valid filter operators ($in, $gt, $like, etc.) for each field.
• Prevent Typos: Catch errors at compile-time, not runtime. TypeScript will immediately flag any attempt to filter on a field that doesn't exist.

await relationFilterService.query({
  entity: "product",
  filters: {
    // IDE suggests 'status', 'handle', 'created_at', etc.
    status: "published",
    variants: {
      // IDE suggests 'sku', 'title', etc. on the variant relation
      sku: {
        // IDE suggests '$in', '$like', '$gt', etc.
        $like: "VIP-",
      },
    },
  },
});

Prerequisites

• A Medusa V2 project.

Installation

1. Install the plugin in your Medusa project:

   npm install medusa-plugin-relation-filter

2. Add the plugin to your medusa-config.ts:

   import { defineConfig } from "@medusajs/framework";
   // ... other imports
   
   export default defineConfig({
     // ...
     plugins: [
       // ... other plugins
       `medusa-plugin-relation-filter`,
     ],
     // ...
   });

Usage

The core of this plugin is the RelationFilterService. You use it to get a list of IDs that match your complex criteria, and then you hydrate those IDs into full objects using Medusa's remoteQuery.

Core Service Import

You can access the service and its registration key from your API Routes or other services:

import {
  RELATION_FILTER_MODULE,
  RelationFilterService,
} from "medusa-plugin-relation-filter/modules/relation-filter";

Basic Example: Filtering Products by Variant SKU

Here is a simple example of an API route that finds all products containing a variant with a specific SKU.

src/api/store/simple-filter/route.ts

import { MedusaRequest, MedusaResponse } from "@medusajs/framework";
import { HttpTypes } from "@medusajs/framework/types";
import {
  ContainerRegistrationKeys,
  remoteQueryObjectFromString,
} from "@medusajs/framework/utils";
import {
  RELATION_FILTER_MODULE,
  RelationFilterService,
} from "medusa-plugin-relation-filter/modules/relation-filter";

export const GET = async (
  req: MedusaRequest,
  res: MedusaResponse<HttpTypes.StoreProductListResponse>
) => {
  const remoteQuery = req.scope.resolve(ContainerRegistrationKeys.REMOTE_QUERY);
  const relationFilterService: RelationFilterService = req.scope.resolve(
    RELATION_FILTER_MODULE
  );

  // 1. Get IDs of products that match the nested filter
  const { ids, count } = await relationFilterService.query({
    entity: "product",
    filters: {
      variants: {
        sku: "variant-sku-123",
      },
    },
    options: {
      pagination: { take: 20, skip: 0 },
    },
  });

  // 2. Fetch the full data for only the matching IDs
  let products: HttpTypes.StoreProduct[] = [];
  if (ids.length > 0) {
    const queryObject = remoteQueryObjectFromString({
      entryPoint: "product",
      variables: { filters: { id: ids } },
      fields: ["id", "title", "handle", "variants.id", "variants.sku"],
    });
    products = await remoteQuery(queryObject);
  }

  res.json({
    products,
    count,
    offset: 0,
    limit: 20,
  });
};

Advanced Example: Filtering by a Custom Module

Imagine you have a custom MaterialModule linked to your products. A material can also have a Certification.

• Product <-> Material (Many-to-Many)
• Material <-> Certification (Many-to-Many)

Goal: Find all products made of "Organic Cotton" that are also "OEKO-TEX" certified.

This requires filtering across two levels of relationships.

src/api/store/certified-products/route.ts

import { MedusaRequest, MedusaResponse } from "@medusajs/framework";
import { HttpTypes } from "@medusajs/framework/types";
import {
  ContainerRegistrationKeys,
  remoteQueryObjectFromString,
} from "@medusajs/framework/utils";
import {
  RELATION_FILTER_MODULE,
  RelationFilterService,
} from "medusa-plugin-relation-filter/modules/relation-filter";

export const GET = async (
  req: MedusaRequest,
  res: MedusaResponse<HttpTypes.StoreProductListResponse>
) => {
  const remoteQuery = req.scope.resolve(ContainerRegistrationKeys.REMOTE_QUERY);
  const relationFilterService: RelationFilterService = req.scope.resolve(
    RELATION_FILTER_MODULE
  );

  // 1. Get IDs of products that match the deeply nested filter
  const { ids, count } = await relationFilterService.query({
    entity: "product",
    filters: {
      materials: {
        // Filter on the 'materials' relation
        $and: [
          // The material must match BOTH conditions
          { name: "Organic Cotton" },
          {
            certifications: {
              // Filter on the nested 'certifications' relation
              name: "OEKO-TEX",
            },
          },
        ],
      },
    },
    options: {
      pagination: { take: 50, skip: 0 },
    },
  });

  // 2. Fetch the full data for only the matching IDs
  let products: HttpTypes.StoreProduct[] = [];
  if (ids.length > 0) {
    const queryObject = remoteQueryObjectFromString({
      entryPoint: "product",
      variables: { filters: { id: ids } },
      fields: req.queryConfig.fields, // Use fields from middleware query config
    });
    products = await remoteQuery(queryObject);
  }

  res.json({
    products,
    count,
    offset: 0,
    limit: 50,
  });
};

How It Works

The plugin operates in two main phases:

1. Schema Building: On the first request, the RelationFilterService inspects the joiner configurations of all loaded modules in your Medusa application. It builds an in-memory graph of how all entities are connected, whether through direct foreign keys or through link modules. This schema is then cached.
2. SQL Query Construction: When you call the query method, the service traverses your nested filter object and uses the schema to identify all necessary table joins. It then constructs a single, efficient SQL query to find the matching primary entity IDs and the total count.

This approach allows for highly flexible and performant filtering without requiring you to write complex SQL or remoteQuery objects manually.

API Reference

RelationFilterService.query(args)

Executes a complex query and returns matching entity IDs and a count.

Parameters:

• args (object): An object containing the query parameters.
  • entity (string, required): The snake_case name of the primary entity to query (e.g., "product").
  • filters (object, optional): A nested object of filters to apply. Supports standard Medusa operators ($gt, $in, etc.) and logical operators ($and, $or).
  • options (object, optional): Pagination and ordering options.
    • pagination (object, optional):
      • skip (number): The number of items to skip.
      • take (number): The number of items to retrieve.
    • orderBy (object, optional): An object where keys are field paths and values are 'ASC' or 'DESC'.

Returns:

• Promise<object>: A promise that resolves to an object containing:
  • ids (string[]): An array of the matching entity IDs.
  • count (number): The total number of entities that match the filter criteria.