Set intersect query with []string and .In()

[Southclaws]

I'm working with a model that includes a tags []string kind of thing, and I'd like to know if it's possible to do some specific intersections for queries.

For reference, the feature I'm working on is open source so it may help to check the PR: https://github.com/openmultiplayer/web/pull/420/files

The relevant part of the schema is here: https://github.com/openmultiplayer/web/blob/forum-categories/prisma/schema.prisma#L94-L128 notably the list of Tags in each Post model.

It works great with these queries: https://github.com/openmultiplayer/web/pull/420/files#diff-3c9c79132aaec9a342a9567721f83d4d8b968069440955ebdbf80e89bd31b1b1R155-R183 if you specify one or many tags in the arguments, you get any post that has any of the specified tags.

So, let's say I have 3 posts

1. tagged "Food"
2. tagged "Music"
3. tagged "Food" and "Music"

When I query "food,music" I get post 1 and 2 but not post 3

When I query "food" I get post 1 but not post 3

When I query "music" I get post 2 but not post 3

So it seems posts with multiple tags cannot be queried with my approach.

Is there a good way of doing this with the current Go API? Or do I have to go to raw SQL for this.

Thanks!

[steebchen]

I can confirm this, something is wrong internally here as the filters for in should actually not have In but rather array-specific methods (e.g. HasSome). I will try to pick this up and fix it soon.

In the meanwhile, I think you could try to work around by using an .Or() and then .Equals(tag) for each tag. Something like this:

queries := []db.PostWhereParam{
	db.Post.First.Equals(true),
	db.Post.CreatedAt.Before(before),
}
var tagQueries []db.PostWhereParam
for _, tag := range tags {
	tagQueries = append(tagQueries, db.Post.Tags.Some(db.Tag.Name.Equals(tag)))
}
queries = append(
	queries,
	db.Post.Or(
		tagQueries...
	),
)
// ...FindMany(queries)...

I know, this is ugly, but it'll save you doing two queries though :P

[Southclaws]

Hey, thanks for the response! This looks like a good alternative, it makes sense, but I just tried it and it doesn't seem to work.

I even tried hard-coding the query, just to be sure:

				FindMany(
					db.Post.First.Equals(true),
					db.Post.CreatedAt.Before(before),
					db.Post.Or(
						db.Post.Tags.Some(
							db.Tag.Name.Contains("open.mp"),
						),
					),
				).

This returns two items, which only have the tag open.mp but it doesn't return the item that has two tags, open.mp and forum:

[Southclaws]

Actually, I'm just going to remove the other filters (before date and first post) to make sure those aren't filtering it out accidentally.

Edit: nope, purely just the tag filter still results in only the items that have a single tag, not the item that has two tags

Edit 2: actually I just realised I'm still on 0.10.0, gonna upgrade and try again

[steebchen]

#618 introduces list read and write filters, for example:

Match any item anywhere in the list

user, err := client.User.FindFirst(
	db.User.Items.HasSome([]string{"b"}),
).Exec(ctx)
if err != nil {
	t.Fatalf("fail %s", err)
}

Push a single item to an existing list/array

user, err := client.User.FindUnique(
	db.User.ID.Equals("id1"),
).Update(
	db.User.Items.Push([]string{"d"}),
).Exec(ctx)
if err != nil {
	t.Fatalf("fail %s", err)
}

[steebchen]

@Southclaws It would be great if you could try this out via go get github.com/prisma/prisma-client-go@main and let me know if it works.

[Southclaws]

Awesome thanks! I will give this a try when I get time and let you know how it goes 👍

[Southclaws]

I grabbed the new version and it hasn't generated HasSome methods for my array types.

[steebchen]

Are you sure you're using the main branch version? There is no release yet.

[Southclaws]

I ran go get github.com/prisma/prisma-client-go@main and my go.mod/sum looks like:

github.com/prisma/prisma-client-go v0.11.1-0.20211014160141-a4310b37583e

github.com/prisma/prisma-client-go v0.11.1-0.20211014160141-a4310b37583e h1:KWVGvpUZGHSrkew3Vgacf9Axa19tgZT3uO1UOGpyDsU=
github.com/prisma/prisma-client-go v0.11.1-0.20211014160141-a4310b37583e/go.mod h1:M8YaRRW8pd/uUgrz8zW6JwnlaywqD15g1ETo9fXpmSQ=

