[FEAT] Supabase Data Provider needs an array_overlap operator

jim-knight commented 3 months ago

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

I would like to filter an array column with an array in Supabase, where any row is shown where a value in the column matches at least one selected.

Example: I have 4 rows of data under "Fruits".

['Apple']
['Banana']
['Apple', 'Banana']
['Kiwi']

I would like to filter with the following:

['Apple', 'Kiwi']

The returned result should be:

['Apple']
['Apple', 'Banana']
['Kiwi']

Describe alternatives you've considered

Issue #5922 is similar in that it added the ability to check if an array matches an array with the ina and nina operators but differs based on the ability to match if ANY value in the filtering array matches the column array.

Additional context

Here's a couple of examples of using the Supabse JS client to do the call:

let { data: orders } = await supabaseClient
    .from('orders')
    .select('*')
    .or('fruits.ov.{Apple,Kiwi}');

let { data: orders2 } = await supabaseClient
    .from('orders')
    .select('*')
    .overlaps('fruits', ['Apple', 'Kiwi']);

Describe the thing to improve

I believe we could follow a similar route and add ina_overlap and nina_overlap CrudFilter and map to query.overlaps in Supabase data provider.

jim-knight commented 3 months ago

If I could get assigned to this, I'll give it a shot at getting it included. Thanks in advance!

alicanerdurmaz commented 3 months ago

Hello @jim-knight,

I believe It's okay to add ina_overlap operator but I'm sorry I couldn't quite understand why ina doesn't work for you. It seems exactly what you looking for.

for example: test: https://github.com/refinedev/refine/pull/5922/files#diff-50bbe4ac8d69008dd5bca7c6b83650fcd59684616f14a9e50dbe9299fd79529eR316 mock: https://github.com/issa012/refine/blob/84bf034c7f6df12cfd0837160125cac9f340a19d/packages/supabase/test/getList/index.mock.ts#L1677

we sent this value: ["health", "travel"] with ina:

and we can get the following data

["health", "travel", "food"]

but we can't get this because "health" is not available in the array.

["travel", "food"]

do you also want to get ["travel", "food"]?

jim-knight commented 3 months ago

@alicanerdurmaz Thanks Alican, that's correct. Ideally in my use case the query would include any post that has a tag that matches ANY in the filter vs requiring ALL to match.

Using your example, if we used value: ["health", "travel"] with ina_overlap, it should return the following examples:

["health", "travel"]
["health", "travel", "food"]
["health"]
["travel"]

Specifically in my use case, we have a table of businesses with categories tied to the industries that they're in. The categories column is an array of strings. Here's a short listing of some of the values: Aerospace, Automotive, Business Services, Consumer Products & Services, Distribution & Logistics, Education, Energy, Food and Beverage, Healthcare.

Let's say business A is in Education and Healthcare, while business B is in Automotive and Education, while business C is in Aerospace.

When filtering for ['Education', 'Healthcare'], businesses A and B should be included because they match at least one of the values in the filter. The user might be looking for businesses in specific industries but they don't necessarily need to be a perfect match.

I hope that helps with what I'm looking for.

alicanerdurmaz commented 3 months ago

@jim-knight Thanks for excellent explanation. I assigned issue to you. 🙏

jim-knight commented 3 months ago

@alicanerdurmaz Perfect. Thanks, Alican! I'll get to work on it sometime this week.

jim-knight commented 3 months ago

@alicanerdurmaz Unfortunately I don't think I'll be able to continue working on this issue. I've been struggling to get the environment set up on my Windows based machine and seem to run into a number of install errors via git bash, or powershell. I've got the main 6 files updated but can't commit due to failing on the install and not being able to run lint-staged.

BatuhanW commented 3 months ago

@jim-knight sorry to hear about your issue. Could you provide more information on your errors? Would be nice to provide any error you encountered.

jim-knight commented 3 months ago

@BatuhanW

Sure thing, so running with Powershell didn't work just because it didn't like the syntax of the devPreinstall command from package.json, which was expected.

Next up, I tried Git Bash and running just the direct install command but got the following message:

