Order by nested entity

[olirice]

Add nested entities to <Entity>OrderBy types

For to-one relationships, this could be order_by: {author: {id: desc}}

query {
  articles (
    order_by: {author: {id: desc}}
  ) {
    id
    ...
    author {
      id
    }
  }
}

and (optionally) for to-many relationships we could filter on aggregates (like count)

query {
  authors (
    order_by: {
      articles_aggregate: {count: desc}
    }
  ) {
    id
    name
    articles_aggregate {
      aggregate{
        count
      }
    }
  }
}

• example shown in hasura style but would likely vary slightly

[moarwick]

Hello @olirice, just a gentle nudge... our app is still in dev but this feature is becoming a bit of a blocker. might you have an ETA? 🙏

[olirice]

unfortunately we can't commit to an ETA

we're actively interviewing for another rust dev which will speed up feature development on pg_graphql

[wesharper]

Is there a workaround for this? I've experimented with functions and views that have order by clauses, but those clauses don't seem to be respected at query time.

[olirice]

but those clauses don't seem to be respected at query time.

If you can provide a reproducible example where the order by is not respected i'd be happy to take a look

[wesharper]

@olirice I've read the docs front-to-back and realized that the behavior is indeed documented, just unexpected. Basically, by default, queries are ordered by primary key, which makes any sql-side ordering effectively useless in certain scenarios. I'll go into our use-case in depth, but that's the gist. Our workaround is to just hoist the values we need to order by to the top level of the query and use the pg_graphql order by.

I'm building a tool that allows users to create complex reports that have many associated versions so they can jump back and do point-in-time recovery. To accomplish this, at least for now, we have a reports table and a report_versions table that work in tandem. reports stores high-level information and metadata about the report, like the user who created it, the report type, and the publication date. report_versions stores all the meaningful data for a report at each point in time.

We have a view in the app that displays a list of reports, where we have meaningful information from both tables. In the app, we'd like the user to be able to filter and order by fields in the "report version", while accessing it as a child of the top-level report behind the scenes. Our query looks something like this:

query ReportPage {
  reportsCollection {
    edges {
      node {
        author {
          firstName
          lastName
        }
        type
        createdAt
        mostRecentVersion {
          importantDate
          foo
          bar
          baz
        }
      }
    }
  }
}

Note: This description also illustrates our real-world run-ins with #88.

In this example, we wanted to order by importantDate by default. To do this, we tried two separate but similar approaches.

1. Create a view called ordered_reports_v.
2. Create a function called ordered_reports.

The underlying query behind both the view and the function was essentially:

SELECT * FROM reports
  JOIN report_versions ON reports.most_recent_version_id = report_versions.id
  ORDER BY report_versions.important_date DESC NULLS LAST;

If you were to look at the view or run the sql function, the data would come back in the desired order. However, because pg_graphql orders by primary key, this ordering wasn't honored and in fact was likely doing a bunch of unnecessary work. The workaround we're using now is to hoist the important_date to the top level of a view so we can use the pg_graphql mechanisms i.e:

CREATE OR REPLACE VIEW ordered_reports_v AS
SELECT reports.*, report_versions.important_date FROM reports
  JOIN report_versions ON reports.most_recent_version_id = report_versions.id;

We do the same thing for filterable fields.

Our ideal API might look something like:

query {
  reportsCollection(orderBy: [{ mostRecentVersion: { importantDate: DescNullsLast } }]) {
    # ...fields
  }
}

[bobbybol]

Hi @olirice, any progress on this? Thanks ;)