Responses to the discussion on Github
Collaborator
can you please move this over to a bug report, I assume this issue only happens to you on Mongo since the default sort for SQL databases is the id which is an integer.
Author
Hi @derrickmehaffy
I’m actually hosting on PostgreSQL; Server is a Digital Ocean droplet, installed via 1-Click; Strapi Version: 3.1.5
See the 2 snapshots for Admin & customized API response. No parameters were applied to the api request.
Collaborator
Is this for a relation field?
Author
Is this for a relation field?
@derrickmehaffy No. The shared extract is actally for a collection with just text types.
Collaborator
I’m not sure I understand what you mean?
Author
Hi @derrickmehaffy
I’m just trying to figure out why API objects are not sorted by id by default (like in the DB) - or at least, by the Default sort attribute.
The snapshot shared shows the default sort order for API objects is random.
Collaborator
The default response is sorted by ID for the root level, any relational connections are not (they are but in certain relation types they are sorted by the middle table that links the two).
If you are using a SQL database (bookshelf connector) in your ./config/database.js in the options object place debug: true which will show the raw SQL query being execute by knex.
Collaborator
I created a test project with two models:
- Article (title,body)
- Comment (text)
With a relation between them set as “Article has many Comments”, creating a single article with 5 comments on it these are the raw Knex queries made to the SQLite database:
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 100 ],
__knexQueryUid: 'e3a3fd2e-49c4-4709-a01d-e0e0896f7eba',
sql: 'select `articles`.* from `articles` limit ?'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1 ],
__knexQueryUid: '212ee6ea-96e2-4782-b89b-b6899968fbce',
sql: 'select distinct `comments`.* from `comments` where `comments`.`article` in (?)'
}
With SQLite the default sort is based on the primary key (in this case id) in an ASC order, thus the default would largely be based on the created_by date since it and the id will follow the same pattern.
In certain cases such as using a manyWay relation this won’t be the case as in SQL we need 3 tables:
- Article
- Comment
- Article__Comments join table that links them
In this case here are the two SQL statements:
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 100 ],
__knexQueryUid: '53fc0eea-035c-4149-a5a2-d988003b10e0',
sql: 'select `articles`.* from `articles` limit ?'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1 ],
__knexQueryUid: 'c3082982-0c38-475d-8b38-d0c7f7159f34',
sql: 'select distinct `comments`.*, `articles__comments`.`article_id` as `_pivot_article_id`, `articles__comments`.`comment_id` as `_pivot_comment_id` from `comments` inner join `articles__comments` on `articles__comments`.`comment_id` = `comments`.`id` where `articles__comments`.`article_id` in (?)'
}
And a snip from the database table, as you can see it’s sorting by the id of that table and not the Comments one, thus the comment_id is out of order.
Collaborator
As a side point I was going to show you an example using GraphQL but I did manage to find a bug, so if the issue for you is related to relations, there is a bug with the manyWay relation type.
See: #8099