$ pnpm install
Scope: all 240 workspace projects
. pnpm:devPreinstall$ powershell -Command "New-Item -ItemType Directory -Force -Path 'packages/cli/dist','packages/devto…
│     Directory: F:\Dev projects\Supabase testing\Refine-6024\packages\cli
│ Mode                 LastWriteTime         Length Name                                                                …
│ ----                 -------------         ------ ----                                                                …
│ d-----         6/15/2024  11:54 AM                dist                                                                …  
│     Directory: F:\Dev projects\Supabase testing\Refine-6024\packages\devtools-server
│ Mode                 LastWriteTime         Length Name                                                                …  
│ ----                 -------------         ------ ----                                                                …  
│ d-----         6/15/2024  11:54 AM                dist                                                                …  
└─ Done in 345ms

   ╭─────────────────────────────────────────────────────────────────╮
   │                                                                 │
   │                Update available! 9.3.0 → 9.4.0.                 │
   │   Changelog: https://github.com/pnpm/pnpm/releases/tag/v9.4.0   │
   │         Run a script from: https://pnpm.io/installation         │
   │                                                                 │
   │        Follow @pnpmjs for updates: https://x.com/pnpmjs         │
   │                                                                 │
   ╰─────────────────────────────────────────────────────────────────╯

Lockfile is up to date, resolution step is skipped
Packages: -145
-------------------------------------------------------------------------------------------------------------------------
 WARN  Failed to create bin at F:\Dev projects\Supabase testing\Refine-6024\packages\devtools\node_modules\.bin\refine-devtools. ENOENT: no such file or directory, stat 'F:\Dev projects\Supabase testing\Refine-6024\packages\devtools\node_modules\@refinedev\devtools-server\dist\cli.cjs.EXE'
 WARN  Failed to create bin at F:\Dev projects\Supabase testing\Refine-6024\packages\cli\node_modules\.bin\refine-devtools. ENOENT: no such file or directory, stat 'F:\Dev projects\Supabase testing\Refine-6024\packages\cli\node_modules\@refinedev\devtools-server\dist\cli.cjs.EXE'
Progress: resolved 0, reused 124, downloaded 0, added 0, done
 WARN  Failed to create bin at F:\Dev projects\Supabase testing\Refine-6024\packages\devtools\node_modules\.bin\refine-devtools. ENOENT: no such file or directory, stat 'F:\Dev projects\Supabase testing\Refine-6024\packages\devtools\node_modules\@refinedev\devtools-server\dist\cli.cjs.EXE'
 WARN  Failed to create bin at F:\Dev projects\Supabase testing\Refine-6024\packages\cli\node_modules\.bin\refine-devtools. ENOENT: no such file or directory, stat 'F:\Dev projects\Supabase testing\Refine-6024\packages\cli\node_modules\@refinedev\devtools-server\dist\cli.cjs.EXE'
