search

sequelize / sequelize

Feature-rich ORM for modern Node.js and TypeScript, it supports PostgreSQL (with JSON and JSONB support), MySQL, MariaDB, SQLite, MS SQL Server, Snowflake, Oracle DB (v6), DB2 and DB2 for IBM i.
https://sequelize.org/
MIT License
29.56k stars 4.28k forks source link

[Suggestion] JSON output for .find() with raw & include #6334

Open inDream opened 8 years ago

inDream commented 8 years ago

What you are doing?

Use .find() with raw & include.

Post.findAll({
  raw: true,
  include: [Tag]
});

What do you expect to happen?

{id: 1, ..., Tag: {id: 1, ...}}

What is actually happening?

{id: 1, ..., 'Tag.id': 1, ...}}

Suggestion

As postgres 9.2 added row_to_json function, raw query can be constructed as:

SELECT "Posts".*, row_to_json("Tags".*) AS "Tag" FROM "Posts" INNER JOIN "Tags" ON ("Posts".tid = "Tags".id);

which give the expected JSON result and should improve .find() performance.

Related issues: #1830 #3682 #3885 #4311 #5685 #5708 #6122

mickhansen commented 8 years ago

We usually try to focus on solutions that work for all dialects.

inDream commented 8 years ago

@mickhansen MySQL also has JSON_OBJECT

mickhansen commented 8 years ago

@inDream In pre-json support versions aswell? SQLite/MSSQL? It's worth testing, we'll need some benchmarking.

inDream commented 8 years ago

@mickhansen MySQL only 5.7 support json, SQLite can use json1 extension and MSSQL can use FOR JSON to output json.

ezze commented 6 years ago

Guys, will it be addressed?

shivasurya commented 6 years ago

Same question. can we expect this?

shivasurya commented 6 years ago

@sushantdhiman this is a really best fos project and I love this seamless ORM integration with my DB. kindly let me know how to achieve this above result by temporary workaround or feature

stale[bot] commented 5 years ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

ezze commented 5 years ago

Still an issue, please, don't close.

dossin commented 4 years ago

For me on MySQL, it will be a major performance boost. Hope you will address it.

It seems that all major DB have this feature now. JS is slow compare to native DB function.

A first step could be to just use JSON_OBJECT at first level to give the the same results as now but on the DB side.

Exemple : SELECT JSON_OBJECT( 'lastName', lastName, 'firstName', firstName ) FROM users;

=>

[{"lastName": "Cell", "firstName": "Ance"} {"lastName": "Laure", "firstName": "Varner"}]

tomoat commented 2 years ago 
Post.findAll({
  raw: true,
  nest: true,
  include: [Tag]
});

This problem should be as early does not exist