How to get products with category filter logic

[jezzzm]

Looking for some syntax help with filtering. I'm using the backend api.

I have your standard ecommerce filter setup with fieldsets containing checkboxes for each of their children (in some cases, nested according to setup in swell). simple example:

Format
  [x] pdf
  [x] ppt
  [ ] doc

Theme
  [ ] rainbow
  [x] neutrals
  [ ] greyscale
  
Grade
  [ ] Kindergarten
  [x] 1st
  [x] 2nd

I cannot figure how to GET /products and return according to this logic:

products WHERE
categories includes pdf OR ppt
AND
categories includes neutrals
AND
categories includes 1st OR 2nd

i may be misunderstanding the mongo syntax but i feel like i've tried everything and nothing works. thanks!

example attempt - ids correspond to child category ids

const queryOptions = {
   where: {
    '$or': [
       {
         categories: {
           '$in': [ '63ae3c88e8b97000134dcb2e', '63ae3c812fc8080012f82b45' ]
         }
       },
       { categories: { '$in': [ '63ae665b3caf1b001212cbed' ] } },
       {
         categories: {
           '$in': [ '63ae65b52643e60013de7fd7', '63ae42492fc8080012f82d2c' ]
         }
       }
     ],
   },
   limit: 24
 }

[ericingram]

Ideally we could support the $filter operator in nested $and/$or queries - that would make this kind of thing a lot easier.

If you're building a filtering UI, then typically you'd want to use $and instead.

In any case, here's a way to make that work using the backend API:

const queryOptions = {
   where: {
    '$or': [
       {
         'category_index.id': {
           '$in': [ '63ae3c88e8b97000134dcb2e', '63ae3c812fc8080012f82b45' ]
         }
       },
       { 'category_index.id': { '$in': [ '63ae665b3caf1b001212cbed' ] } },
       {
         'category_index.id': {
           '$in': [ '63ae65b52643e60013de7fd7', '63ae42492fc8080012f82d2c' ]
         }
       }
     ],
   },
   limit: 24
 }

Note the cateogry_index field is an index of categories that's maintained automatically. It helps make fetching products by category, along with sorting, more efficient. It's used behind the scenes with $filter and $categories operators.

[jezzzm]

that's perfect. thanks Eric!

[jezzzm]

is there a way to make this work such that passing a parent category id will also include children of that parent?

e.g. products in the three child categories here (but not explicitly in phonics itself) will be included?

[jezzzm]

hey @ericingram , sorry to dig this one up - my last comment on getting products that have a category which is the child of that selected is a big blocker. At the moment, on the store we have no way of showing all products from nested categories, and it is rare to have the parent explicitly set on products.

any help here is much appreciated.

example:

my current query is:

 {
   '$and': [
     {
       'category_index.id': {
         '$in': [ '62c596794185110013302a4d', '62c5ac654551e30012304216' ] // content area is: numeracy or maths (both of which have many child categories)
       }
     },
     {
       'category_index.id': {
         '$in': [ '63ae3c812fc8080012f82b45', '63ae3c88e8b97000134dcb2e' ] // grade is 1st or 2nd (no children for these)
       }
     },
     { 'category_index.id': { '$in': [ '63ae65b52643e60013de7fd7' ] } } // format is handbook (no children for this)
   ]
 }

content area nesting:

anything categorised with phonics or number should pass the query filter here.

thanks!

[ericingram]

@jezzzm It looks like there's no simplified way to do the $and/$or logic you need, but you can fetch the sub-categories and make the query work as you described.

There is a special operator on the product model that does retrieve products from sub-categories of a parent, but it doesn't support your mix of logical operators (note $in is effectively like $or in this case).

In any case here's how it works:

swell-get('/products', {
  categories: [ 'category1', 'category2', 'category3', ... ]
})

The above will return products in any of those top or sub-categories. Internally the code does something like this:

async function queryByCategoriesDeep(categoryIds) {
  const allCategoryIds = await findCategoryIdsRecursive(
    categoriesIds,
  );
  ...
  return {
    'category_index.id': { $in: allCategoryIds },
  }
}

async function findCategoryIdsRecursive(parentIds, ids) {
  const resultIds = ids || [];

  const result = await get('/categories', {
    id: { $in: parentIds },
    fields: 'id',
    limit: null,
    active: true,
    include: {
      children: {
        url: '/categories',
        params: {
          parent_id: 'id',
        },
        data: {
          fields: 'id',
          limit: null,
          active: true,
        },
      },
    },
  });

  if (!result.results.length) {
    return resultIds;
  }

  await Promise.all(
    result.results.map(async (category) => {
      resultIds.push(category.id);

      if (category.children.results.length > 0) {
        const nextIds = category.children.results.map((child) => child.id));
        await findCategoryIdsRecursive(nextIds, resultIds);
      }
    }),
  );

  return resultIds;
};

We should really improve the categories approach to handle this use case, which is also used by the $filter operator. Need to figure out the right syntax for that.

[jezzzm]

thanks @ericingram. appreciate the info.

I've since realised that perhaps the more standard UX is to click a parent and have all children selected programatically on the frontend before sending the request off to Swell...

coding is hard