packages/create-refine-app prepare$ pnpm -w build --scope create-refine-app
[86 lines collapsed]                                                                                                       
│ @refinedev/core: CJS dist\index.cjs.map 746.61 KB
│ @refinedev/core: CJS ⚡️ Build success in 2245ms
│ @refinedev/core: ESM dist\index.mjs     141.37 KB
│ @refinedev/core: ESM dist\index.mjs.map 738.88 KB
│ @refinedev/core: ESM ⚡️ Build success in 2248ms
│ @refinedev/core: Generating declarations...
│ @refinedev/core: Generating d.mts and d.cts files...
│ @refinedev/core: ✓ Declarations are generated.
│  Lerna (powered by Nx)   Successfully ran target build for 2 projects and 2 tasks they depend on
│ Nx read the output from the cache instead of running the command for 1 out of 4 tasks.
└─ Done in 2m 2.1s
packages/devtools-shared prepare$ pnpm -w build --scope @refinedev/devtools-shared
[65 lines collapsed]                                                                                                       
│ CJS dist\index.cjs.map 746.61 KB
│ CJS ⚡️ Build success in 2245ms
│ ESM dist\index.mjs     141.37 KB
│ ESM dist\index.mjs.map 738.88 KB
│ ESM ⚡️ Build success in 2248ms
│ Generating declarations...
│ Generating d.mts and d.cts files...
│ ✓ Declarations are generated.
│  Lerna (powered by Nx)   Successfully ran target build for 2 projects and 1 task they depend on
│ Nx read the output from the cache instead of running the command for 3 out of 3 tasks.
└─ Done in 1m 19.6s
. prepare$ husky install
│ husky - Git hooks installed
└─ Done in 282ms
packages/devtools-internal prepare$ pnpm -w build --scope @refinedev/devtools-internal
[65 lines collapsed]                                                                                                       
│ CJS dist\index.cjs.map 746.61 KB
│ CJS ⚡️ Build success in 2245ms
│ ESM dist\index.mjs     141.37 KB
│ ESM dist\index.mjs.map 738.88 KB
│ ESM ⚡️ Build success in 2248ms
│ Generating declarations...
│ Generating d.mts and d.cts files...
│ ✓ Declarations are generated.
│  Lerna (powered by Nx)   Successfully ran target build for 2 projects and 1 task they depend on
│ Nx read the output from the cache instead of running the command for 3 out of 3 tasks.
└─ Done in 10.1s
packages/devtools-ui prepare$ pnpm -w build --scope @refinedev/devtools-ui
[89 lines collapsed]                                                                                                       
│ @refinedev/devtools-ui: CJS dist\index.cjs     130.36 KB
│ @refinedev/devtools-ui: CJS dist\style.css     46.49 KB
│ @refinedev/devtools-ui: CJS dist\index.cjs.map 328.98 KB
│ @refinedev/devtools-ui: CJS dist\style.css.map 74.58 KB
│ @refinedev/devtools-ui: CJS ⚡️ Build success in 8107ms
│ @refinedev/devtools-ui: Generating declarations...
│ @refinedev/devtools-ui: Generating d.mts and d.cts files...
│ @refinedev/devtools-ui: ✓ Declarations are generated.
│  Lerna (powered by Nx)   Successfully ran target build for 2 projects and 2 tasks they depend on
│ Nx read the output from the cache instead of running the command for 3 out of 4 tasks.
└─ Done in 42.5s
packages/devtools-server prepare$ pnpm -w build --scope @refinedev/devtools-server
│ > refinedev@0.0.0 build F:\Dev projects\Supabase testing\Refine-6024                                                     
│ > lerna run build --scope @refinedev/core "--scope" "@refinedev/devtools-server"
│ (node:22496) [DEP0040] DeprecationWarning: The punycode module is deprecated. Please use a userland alternative inst…
│ (Use node --trace-deprecation ... to show where the warning was created)
│ lerna notice cli v8.1.2
│ lerna notice filter including ["@refinedev/core","@refinedev/devtools-server"]
│ lerna notice filter excluding "{monorepo-*,with-nx,blog-react-aria,blog-sveltekit-crud}"
│ lerna info filter [
│ lerna info filter   '@refinedev/core',
│ lerna info filter   '@refinedev/devtools-server',
│ lerna info filter   '!{monorepo-*,with-nx,blog-react-aria,blog-sveltekit-crud}'
│ lerna info filter ]
│ lerna info run Using the "ignore" option when Nx targets are configured will exclude only tasks that are not determine…
│  Lerna (powered by Nx)   Running target build for 2 projects and 3 tasks they depend on:
│ - @refinedev/core
│ - @refinedev/devtools-server
│ > @refinedev/devtools-shared:build  [existing outputs match the cache, left as is]
│ > @refinedev/devtools-shared@1.1.9 build F:\Dev projects\Supabase testing\Refine-6024\packages\devtools-shared
│ > tsup && node ../shared/generate-declarations.js
│ CLI Building entry: src/index.ts
│ CLI Using tsconfig: tsconfig.json
│ CLI tsup v6.7.0
│ CLI Using tsup config: F:\Dev projects\Supabase testing\Refine-6024\packages\devtools-shared\tsup.config.ts
│ CLI Target: node14
│ CJS Build start
│ ESM Build start
│ ESM dist\index.mjs     2.60 KB
│ ESM dist\index.mjs.map 11.21 KB
│ ESM ⚡️ Build success in 252ms
│ CJS dist\index.cjs     3.23 KB
│ CJS dist\index.cjs.map 12.06 KB
│ CJS ⚡️ Build success in 254ms
│ Generating declarations...
│ Generating d.mts and d.cts files...
│ ✓ Declarations are generated.
│ > @refinedev/devtools-internal:build  [existing outputs match the cache, left as is]
│ > @refinedev/devtools-internal@1.1.11 build F:\Dev projects\Supabase testing\Refine-6024\packages\devtools-internal      
│ > tsup && node ../shared/generate-declarations.js
│ CLI Building entry: src/index.ts
│ CLI Using tsconfig: tsconfig.json
│ CLI tsup v6.7.0
│ CLI Using tsup config: F:\Dev projects\Supabase testing\Refine-6024\packages\devtools-internal\tsup.config.ts
│ CLI Target: node14
│ CJS Build start
│ ESM Build start
│ CJS dist\index.cjs     3.95 KB
│ CJS dist\index.cjs.map 14.58 KB
│ CJS ⚡️ Build success in 76ms
│ ESM dist\index.mjs     3.30 KB
│ ESM dist\index.mjs.map 14.37 KB
│ ESM ⚡️ Build success in 77ms
│ Generating declarations...
│ Generating d.mts and d.cts files...
│ ✓ Declarations are generated.
│ > @refinedev/devtools-ui:build  [existing outputs match the cache, left as is]
│ > @refinedev/devtools-ui@1.1.24 build F:\Dev projects\Supabase testing\Refine-6024\packages\devtools-ui
│ > tsup && node ../shared/generate-declarations.js
│ CLI Building entry: src/index.ts, src/style.css
│ CLI Using tsconfig: tsconfig.json
│ CLI tsup v6.7.0
│ CLI Using tsup config: F:\Dev projects\Supabase testing\Refine-6024\packages\devtools-ui\tsup.config.ts
│ CLI Target: node14
│ CJS Build start
│ ESM Build start
│ ESM dist\style.css     46.49 KB
│ ESM dist\index.mjs     119.92 KB
│ ESM dist\style.css.map 74.58 KB
│ ESM dist\index.mjs.map 324.36 KB
│ ESM ⚡️ Build success in 8105ms
│ CJS dist\index.cjs     130.36 KB
│ CJS dist\style.css     46.49 KB
│ CJS dist\index.cjs.map 328.98 KB
│ CJS dist\style.css.map 74.58 KB
│ CJS ⚡️ Build success in 8107ms
│ Generating declarations...
│ Generating d.mts and d.cts files...
│ ✓ Declarations are generated.
│ > @refinedev/devtools-server:build
│ (node:22496) [DEP0060] DeprecationWarning: The util._extend API is deprecated. Please use Object.assign() instead.     
│ > @refinedev/core:build  [existing outputs match the cache, left as is]
│ > @refinedev/core@4.51.0 build F:\Dev projects\Supabase testing\Refine-6024\packages\core
│ > tsup && node ../shared/generate-declarations.js
│ CLI Building entry: src/index.tsx
│ CLI Using tsconfig: tsconfig.json
│ CLI tsup v6.7.0
│ CLI Using tsup config: F:\Dev projects\Supabase testing\Refine-6024\packages\core\tsup.config.ts
│ CLI Target: node14
│ CJS Build start
│ ESM Build start
│ CJS dist\index.cjs     146.36 KB
│ CJS dist\index.cjs.map 746.61 KB
│ CJS ⚡️ Build success in 2245ms
│ ESM dist\index.mjs     141.37 KB
│ ESM dist\index.mjs.map 738.88 KB
│ ESM ⚡️ Build success in 2248ms
│ Generating declarations...
│ Generating d.mts and d.cts files...
│ ✓ Declarations are generated.
│ @refinedev/devtools-server: > @refinedev/devtools-server@1.1.31 build F:\Dev projects\Supabase testing\Refine-6024\pac…  
│ @refinedev/devtools-server: > pnpm build:client && tsup && node ../shared/generate-declarations.js
│ @refinedev/devtools-server: > @refinedev/devtools-server@1.1.31 build:client F:\Dev projects\Supabase testing\Refine-6…  
│ @refinedev/devtools-server: > NODE_ENV=production tsc && vite build --config src/client/vite.config.ts
│ @refinedev/devtools-server: 'NODE_ENV' is not recognized as an internal or external command,
│ @refinedev/devtools-server: operable program or batch file.
│ @refinedev/devtools-server:  ELIFECYCLE  Command failed with exit code 1.
│ @refinedev/devtools-server:  ELIFECYCLE  Command failed with exit code 1.
│  Lerna (powered by Nx)   Running target build for 2 projects and 3 tasks they depend on failed
│ Failed tasks:
│ - @refinedev/devtools-server:build
│  ELIFECYCLE  Command failed with exit code 1.
└─ Failed in 10.6s at F:\Dev projects\Supabase testing\Refine-6024\packages\devtools-server
 ELIFECYCLE  Command failed with exit code 1.