I invoke it like this:

web on  forum-categories [$!] via 🐹 v1.16.6 
❯ go run github.com/prisma/prisma-client-go generate
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma

✔ Generated Prisma Client Go to ./server/src/db in 5.48s

My schema is using this config:

generator client {
  provider      = "go run github.com/prisma/prisma-client-go"
  output        = "../server/src/db"
  package       = "db"
  binaryTargets = ["native"]
}

[steebchen]

That's weird, can you please also share the prisma schema or at least the part where you use string[], and also the code part where you want to use .HasSome.

[Southclaws]

Sure thing, here's the project:

https://github.com/openmultiplayer/web/tree/forum-categories

The field I want to use HasSome is here: https://github.com/openmultiplayer/web/blob/forum-categories/prisma/schema.prisma#L111

The query I want to add a tags filter to is here: https://github.com/openmultiplayer/web/blob/forum-categories/server/src/resources/forum/db.go#L154-L158

Which should look like this:

		FindMany(
			db.Post.First.Equals(true),
			db.Post.CreatedAt.Before(before),
			db.Post.Category.Where(db.Category.Name.Equals(category)),
			db.Post.Tags.HasSome(tags),
		).

And version info if that also helps:

web on  forum-categories [$!] via 🐹 v1.16.6 
❯ go run github.com/prisma/prisma-client-go version 
Environment variables loaded from .env
prisma                : 3.1.1
@prisma/client        : Not found
Current platform      : darwin
Query Engine (Binary) : query-engine c22652b7e418506fab23052d569b85d3aec4883f (at ../../../Library/Caches/prisma/binaries/cli/3.1.1/c22652b7e418506fab23052d569b85d3aec4883f/prisma-query-engine-darwin, resolved by PRISMA_QUERY_ENGINE_BINARY)
Migration Engine      : migration-engine-cli c22652b7e418506fab23052d569b85d3aec4883f (at ../../../Library/Caches/prisma/binaries/cli/3.1.1/c22652b7e418506fab23052d569b85d3aec4883f/prisma-migration-engine-darwin, resolved by PRISMA_MIGRATION_ENGINE_BINARY)
Introspection Engine  : introspection-core c22652b7e418506fab23052d569b85d3aec4883f (at ../../../Library/Caches/prisma/binaries/cli/3.1.1/c22652b7e418506fab23052d569b85d3aec4883f/prisma-introspection-engine-darwin, resolved by PRISMA_INTROSPECTION_ENGINE_BINARY)
Format Binary         : prisma-fmt c22652b7e418506fab23052d569b85d3aec4883f (at ../../../Library/Caches/prisma/binaries/cli/3.1.1/c22652b7e418506fab23052d569b85d3aec4883f/prisma-prisma-fmt-darwin, resolved by PRISMA_FMT_BINARY)
Default Engines Hash  : c22652b7e418506fab23052d569b85d3aec4883f
Studio                : 0.423.0

[steebchen]

Ah. That's a relation field and not an array/list field. You need to a relation query with .Where and then do .HasSome on the field of type string[]. Basically the same query as before except of doing .In you write .HasSome

[steebchen]

Hm did your schema change? I'll check again tomorrow when I'm back on my computer

[Southclaws]

Ohh because my tags list is a relation not a simple list of strings? I completely forgot this detail while writing the issue!

[steebchen]

Yeah exactly. These operations only exist for list/array fields of type string[]. In your case it seems you just have a another model (Tag). You still should be able to query for what you want either way, e.g. with .Some and then compare the Tag.name with .Equals or something.

[steebchen]

You might want to think about if maybe a string array for post tags would be sufficient, but that obviously depends on your use-case. I think it would fit considering your current schema but would not work anymore if e.g. users could have their own tags on posts.

[Southclaws]

Yeah the reason for this is to show all posts for a particular tag etc. also we might add colours to tags too or other metadata so it won't fit a []string.

I'll try what I originally tried which was db.Post.Tags.Some(db.Tag.Name.In(tags)), or the earlier suggestion you posted here #594 (comment)