Unable to create array relationships from array of IDs

[pandoratoolbox]

I have a table 'post' with the column 'media_ids'. I'm trying to create an array relationship to the 'media' table on it's 'id' column.
In Hasura, it seems that this is impossible although I see a similar issue from a while ago that was closed (#4464).

When querying after creating this relationship, an error is produced:
"error": { "exec_status": "FatalError", "hint": "No operator matches the given name and argument types. You might need to add explicit type casts.", "message": "operator does not exist: bigint[] = bigint", "status_code": "42883", "description": null },

Is there any solution to this? I've created a computed field to retrieve the media rows from the media_ids column, but now there is no way to do a nested insert for medias when inserting a post.

[ro-savage]

Also running into the same issue. We have a bulk action that does many different things, and we would like to be able to return these as something like

{
 create: [18, 19, 20],
 updated: [2, 7],
 deleted: [1, 8, 9],
}

There are ids of the items that changed. We'd like to be able to link those to 'post'. So that we can then get the data for all the created posts, updated posts, and deleted posts.

The reason we cant return them in a single array such as [18, 19, 20, 2, 7, 1, 8, 9]. Is because
A) Some items have been deleted, and we now need to delete them from the frontend
B) We handle the create and updated changes differently and need to know what is new, and what updated.

[GV14982]

@pandoratoolbox are you looking to create a one-to-many relationship? If so, what about adding a post_id field to the media table, then adding it as a foreign key to the posts table? Then Hasura should auto suggest the relationships you need.

Lmk if that helps, or if I was off the mark in what you are trying to do.

[valstu]

Any updates to this? Also would like to do similar attachment logic without extra tables.

[pandoratoolbox]

@pandoratoolbox are you looking to create a one-to-many relationship? If so, what about adding a post_id field to the media table, then adding it as a foreign key to the posts table? Then Hasura should auto suggest the relationships you need.

Lmk if that helps, or if I was off the mark in what you are trying to do.

One media item can be used for many post items, one post item can use many media items

[GV14982]

Sounds like you are looking for a many-to-many relationship. You would want to create a pivot table to create the relationships between those two logical objects.

[pandoratoolbox]

Sounds like you are looking for a many-to-many relationship. You would want to create a pivot table to create the relationships between those two logical objects.

Thanks for the intention to assist. I'm aware of using an intermediate table to establish this relationship but this is not how I'm intending to structure my data. Unfortunately it seems like Hasura does not fulfill my requirements currently.

[rodrigoreis22]

We have the same requirement here but it's on the output type of an action, we return a field called: affected_activities_ids: [Int!] and then on Hasura we want to create a relationship from this array to the actual activity table so the client can get the actual activity objects and not just the IDs.

Using a many-to-many table here doesn't work because the data is mutable, not persisted.. it's calculated at runtime.

[rennehir]

@rodrigoreis22 did you manage to solve that already? I am having exactly the some issue currently.

Here is my custom action:

Here is the relationship I am trying to create between the resultIds and the actual results table:

And this is the error I get when saving the relationship configuration:

It seems that we are not allowed to make array relationships with arrays. Feels very illogical to me. Or am I just trying to approach this in a wrong way?

[manuFL]

Agree, we need this functionality too... any news on this?

[tirumaraiselvan]

Hey folks, this is quite a different type of relationship. Something from [Int!] to a table with column of type Int. It's not clear how the schema of such an API would look like. Would be keen to hear any possible suggestions here.

For table relationships, a workaround is to create a view with each item in the list "unnested" and then creating relationships from this view. This is possible using the unnest() function. See usage here: https://www.postgresqltutorial.com/postgresql-array/

For Actions, a workaround is to return a list of objects for each ID at the top-level instead of returning a list of IDs. For example, taking the schema from the comment above:

type Query {
  latestResults: [LatestResult!]
}

type LatestResult {
  someOtherId: Int!
  resultId: Int!
}

Now you can create a relationship between resultId and the table column. The downside of this approach is the need to duplicate someOtherId for each returned object.

[L-U-C-K-Y]

We also just ran into this issue, any other ideas than creating a view?

[kokroo]

Looking for the same!

EDIT:

I figured out a workaround using computed fields. I created a function in SQL to return rows based on some logic, and then I created a computed field that points to this function. Works beautifully!

[deathemperor]

Looking for the same!

EDIT:

I figured out a workaround using computed fields. I created a function in SQL to return rows based on some logic, and then I created a computed field that points to this function. Works beautifully!

@kokroo were you able to return relationship for the data of this computed field? Example: users.articles.category where articles are the rows returned from the computed field and category relates from article table to category table. Can you share your solution?

[pixelmaven]

@kokroo Also interested in your solution, cant wrap my head around what you're suggesting. cheers!

[kokroo]

@deathemperor No, this is not what I was doing.
@pixelmaven I created a view in the database and wrote a custom PL/pgSQL function which took a value from one column, processed it, and made another column out of this. You can try "computed functions" in Hasura which do pretty much the same thing if I am not wrong.