Distinct on (column) in PostGraphQL?

[keehun]

We are early in a project using React and Postgres, and until now, we had been using Express to serve custom endpoints to an API. We are looking to switch over to GraphQL.

I am wondering how to take advantage of Postgres' distinct on (column) feature. Is there a way to ask GraphQL to only return distinct results?

For example, we have a database of tens of thousands of cars. Each car has a year, make, and model name. We want to query GraphQL and get back all the years represented in the database, all the models, and all the makes.

Currently, in our API, we're running the following query to get all the available years on a specific make and model: select distinct on (year) year from all_cars WHERE make = $1 AND model = $2

Can we do this in GraphQL?

Thank you

[keehun]

Would anyone advise us to change the schema so that we have a table of years and a table of makes and that each model reference the years/makes with its ID?

[benjie]

So: yes, I would recommend normalizing your data; but what you want to achieve is possible without doing so:

create function years_by_make_and_model(make string, model string) returns setof int as $$
  select distinct on (year) year
  from all_cars
  where all_cars.make = years_by_make_and_model.make
  and all_cars.model = years_by_make_and_model.model;
$$ language sql stable;

[benjie]

You probably want to make that strict too since you wouldn't want make or model to be null.

[benjie]

To keep things manageable I'm going to close this issue as I think it's solved; but if not or you require further help please re-open it.

[PedroD]

No, this issue is not solved.

Sometimes you want to make a SELECT DISTINCT columnA FROM table WHERE columnB = xxx AND columnC > yyy and for this type of query no normalization can save you, you really need a DISTINCT option that allows you to select which columns you want to distinct by.

Can this be done?

Thanks.