SQL to Sequelize conversion

Question

I have the SQL query that I now need to rewrite as a sequelize.js query in node.js.

SELECT historyTable1.* FROM table1 historyTable1
 WHERE NOT EXISTS (
    SELECT * FROM table1 historyTable2 
    WHERE historyTable2.id=historyTable1.id AND
    historyTable2.date>historyTable1.date
  )

Format of data in table1:

id   date         amount      documentNo     paperID
1    2015/10/15   500         1234             34
1    2015/10/16   100         1332             33
2    2015/10/13   200         1302             21
2    2015/10/12   400         1332             33
3    2015/11/23   500         1332             43

I should get the output as ( get a column for an id with the latest date ):

id    date         amount      documentNo     paperID
1     2015/10/16   100         1332             33
2     2015/10/13   200         1302             21
3     2015/11/23   500         1332             43

not quite sure how this query needs to be structured to get the same results with sequelize.

Answer 1

From this issue#2787:

We know that sequelize generally have really lousy support for doing non-include joins. Your where not exists can be done by a sequelize.literal where.

So, the solution is:

import { sequelize } from '../../db';
import Sequelize, { DataTypes, Model } from 'sequelize';

class Table1 extends Model {}
Table1.init(
  {
    _id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    id: DataTypes.INTEGER,
    date: DataTypes.DATE,
    amount: DataTypes.INTEGER,
    documentNo: DataTypes.INTEGER,
    paperID: DataTypes.INTEGER,
  },
  { sequelize, tableName: 'table1' },
);

(async function test() {
  try {
    await sequelize.sync({ force: true });
    // seed
    await Table1.bulkCreate([
      { id: 1, date: new Date('2015/10/15'), amount: 500, documentNo: 1234, paperID: 34 },
      { id: 1, date: new Date('2015/10/16'), amount: 100, documentNo: 1332, paperID: 33 },
      { id: 2, date: new Date('2015/10/13'), amount: 200, documentNo: 1302, paperID: 21 },
      { id: 2, date: new Date('2015/10/12'), amount: 400, documentNo: 1332, paperID: 33 },
      { id: 3, date: new Date('2015/11/23'), amount: 500, documentNo: 1332, paperID: 43 },
    ]);
    // test 1
    const data1 = await Table1.findAll({
      where: Sequelize.literal(`
        NOT EXISTS (
            SELECT * FROM table1 historyTable2 
            WHERE historyTable2.id = "Table1".id AND
            historyTable2.date > "Table1".date
        )`),
      raw: true,
    });
    console.log(data1);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

Execution result:

Executing (default): DROP TABLE IF EXISTS "table1" CASCADE;
Executing (default): DROP TABLE IF EXISTS "table1" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "table1" ("_id"  SERIAL , "id" INTEGER, "date" TIMESTAMP WITH TIME ZONE, "amount" INTEGER, "documentNo" INTEGER, "paperID" INTEGER, PRIMARY KEY ("_id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'table1' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "table1" ("_id","id","date","amount","documentNo","paperID") VALUES (DEFAULT,1,'2015-10-14 16:00:00.000 +00:00',500,1234,34),(DEFAULT,1,'2015-10-15 16:00:00.000 +00:00',100,1332,33),(DEFAULT,2,'2015-10-12 16:00:00.000 +00:00',200,1302,21),(DEFAULT,2,'2015-10-11 16:00:00.000 +00:00',400,1332,33),(DEFAULT,3,'2015-11-22 16:00:00.000 +00:00',500,1332,43) RETURNING *;
Executing (default): SELECT "_id", "id", "date", "amount", "documentNo", "paperID" FROM "table1" AS "Table1" WHERE 
        NOT EXISTS (
            SELECT * FROM table1 historyTable2 
            WHERE historyTable2.id = "Table1".id AND
            historyTable2.date > "Table1".date
        );
[
  {
    _id: 2,
    id: 1,
    date: 2015-10-15T16:00:00.000Z,
    amount: 100,
    documentNo: 1332,
    paperID: 33
  },
  {
    _id: 3,
    id: 2,
    date: 2015-10-12T16:00:00.000Z,
    amount: 200,
    documentNo: 1302,
    paperID: 21
  },
  {
    _id: 5,
    id: 3,
    date: 2015-11-22T16:00:00.000Z,
    amount: 500,
    documentNo: 1332,
    paperID: 43
  }
]

Check the database:

node-sequelize-examples=# select * from table1;
 _id | id |          date          | amount | documentNo | paperID 
-----+----+------------------------+--------+------------+---------
   1 |  1 | 2015-10-14 16:00:00+00 |    500 |       1234 |      34
   2 |  1 | 2015-10-15 16:00:00+00 |    100 |       1332 |      33
   3 |  2 | 2015-10-12 16:00:00+00 |    200 |       1302 |      21
   4 |  2 | 2015-10-11 16:00:00+00 |    400 |       1332 |      33
   5 |  3 | 2015-11-22 16:00:00+00 |    500 |       1332 |      43
(5 rows)

node-sequelize-examples=# SELECT historyTable1.* FROM table1 historyTable1
node-sequelize-examples-#  WHERE NOT EXISTS (
node-sequelize-examples(#     SELECT * FROM table1 historyTable2 
node-sequelize-examples(#     WHERE historyTable2.id=historyTable1.id AND
node-sequelize-examples(#     historyTable2.date>historyTable1.date
node-sequelize-examples(#   )
node-sequelize-examples-# ;
 _id | id |          date          | amount | documentNo | paperID 
-----+----+------------------------+--------+------------+---------
   2 |  1 | 2015-10-15 16:00:00+00 |    100 |       1332 |      33
   3 |  2 | 2015-10-12 16:00:00+00 |    200 |       1302 |      21
   5 |  3 | 2015-11-22 16:00:00+00 |    500 |       1332 |      43
(3 rows)