Single object lookup by unique key

[karibertils]

It would be useful if we could lookup single objects by their unique fields other than the primary key.

user_by_email(email: "an@example.org") { id email }

For composed unique fields it could be something along the lines of

order_by_user_id_and_invoice_id(user_id: 1, invoice_id: 1) { id email }

I presume we could automatically get this functionality based on the unique indexes in postgresql. It is a bit nicer than with the where clause when we know there will only be a single item.

[0x777]

We should also generate the corresponding mutations.

[revskill10]

Is this Rails's ActiveRecord pattern ?

[andrewalex]

This would make things cleaner. Whatever design decision should take into consideration views/functions/etc

[tafelito]

what about allowing returning single items for custom functions

if I want to create a function that returns the current logged user, right now it will return an array instead of a single item

[c-spencer]

Want to echo a desire for this, as it's a bit awkward to be returning arrays that are known to only return 0-1 records. I think just allowing custom functions to be manually flagged as 'singular' in the metadata would be ideal. For a concrete use case, this is the kind of function I'm using currently:

CREATE OR REPLACE FUNCTION "accountByUsername"(search text)
RETURNS SETOF accounts AS $$
    SELECT * FROM accounts WHERE lower(username) = lower(search)
$$ LANGUAGE sql STABLE;

Where there's a unique index on lower(username).

[maxcan]

@tirumaraiselvan @0x777 any updates on this? This isn't a major blocker to anything since the workaround is straightforward but it would make a lot of my code far cleaner and more concise.

[gsnunes]

Would be nice if we had a query_by_uk exactly like query_by_pk but base on the unique keys, this way we don't need to use the search queries that return an array.

[amnaveenriaz]

@tirumaraiselvan @0x777 Do you any updates on this? Any chance we can see this in the 2.1 Release.

[joanrodriguez]

This would greatly help

[alherd-by]

any updates on this?

[furkan-vibely]

Is there any update?

[SimEli]

I would also like to have a cleaner solution

[smeevil]

 👀

[BrianHung]

Want to echo a desire for this, as it's a bit awkward to be returning arrays that are known to only return 0-1 records. I think just allowing custom functions to be manually flagged as 'singular' in the metadata would be ideal. For a concrete use case, this is the kind of function I'm using currently:

CREATE OR REPLACE FUNCTION "accountByUsername"(search text)
RETURNS SETOF accounts AS $$
    SELECT * FROM accounts WHERE lower(username) = lower(search)
$$ LANGUAGE sql STABLE;

Where there's a unique index on lower(username).

Is there a custom way to autogenerate these for multiple tables from a user-side in hasura console?

[mbrimmer83]

👀

[dwoske]

This issue is nearly blocking me from selling Hasura to my organization. We have synthetic UUIDs or integers as PK for almost all of our tables, and unique key constraints which are really used to lookup and refer to entities. A get_by_uk is a no-brainer necessity for our GraphQL apis, as using the where syntax is terrible for this use-case. Our hand-made GraphQL resolvers can't be replaced by Hasura without this feature.

[a-chocolate-bear]

Hi @dwoske I've reached to you via email regarding this.

[manasag]

Thanks everyone for your comments and patience on this issue. We have been closely listening into all the feedback and requests from the community, and have been working on a re-imagined, re-architectured Hasura, that tackles many of these from ground up. We are pleased to announce that we are launching V3-Alpha of Hasura (Data Delivery Network) on our next Community call on Nov 30. The new metadata structure in V3 is designed to be highly flexible and declarative, to support the perfect GraphQL schema you would like to achieve, while leveraging the database specific features as much as possible. In V3, you can customise and decide what key you would like to use for lookups (and not fixed to the primary key). We would request to join this community call to learn more about Hasura V3. Post launch, we will update this issue with relevant details.

[dameleney]

This is possible in Hasura V3 by defining the appropriate selectUniques configuration in your metadata. Using the example from the original post.

GraphQL order_by_user_id_and_invoice_id(user_id: 1, invoice_id: 1) { id email }

The model would look something like this...

kind: Model
version: v1
definition:
  ... # Other model info
  graphql:
    selectUniques:
      - queryRootField: user_by_email
        uniqueIdentifier:
          - email
      - queryRootField: order_by_user_id_and_invoice_id
        uniqueIdentifier:
          - user_id
          - invoice_id

Look forward to hearing your feedback. More information can be found in the V3 documentation here. V3 data domain modeling docs