4

I have two tables - customer and order with one-to-many relationship. I want to retrieve in a single query all customer data and total value of his orders.

In raw SQL that would be something like this:

select customer.*, o.total from customer 
  inner join (select sum(value) as total, customer_id from 
      order group by customer_id) as o 
  on customer.id=i.customer_id

My question is - how to express this query in Sequelize without writing SQL? Is this even possible?

xersiee
  • 4,432
  • 1
  • 14
  • 23
  • Have you looked into the documentation? http://docs.sequelizejs.com/manual/tutorial/models-usage.html#manipulating-the-dataset-with-limit-offset-order-and-group – asosnovsky Dec 19 '17 at 19:56
  • Yes and I didn't find answer to my question. I know how to model aggregate functions and `group by` but I dont know how to put this stuff in subquery in join. – xersiee Dec 19 '17 at 19:59

2 Answers2

0

Try this:

const query = `
  SELECT
    table_a.table_a_id AS "table_a.table_a_id",
    table_b.table_b_id AS "table_b.table_b_id"
  FROM 
    table_a
  LEFT JOIN
    (SELECT * FROM table_b) AS table_b
  ON
    table_a.table_b_id = table_b.table_b_id
`;

const options = {
  model: models.table_a,
  mapToModel: true,
  nest: true,
  raw: true,
  type: sequelize.QueryTypes.SELECT
};

let tableAs = await db.sequelize.query(query, options);

Please note that you need the alias in the select statements. Seems like Sequelize.js differentiates the tables by the dot in the name.

caution: this doesn't work if table_a and table_b has "one to many" relationship because table_bs must be array. This seems like just overwrite table_b without making it array.

lechat
  • 390
  • 2
  • 15
-1

Hi xersee first you want to create two tables customer and order in sequalize.js format Then after summation of value column and customer_id column from table order put it into one table Then after that just join the new table with customer table in ORM Style Please refer below link it may be help for you. How to make join querys using sequelize in nodejs

shashank padelkar
  • 1
  • 2
  • Welcome to Stack Overflow! Please, always quote the most relevant part of an important link, in case the target site is unreachable or goes permanently offline. Thanks! – dferenc Dec 19 '17 at 20:49
  • Link you provided doesnt say anything about modelling subqueries. – xersiee Dec 20 '17 at 06:38
  • Please refer link-:https://stackoverflow.com/questions/27157687/is-it-possible-to-do-a-subquery-with-sequelize-js maybe this will help you. – shashank padelkar Dec 21 '17 at 16:00