CODEWITHSUNDEEP
mysqlsqlnode.jssequelize.js
adamlmiller
adamlmiller

Reputation: 532

Using MySQL FIELD Function within Sequelize

I need to replicate the following in Sequelize. Unfortunately, I have not been able to replicate it completely and not enough to actually get it to work. I need to ORDER BY FIELD.

Working Query

SELECT * FROM invoices ORDER BY FIELD(invoice_status_key, 'unpaid', 'overdue', 'open', 'paid') ASC;

Here is the code that I have that is close but not exact and not working:

Invoice.findAll({ order: [ [sequelize.fn('FIELD', ['invoice_status_key', 'open', 'unpaid', 'overdue', 'paid']), 'ASC'] ], logging: console.log });

This code produces the following query:

SELECT id, company_id, customer_id, job_id, invoice_status_key, identifier, subtotal, tax, shipping, total, summary, due_at, paid_at, created_at, updated_at FROM invoices AS Invoice ORDER BY FIELD('invoice_status_key', 'open', 'unpaid', 'overdue', 'paid') ASC;

Presumably what is wrong is the field invoice_status_key is being quoted inside the function while the function expects it to NOT be quoted. I have tried numerous ways and this is the closest that I have gotten but unfortunately it does not yield the results in the correct order.

Any help is appreciated.

Upvotes: 0

Views: 363

Answers (1)

adamlmiller
adamlmiller

Reputation: 532

I was able to use a literal and raw query to accomplish what I was after.

let invoices = await Invoice.findAll({ order: [ sequelize.literal("FIELD(invoice_status_key, 'open', 'unpaid', 'overdue', 'paid') ASC")] ] });

Which produced the end result as expected.

Upvotes: 1

Related Questions