TanStack•4mo ago•

2 replies

verbal-lime

How to filter where array field contains a value?

I need to filter records where an array field contains a specific value. The

inArray()

inArray()

operator works the opposite way (checking if a value is in a literal array).

I could use

fn.where()

fn.where()

, but this wont work on

on-demand

on-demand

data, as far as I understand.

Use case:

Data Structure

Database: 3 tables with many-to-many relationship

article (id, content)
tag (id, name)
article_tags (article_id, tag_id)  -- junction table

article (id, content)
tag (id, name)
article_tags (article_id, tag_id)  -- junction table

API Response: I am using query collections.

// GET /api/tags
{ id: number; name: string }[]

// GET /api/articles
{ id: number; content: string; tags: number[]; }[]

// GET /api/tags
{ id: number; name: string }[]

// GET /api/articles
{ id: number; content: string; tags: number[]; }[]

The Problem

Goal: Filter articles where

tags

tags

array contains a specific tag ID.

Current approach (what I'm doing now):

// Using fn.where() to filter client-side
const filtered = createLiveQueryCollection((q) =>
  q
    .from({ article: articlesCollection })
    .fn.where((row) => row.article.tags.includes(selectedTagId)),
);

// Using fn.where() to filter client-side
const filtered = createLiveQueryCollection((q) =>
  q
    .from({ article: articlesCollection })
    .fn.where((row) => row.article.tags.includes(selectedTagId)),
);

Issue: Articles collection is on-demand.

fn.where()

fn.where()

runs client-side after fetching ALL data, defeating the purpose. Need expression-based operator compiled to backend query.

Potential Solutions

Option 1: Should there be an

arrayContains()/arrayIncludes()

arrayContains()/arrayIncludes()

operator?

q.from({ article: articlesCollection }).where(({ article }) =>
  arrayContains(article.tags, selectedTagId),
);

q.from({ article: articlesCollection }).where(({ article }) =>
  arrayContains(article.tags, selectedTagId),
);

Option 2: Use junction table with joins instead of embedded array?
TanStack DB mimics tabular data, maybe the solution is to work with the actual junction table instead of the embedded array?

q.from({ article: articlesCollection })
  .join({ articleTag: articleTagsCollection }, ({ article, articleTag }) =>
    eq(article.id, articleTag.articleId),
  )
  .where(({ articleTag }) => eq(articleTag.tagId, selectedTagId))
  .select(({ article }) => ({ ...article }))
  .distinct();

q.from({ article: articlesCollection })
  .join({ articleTag: articleTagsCollection }, ({ article, articleTag }) =>
    eq(article.id, articleTag.articleId),
  )
  .where(({ articleTag }) => eq(articleTag.tagId, selectedTagId))
  .select(({ article }) => ({ ...article }))
  .distinct();

Option 3: Something else, maybe I am missing something...

TanStack•4mo ago•

2 replies

verbal-lime

How to filter where array field contains a value?

I need to filter records where an array field contains a specific value. The

inArray()

inArray()

operator works the opposite way (checking if a value is in a literal array).

I could use

fn.where()

fn.where()

, but this wont work on

on-demand

on-demand

data, as far as I understand.

Use case:

Data Structure

Database: 3 tables with many-to-many relationship

article (id, content)
tag (id, name)
article_tags (article_id, tag_id)  -- junction table

article (id, content)
tag (id, name)
article_tags (article_id, tag_id)  -- junction table

API Response: I am using query collections.

// GET /api/tags
{ id: number; name: string }[]

// GET /api/articles
{ id: number; content: string; tags: number[]; }[]

// GET /api/tags
{ id: number; name: string }[]

// GET /api/articles
{ id: number; content: string; tags: number[]; }[]

The Problem

Goal: Filter articles where

tags

tags

array contains a specific tag ID.

Current approach (what I'm doing now):

// Using fn.where() to filter client-side
const filtered = createLiveQueryCollection((q) =>
  q
    .from({ article: articlesCollection })
    .fn.where((row) => row.article.tags.includes(selectedTagId)),
);

// Using fn.where() to filter client-side
const filtered = createLiveQueryCollection((q) =>
  q
    .from({ article: articlesCollection })
    .fn.where((row) => row.article.tags.includes(selectedTagId)),
);

Issue: Articles collection is on-demand.

fn.where()

fn.where()

runs client-side after fetching ALL data, defeating the purpose. Need expression-based operator compiled to backend query.

Potential Solutions

Option 1: Should there be an

arrayContains()/arrayIncludes()

arrayContains()/arrayIncludes()

operator?

q.from({ article: articlesCollection }).where(({ article }) =>
  arrayContains(article.tags, selectedTagId),
);

q.from({ article: articlesCollection }).where(({ article }) =>
  arrayContains(article.tags, selectedTagId),
);

Option 2: Use junction table with joins instead of embedded array?
TanStack DB mimics tabular data, maybe the solution is to work with the actual junction table instead of the embedded array?

q.from({ article: articlesCollection })
  .join({ articleTag: articleTagsCollection }, ({ article, articleTag }) =>
    eq(article.id, articleTag.articleId),
  )
  .where(({ articleTag }) => eq(articleTag.tagId, selectedTagId))
  .select(({ article }) => ({ ...article }))
  .distinct();

q.from({ article: articlesCollection })
  .join({ articleTag: articleTagsCollection }, ({ article, articleTag }) =>
    eq(article.id, articleTag.articleId),
  )
  .where(({ articleTag }) => eq(articleTag.tagId, selectedTagId))
  .select(({ article }) => ({ ...article }))
  .distinct();

Option 3: Something else, maybe I am missing something...

How to filter where array field contains a value?

Use case:

Data Structure

The Problem

Current approach (what I'm doing now):

Potential Solutions

Similar Threads

How to filter where array field contains a value?

Use case:

Data Structure

The Problem

Current approach (what I'm doing now):

Potential Solutions

Similar Threads

Similar Threads

Similar Threads