packages/core prepare$ pnpm -w build --scope @refinedev/core
└─ Running...

I then tried running it while ignoring scripts, and got the following message:

$ pnpm install --ignore-scripts
Scope: all 240 workspace projects
Lockfile is up to date, resolution step is skipped
Packages: -145
-------------------------------------------------------------------------------------------------------------------------
 WARN  Failed to create bin at F:\Dev projects\Supabase testing\Refine-6024\packages\devtools\node_modules\.bin\refine-devtools. ENOENT: no such file or directory, stat 'F:\Dev projects\Supabase testing\Refine-6024\packages\devtools\node_modules\@refinedev\devtools-server\dist\cli.cjs.EXE'
 WARN  Failed to create bin at F:\Dev projects\Supabase testing\Refine-6024\packages\cli\node_modules\.bin\refine-devtools. ENOENT: no such file or directory, stat 'F:\Dev projects\Supabase testing\Refine-6024\packages\cli\node_modules\@refinedev\devtools-server\dist\cli.cjs.EXE'
Progress: resolved 0, reused 124, downloaded 0, added 0, done
 WARN  Failed to create bin at F:\Dev projects\Supabase testing\Refine-6024\packages\devtools\node_modules\.bin\refine-devtools. ENOENT: no such file or directory, stat 'F:\Dev projects\Supabase testing\Refine-6024\packages\devtools\node_modules\@refinedev\devtools-server\dist\cli.cjs.EXE'
 WARN  Failed to create bin at F:\Dev projects\Supabase testing\Refine-6024\packages\cli\node_modules\.bin\refine-devtools. ENOENT: no such file or directory, stat 'F:\Dev projects\Supabase testing\Refine-6024\packages\cli\node_modules\@refinedev\devtools-server\dist\cli.cjs.EXE'
