Ability to filter by presence / absence of Many to Many relationship

Help me!
1

Expected behavior

I have Products and Tags which have a Many to Many relationship. Tags have a type field I would like to have the ability to filter and sort products by missing Tag type. (Give me all the products that do not have a Tag of type X)

Is the best way to create a smart field on Products and put a filter on that smart field ?

Thanks,
Camille

2

Hello @cooki23,

I understand your question. You want kind of a not in operator ?

That could be a possibility. For example you override the behaviour of not_equal filter but I guess it would require complexe queries to compute all the ids that matchs.

  {
    field: 'fakeMissingTags',
    isFilterable: true,
    type: 'String',
    get: (product) => {
      // you can compute something like a string of missing tags
      return 'we dont care';
    },
    filter({ condition, where }) {
      const value = !!condition.value && condition.value;

      switch (condition.operator) {
        // Choose whatever filter you want to implement
        case 'not_equal':
          const complexeQueriesToFindProdutsIds = await models.products
            .findAll({
                include: [{
                  model: models.tags,
                  as: "tagsThroughProductTags",
                  where: {  [Op.not]: { tagType: 0 } },
                }],
	        });

          const ids = complexeQueriesToFindProdutsIds.map(product => product.id);
          // Feed it with the array of ids.
          return { id: { [Op.in]: ids } };
 
        // ... implement the operator you want: equal for example pour the presence comportement
        default:
          return null;
      }
    },
  }
4

Hey @morganperre , I have the following code:

			async filter({ condition, where }) {
				switch (condition.operator) {
					case 'not_contains':
						const complexeQueriesToFindProdutsIds = await models.products.findAll({
							include: [{ model: models.tags, as: "tagsThroughProductTags", where: { 
								[Op.not]: {
									tagType: 0
								}
							} 
						}]
						});
						console.log({complexeQueriesToFindProdutsIds})
						let productIdsToDisplay = complexeQueriesToFindProdutsIds.map(product => product.id) 
						return { id: { [Op.In]: complexeQueriesToFindProdutsIds } };
				  	// ... implement the operator you want: equal for example pour the presence comportement
					default:
						return true;
				}
			},

But am getting the following error:
Unexpected error: invalid input syntax for integer: “[object Object]”
Which I believe is caused by the return value of the “not_contains” case.

I’m not sure I understand what the return value should be, If I return a single integer it works fine and the UI shows only one record with the id of the returned value, but If I pass an array of integers [607, 608].

I get this error:

Unexpected error: Support for literal replacements in the `where` object has been removed.

I would like to either display or not display a list of records.

Could you shed some light on what is the expected return value of the “not_contains” block ?

Thanks,
Camille

5

Turns out

Unexpected error: Support for literal replacements in the `where` object has been removed.

Is caused by sequelize and was resolved in version 5.19.1. But why would FA ship with sequelize 5.15 ?

6

Please @cooki23, verify your sequelize findAll ! You return products and not only ids…
You’re missing attributes: ['id'], to only retrieve ids.

const complexeQueriesToFindProdutsIds = await models.products.findAll({ attributes: ['id'],
    include: [{ model: models.tags, as: "tagsThroughProductTags", where: { 
      [Op.not]: {
        tagType: 0
      }
    } 
  }]
});	

// Plus use a mapping to remove related tag..
const ids = complexeQueriesToFindProdutsIds.map(product => product.id);

Kind regards,
Morgan

7

We update sequelize version rarely. Because it can be annoying for big customer. :confused:

8

@morganperre If the point is to return an array of ids, why does return [607, 608] throw

Unexpected error: Support for literal replacements in the `where` object has been removed.

#Dependencies

  "dependencies": {
    "body-parser": "1.19.0",
    "chalk": "~1.1.3",
    "cookie-parser": "1.4.4",
    "cors": "2.8.5",
    "debug": "~4.0.1",
    "dotenv": "~6.1.0",
    "express": "~4.17.1",
    "express-jwt": "6.0.0",
    "forest-express-sequelize": "^8.0.0",
    "morgan": "1.9.1",
    "pg": "~8.2.2",
    "require-all": "^3.0.0",
    "sequelize": "^5.19.1"
  }

If you could tell me what is supposed to be returned from that case condition that would be awesome:

Is it
return [12, 34, 45, 56]

throws forest] 🌳🌳🌳 Unexpected error: Support for literal replacements in the `where` object has been removed

Or
return { id: { [Op.In]: [12, 34, 45, 56] } };
throws Unexpected error: invalid input syntax for integer: "[object Object]"

The only thing that is working is when I return a single Integer

9

@morganperre turns out there was a typo in the initial codeblock you shared, it’s [Op.in] not [Op.In] thanks for the great help !

Camille

1 Like
10

My mistake… :sweat:

Great that works for you anyway. Have a great day.

Regards,
Morgan

1 Like