Topics

Drizzle Team•2mo ago

inArray not working as expected

This works but looks like a (sql injection) const tagsArray = selectedTags.map((tag) => '${tag}').join(', '); where.push(sql${tagData.tags} @> ARRAY[${sql.raw(tagsArray)}]::text[]); Why doesn't this just work (tm)? where.push(inArray(tagData.tags, selectedTags));

26 Replies

rphlmr ⚡•2mo ago

Hum 🧐 that’s not expected https://orm.drizzle.team/docs/operators#inarray do you have a particular error?

Drizzle ORM - Filters

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

tcurdtOP•2mo ago

The tagData is a CTE which gives a type error for inArray that says "no overload matches the call". And then the query errors out with

PostgresError: malformed array literal: "tag2"

PostgresError: malformed array literal: "tag2"

with selectedTags being [ "tag2" ]

rphlmr ⚡•2mo ago

Maybe I missed something: https://drizzle.run/n0mpj7jpps2yvbwwvua81mms Do not hesitate to share a small repro, just to be sure

Drizzle Run

inArray from sub select - Drizzle Run

tcurdtOP•2mo ago

I'll try to reproduce it in playground. That's probably the easiest.

tcurdtOP•2mo ago

Here we go! That's most stripped down version that still shows it. https://drizzle.run/g9pjwezeysxkxsfh6d83ayye

Drizzle Run

inArray from cte - Drizzle Run

tcurdtOP•2mo ago

Any idea, @Raphaël M (@rphlmr) ⚡ ?

rphlmr ⚡•2mo ago

https://drizzle.run/gk5h6fj2cjv8hk4brmaepjvh

Drizzle Run

(fork) inArray from cte - Drizzle Run

rphlmr ⚡•2mo ago

It was because of the cast to ARRAY I shared a safe toArray

tcurdtOP•2mo ago

Thanks for the help. I would have never figured that out by myself. I wished the where.push(inArray(tagData.tags, selectedTags)); would just work and TBH I don't fully understand why is doesn't yet. tagData.tags should be an array and selectedTags also is an array why is there a casting problem?

rphlmr ⚡•2mo ago

inArray works with a list (from a query or a js array). It translates to a “in” operator. Sql array are different and arrayContains translate to a “@ > {…}” 😅 I have a lot of these helpers in my projects, and I would love to have some of them in Drizzle. I will try to take a deeper look into it. inArray works fine everywhere I need it.

tcurdtOP•2mo ago

But shouldn't this also work with an IN operator? I don't quite get this

[P in PGCastTypes]: P extends `${infer _T}[]` ? P : never;

[P in PGCastTypes]: P extends `${infer _T}[]` ? P : never;

Why the _T?

rphlmr ⚡•2mo ago

Just an eslint convention for unused declaration

rphlmr ⚡•2mo ago

https://drizzle.run/gct3zuow3i13kvm33rerlb3u This is simpler.

Drizzle Run

inArray from cte - Drizzle Run

rphlmr ⚡•2mo ago

with "tag_data" as (select "postId", "tag" from "tags") select "post"."id", "post"."content", array_agg(distinct "tag_data"."tag") filter (where "tag_data"."tag" is not null) as "tags" from "post" inner join "tag_data" on "tag_data"."postId" = "post"."id" where "tag_data"."tag" in ('foo', 'bar') group by "post"."id"

with "tag_data" as (select "postId", "tag" from "tags") select "post"."id", "post"."content", array_agg(distinct "tag_data"."tag") filter (where "tag_data"."tag" is not null) as "tags" from "post" inner join "tag_data" on "tag_data"."postId" = "post"."id" where "tag_data"."tag" in ('foo', 'bar') group by "post"."id"

tcurdtOP•2mo ago

no no, not the _ .. but the T itself if it isn't even used. ...and I also get an type warning despite

    rules: {
      'no-unused-vars': 'off',
      '@typescript-eslint/no-unused-vars': [
        'error',
        {
          argsIgnorePattern: '^_',
          varsIgnorePattern: '^_',
          caughtErrorsIgnorePattern: '^_'
        }
      ]
    }

    rules: {
      'no-unused-vars': 'off',
      '@typescript-eslint/no-unused-vars': [
        'error',
        {
          argsIgnorePattern: '^_',
          varsIgnorePattern: '^_',
          caughtErrorsIgnorePattern: '^_'
        }
      ]
    }

but that sounds more like an eslint config issue. man, your utility functions are golden. thanks for those. but I still don't quite understand the why that approach works and my previous approach doesn't

rphlmr ⚡•2mo ago

"@typescript-eslint/no-unused-vars": [
      "warn",
      {
        vars: "all",
        varsIgnorePattern: "^_",
        args: "all",
        argsIgnorePattern: "^_",
        destructuredArrayIgnorePattern: "^_",
        ignoreRestSiblings: false,
      },
    ],

"@typescript-eslint/no-unused-vars": [
      "warn",
      {
        vars: "all",
        varsIgnorePattern: "^_",
        args: "all",
        argsIgnorePattern: "^_",
        destructuredArrayIgnorePattern: "^_",
        ignoreRestSiblings: false,
      },
    ],

It could be [P in PGCastTypes]: P extends ${string}[] ? P : never;.Maybe it was not possible with the TS version when I first did it (2 years ago).

rphlmr ⚡•2mo ago

Because you aggregated the data in the CTE and not in the final result.

No description

rphlmr ⚡•2mo ago

in works with a scalar column but not an array

tcurdtOP•2mo ago

interesting ... sounds like I need to read up on that difference ... first time I am trying it like that also curious to see if there is a big difference in the execution plan

rphlmr ⚡•2mo ago

I always try to aggregate in the last query now that we have views, it can be simpler to pre-build complex ... views 😄

tcurdtOP•2mo ago

that does feel counter intuitive to me though ... it seems aggregating as early as possible should scale better ... but that's really just my unsubstantiated gut feeling 🙃 drizzle supports views?

rphlmr ⚡•2mo ago

yes since monday!

rphlmr ⚡•2mo ago

https://drizzle.run/lxapec3034aaen4ou5cj28kr

Drizzle Run

View and Check constraint - Drizzle Run

rphlmr ⚡•2mo ago

and check constraints :p

tcurdtOP•2mo ago

ok ... that is recent 🙂

rphlmr ⚡•2mo ago

Yes, after a long wait :tired:

Drizzle Team Join

The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!

9.5KMembers

View on Discord

Want results from more Discord servers?

Add your server