Done in 1m 16s

I thought that running the command through Git Bash would help to resolve any issue relating to NODE_ENV not being recognized in Windows. I then switched to using WSL with Ubuntu to install and again ran up against issues:

jknight@DESKTOP-PCBKQJF:~$ npm install -g pnpm
npm notice
npm notice New minor version of npm available! 10.7.0 -> 10.8.1
npm notice Changelog: https://github.com/npm/cli/releases/tag/v10.8.1
npm notice To update run: npm install -g npm@10.8.1
npm notice
npm error code EACCES
npm error syscall mkdir
npm error path /usr/lib/node_modules/pnpm
npm error errno -13
npm error [Error: EACCES: permission denied, mkdir '/usr/lib/node_modules/pnpm'] {
npm error   errno: -13,
npm error   code: 'EACCES',
npm error   syscall: 'mkdir',
npm error   path: '/usr/lib/node_modules/pnpm'
npm error }
npm error
npm error The operation was rejected by your operating system.
npm error It is likely you do not have the permissions to access this file as the current user
npm error
npm error If you believe this might be a permissions issue, please double-check the
npm error permissions of the file and its containing directories, or try running
npm error the command again as root/Administrator.

npm error A complete log of this run can be found in: /home/jknight/.npm/_logs/2024-06-17T20_57_50_919Z-debug-0.log

At that point I called it quits as I just ran out of time and thought it might be better for someone else to step in.

aliemir commented 2 months ago

Hey @jim-knight, we've implemented the fix about the Windows environment. Can you check if you can continue working on this one? Please let us know if you encounter any other issues 🙏

jim-knight commented 2 months ago

Hey @jim-knight, we've implemented the fix about the Windows environment. Can you check if you can continue working on this one? Please let us know if you encounter any other issues 🙏

Thanks for the update @aliemir. I'll give it a shot as soon as I have some free time and will report back.

refinedev / refine