[FEAT]: Supabase Data Provider should filter array column by array

[alicanerdurmaz]

Is your feature request related to a problem? Please describe.

I want to filter the array column with an array in Supabase.
For example, I have the genres column and it has this data:

• [Comedy, Music, Horror]
• [Comedy]
• [Drama]

and I want to filter with this value:

• [Comedy, Music]

but Refine doesn't have a CrudFilter to support it.

Describe the thing to improve

We can add ina and nina CrudFilter and map to query.contains in Supabase data provider to support this case.

[issa012]

Hello! Please assign this to me, I would be happy to help.

[alicanerdurmaz]

Hello @issa012, I assigned this task to you. Thank you for improving Refine 🙌

[issa012]

Hi! 2 questions.

1. I am adding ina and nina directly to CrudFilter. Is this okay, or should I try to create custom type and extend CrudFilter?
2. How should I go about writing tests? The Supabase database doesn't contain any tables that have array values. I would like to add some fields or create new tables. It seems it is not possible to connect with only URL and KEY.

[alicanerdurmaz]

Hi! 2 questions.

1. I am adding ina and nina directly to CrudFilter. Is this okay, or should I try to create custom type and extend CrudFilter?
2. How should I go about writing tests? The Supabase database doesn't contain any tables that have array values. I would like to add some fields or create new tables. It seems it is not possible to connect with only URL and KEY.

1. Yes, we can add ina and nina to CrudFilter.

2. In supabase package all tests are mocked. In supabase package, all tests are mocked. I think the ideal scenario for testing this feature would be like this:

3. Create a new supabase project with your needs and connect the tests supabaseClient to that project from here:

   refine/packages/supabase/test/supabaseClient.ts

   Line 4 in eafc46b

   const SUPABASE_KEY =

4. Add new test cases and run tests. you can get mock results on CLI when you uncomment this line

5. copy mocks to your mock file, comment nock.recorder.rec() again

[jim-knight]

Thanks for this guys, literally just ran into this issue today so would love to see this implemented.

[jim-knight]

Hi everyone, just wanted to follow up on this as I saw the update go live. I've been playing around with it but haven't been able to get it to function the way I had hoped.

What I'm aiming for it to be able to filter posts by values included in an array where one value matches, vs all.

Here's my filter:

filters: {
	initial: [
		{
			field: 'status',
			operator: 'in',
			value: ['Active', 'Under LOI'],
		},
		{
			field: 'industry',
			operator: 'ina', 
			value: ['Business Services', 'Energy'], 
		},
	],
}

And my column code:

<Table.Column
	dataIndex={['industry']}
	key='industry'
	title='Industries'
	render={(value, record: IEngagements) => {
		if (isEditing(record.id)) {
			return (
				<Form.Item name='industry' className='mb-0'>
					<Select
						placeholder='Select related industries'
						options={IndustryOptions.map((industry) => ({
							value: industry.value,
							label: industry.label,
						}))}
						mode='multiple'
						allowClear
					/>
				</Form.Item>
			);
		}
		if (value === null) {
			return null;
		}
		return (
			<Flex wrap gap='4px'>
				{value.map((industry: string) => {
					return (
						<Tag key={industry}>
							<Space>{industry}</Space>
						</Tag>
					);
				})}
			</Flex>
		);
	}}

	filterDropdown={(props) => (
		<FilterDropdown {...props}>
			<Select
				options={IndustryOptions.map((industry) => ({
					value: industry.value,
					label: industry.label,
				}))}
				placeholder='Filter by transaction industry'
				style={{ minWidth: 200 }}
				mode='multiple'
				
			/>
		</FilterDropdown>
	)}
/>

So filtering actually works now without Supabase complaining, but it only shows results that match where ALL values are included. In my case, for testing I set the initial filter values of "Industry" to ['Business Services', 'Energy']. Posts that don't contain both values aren't being shown. Any idea what I might be missing?

[jim-knight]

Coming back to this, I think I've found what I'm looking for personally, which is array_overlap instead of contains. I was playing around with doing calls and this works where it'll show any entry that has ANY of the included values in the array.

let { data: engagements, error } = await supabaseClient
	.from('engagements')
	.select('*')
	.or('industry.ov.{Business Services,Energy}');
let { data: engagements2, error: error2 } = await supabaseClient
	.from('engagements')
	.select('*')
	.overlaps('industry', ['Business Services', 'Energy']);

Now I just need to figure out how to implement it.

Edit: I've opened it as a new issue here: #6024.