Order by not working? about sqlmancer

[danielrearden]

That query fetches all customers (in any order) and for each one, fetches the invoices. The invoices themselves are sorted by the customer's last name (which in this case wouldn't do anything since they're already constrained to a single customer). If you want to order the customers by last name:

{
  customers(orderBy: { lastName: ASC }) {
    lastName
    invoices {
      total
    }
  }
}

Ordering the customers by last name and each customer's invoices by the total:

{
  customers(orderBy: { lastName: ASC }) {
    lastName
    invoices (orderBy: { total: DESC }){
      total
    }
  }
}

[JeffML]

Alright, I'm going to be argumentative here, and push for reopening this, possibly under a different title. Here are two queries, one with a nonworking orderBy, the other with:

query no1 {
  customers {
    lastName
    invoices (orderBy: {customer:{lastName:ASC}}){
      createdOn
    }
  }
}

query no2 {
  invoices (orderBy: {customer:{lastName:ASC}}){
    customer {
      lastName
    }
    total
  }
}

The no2 orderBy clause is identical to the nonworking no1 clause. The problem as I see it is the clause in no2 works, whereas the one in no1 is a null operation.

I view the ability to specify non-functioning operations as bug, not a feature. I can't think of a reason (there may be one) why the results of the relational join operation between customer and invoice can't be ordered independently of the hierarchical relation that is presented by GraphQL. I would expect something like the following to be generated:

select `c1`.`CustomerId` as `id`, `c1`.`FirstName` as `firstName`, `c1`.`LastName` as `lastName`, `c1`.`Email` as `email`, (select json_group_array(json(`i1`.`o`)) from (select json_object(\'id\', `i2`.`InvoiceId`, \'total\', `i2`.`Total`, \'createdOn\', `i2`.`InvoiceId`) as `o` from `invoices` as `i2` where `c1`.`CustomerId` = `i2`.`CustomerId` orderby (select `c2`.`LastName` from `customers` as `c2` where `i2`.`CustomerId` = `c2`.`CustomerId`) asc) as `i1`) as `invoices` from `customers` as `c1`
order by 'lastName';

The only difference I added was the orderBy clause at the end. The order by is on the result set, which is the product of the join (c1, i1), with the column alias 'lastName'. I'm not that familiar with Postgres' JSON query support, but I don't think that matters here*, though maybe there is a SqlLite3 restriction of some kind on order by syntax.

In any case, if for some reason an orderBy parameter can't be respected, an error or warning should be thrown. That's more work, I know, but I don't think it's unreasonable.

* Observations:

1. why is Email in the SELECT statement? It wasn't asked for. For many-columned tables, and those with BLOBs, it would be really inefficient to fetch more fields than requested.
2. I see invoices is stored as JSON and aliased to i2 in the inline join in the select clause, then outer alias as i1 (I think). I can't grok why JSON is used here, though. Maybe I need to finish reading the docs.
3. Why the use of relational expressions in WHERE instead of JOIN statements? I'm sure there are reasons, just asking.

[danielrearden]

Thanks for your comments. Maybe I'm not fully groking the point you're trying to raise, so bear with me if I'm misunderstanding. In our example, we have a graph of data representing a list of customers and each customer's invoices:

query no1 {
  customers {
    lastName
    invoices {
      createdOn
    }
  }
}

We can provide an orderBy to both the customers field and the invoices field. In the former case, we're saying "get all customers and sort them by these criteria". In the latter case, we're saying "for each customer, get their invoices and sort that set of invoices by these criteria".

In query no1, we don't provide any criteria for sorting customers so their order will not be guaranteed. Query no1 does provide a sort criteria for invoices. So for each customer, we return the invoices sorted by the provided criteria. However, the criteria is {customer:{lastName:ASC}}. That does not mean "sort the customers by last name". It means "sort the invoices according to each invoice's associated customer's last name". Looking at the generated query, this is exactly what it does:

select `c1`.`CustomerId`                                                           as `id`,
       `c1`.`FirstName`                                                            as `firstName`,
       `c1`.`LastName`                                                             as `lastName`,
       `c1`.`Email`                                                                as `email`,
       (select json_group_array(json(`i1`.`o`))
        from (select json_object('id', `i2`.`InvoiceId`, 'total', `i2`.`Total`, 'createdOn', `i2`.`InvoiceId`) as `o`
              from `invoices` as `i2`
              where `c1`.`CustomerId` = `i2`.`CustomerId`
              order by (select `c2`.`LastName`
                        from `customers` as `c2`
                        where `i2`.`CustomerId` = `c2`.`CustomerId`) asc) as `i1`) as `invoices`
from `customers` as `c1`

Again, it's not that this query doesn't work -- it's just that the value we're sorting by (invoice.customer.lastName) happens to be the same for all the records we're sorting. It's no different than if the invoices all had the same total and we sorted by that -- that query also "wouldn't work" in the same sense.

[danielrearden]

The extraneous columns are actually a bug so thanks for catching that! Looks like a regression introduced by a recent change. I opened #61 to track it. Will fix ASAP.

[danielrearden]

Utilizing JSON aggregation and correlated subqueries allows us to easily apply ORDER BY, WHERE, LIMIT and OFFSET clauses to each set of related records independently. Just using joins isn't really viable unless we used lateral joins but those are only supported by Postgres.

JSON aggregation also ensures that the driver returns the data in the shape that we need, without needing to do additional processing after the fact. This is in contrast with the approach taken by many other libraries, like Sequelize, where the database returns something like

SELECT "User"."id",
       "User"."createdAt",
       "User"."updatedAt",
       "Addresses"."id"                                                                       
       "Addresses"."createdAt"                                                                
       "Addresses"."updatedAt"                                                                
       "Addresses->UserAddressWithExtraNamePaddingHelloWorldWithExtraNamePadding"."createdAt",
       "Addresses->UserAddressWithExtraNamePaddingHelloWorldWithExtraNamePadding"."updatedAt",
       "Addresses->UserAddressWithExtraNamePaddingHelloWorldWithExtraNamePadding"."UserId",
       "Addresses->UserAddressWithExtraNamePaddingHelloWorldWithExtraNamePadding"."AddressId"

and the library has to transform it into the appropriate JSON structure after the fact.

[JeffML]

I understand what you are saying. Any nested orderBy operates within the constraints of the parent context. You could achieve something similar by doing a correlated subquery in SQL. And yes, your right: the orderBy statement on the Invoice in *no1* "works" but there's nothing really to order by; there's only one lastName (one customer) for each invoice. In a heavily nested query, this is a kind of thing that could easily be missed, though. It seems possible to throw an error if the orderBy references a field in a ancestor...maybe? I'd have to think about it. Okay, my head is a little clearer now. Thanks for detailed clarification.

…

On Wed, May 20, 2020 at 4:57 PM Daniel Rearden ***@***.***> wrote: Thanks for your comments. Maybe I'm not fully groking the point you're trying to raise, so bear with me if I'm misunderstanding. In our example, we have a graph of data representing a list of customers and each customer's invoices: query no1 { customers { lastName invoices { createdOn } } } We can provide an orderBy to both the customers field and the invoices field. In the former case, we're saying "get all customers and sort them by these criteria". In the latter case, we're saying "for each customer, get their invoices and sort that set of invoices by these criteria". In query no1, we don't provide any criteria for sorting customers so their order will not be guaranteed. Query no1 *does* provide a sort criteria for invoices. So for each customer, we return the invoices sorted by the provided criteria. However, the criteria is {customer:{lastName:ASC}}. That does *not* mean "sort the customers by last name". It means "sort the invoices according to each invoice's associated customer's last name". Looking at the generated query, this is exactly what it does: select `c1`.`CustomerId` as `id`, `c1`.`FirstName` as `firstName`, `c1`.`LastName` as `lastName`, `c1`.`Email` as `email`, (select json_group_array(json(`i1`.`o`)) from (select json_object('id', `i2`.`InvoiceId`, 'total', `i2`.`Total`, 'createdOn', `i2`.`InvoiceId`) as `o` from `invoices` as `i2` where `c1`.`CustomerId` = `i2`.`CustomerId` order by (select `c2`.`LastName` from `customers` as `c2` where `i2`.`CustomerId` = `c2`.`CustomerId`) asc) as `i1`) as `invoices` from `customers` as `c1` Again, it's not that this query doesn't work -- it's just that the value we're sorting by (invoice.customer.lastName) happens to be the same for all the records we're sorting. It's no different than if the invoices all had the same total and we sorted by that -- that query also "wouldn't work" in the same sense. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#60 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA2VY3LY6NBU4ZIW3ZTDCNLRSRVAPANCNFSM4NGE7TQQ> .

[JeffML]

Okay, thanks, I'm learning new stuff. I'll forget it soon, but I can say I was smarter once upon a time.

…

On Wed, May 20, 2020 at 5:20 PM Daniel Rearden ***@***.***> wrote: Utilizing JSON aggregation and correlated subqueries allows us to easily apply ORDER BY, WHERE, LIMIT and OFFSET clauses to each set of related records independently. Just using joins isn't really viable unless we used *lateral* joins but those are only supported by Postgres. JSON aggregation also ensures that the driver returns the data in the shape that we need, without needing to do additional processing after the fact. This is in contrast with the approach taken by many other libraries, like Sequelize, where the database returns something like SELECT "User"."id", "User"."createdAt", "User"."updatedAt", "Addresses"."id" "Addresses"."createdAt" "Addresses"."updatedAt" "Addresses->UserAddressWithExtraNamePaddingHelloWorldWithExtraNamePadding"."createdAt", "Addresses->UserAddressWithExtraNamePaddingHelloWorldWithExtraNamePadding"."updatedAt", "Addresses->UserAddressWithExtraNamePaddingHelloWorldWithExtraNamePadding"."UserId", "Addresses->UserAddressWithExtraNamePaddingHelloWorldWithExtraNamePadding"."AddressId" and the library has to transform it into the appropriate JSON structure after the fact. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#60 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA2VY3L33S27Y4EITXV2CI3RSRXVDANCNFSM4NGE7TQQ> .

[danielrearden]

I understand what you are saying. Any nested orderBy operates within the constraints of the parent context. You could achieve something similar by doing a correlated subquery in SQL. And yes, your right: the orderBy statement on the Invoice in no1 "works" but there's nothing really to order by; there's only one lastName (one customer) for each invoice. In a heavily nested query, this is a kind of thing that could easily be missed, though. It seems possible to throw an error if the orderBy references a field in a ancestor...maybe? I'd have to think about it. Okay, my head is a little clearer now. Thanks for detailed clarification.

This is also a good example of where you might start with the @orderBy directive initially and then drop it and just provide your own modified type(s). This way you can get a MVP schema rolled out quickly, but then come back and tweak the input types and filter out options that don't really make sense in the context of your business logic.