How to convert SQL query to sequelize? (I'm new)

Question

I need to convert this SQL to Sequelize. I'm new here and I can't convert it. I have Error on [tem_interesse], the rest works, but i don't know how to convert it, can someone show me the process to convert? Thank you

select distinct
  aux.nome,
  aux.media,
  case when fi.idfuncionario is not null
  then 1
  else 0 end [tem_interesse]
from {
  select e.nome, e.id, round(avg(fc.nivelcomp), 2) as media
  from employee e
  inner join funcionarios_competencias fc on fc.idfuncionario = e.id
  where fc.idcompetencia in (1,3)
  group by e.nome, e.id
}aux
left join funcionarios_interesses fi on fi.idfuncionarios = aux.id and fi.idinteresse in (1,2)
where aux.media is not null
order by aux.media desc, [tem_interesse] desc
limit 3

You may want to add more information, such as what errors you get from this code. — nunop, Jul 03 '20 at 11:26
Yes, i edit my question. I have a error on [tem_interesse], i don't know Sequelize too well, i work more on SQL but i need to convert it to Sequelize. Can you show a exemple to convert step by step my info SQL? Thank you — raxnon, Jul 03 '20 at 13:12

score 0 · Answer 1 · answered Jul 04 '20 at 05:35

Please add your models here. That would help anybody who wants to guide you towards the solutions. The community is strong but due to lack of information - it would require too much time for anybody to help you, so you might miss the bus.

Associations - You need to define associations here - since joins are involved. They are defined at model level first - in the definition. Then the association is defined for sequelize to know that there is a relation. Else if the association is missed - and only model relation is defined - the FK constraint shows in the DB but Sequelize won't know how to get the relation parsed. For defining the models with relation [foreign key] and defining the association - you can take a quick look at this link. You will get to know both from here. Reading more about BelongsTo and HasMany would help you.
Grouping please see this link - link
Case When - please see this link - link
Order - please see this link - link

The most important part would be to follow the links given in the discussion link - given for item #1 above. Even after defining the models and association well - the db.js and index.js file needs to be defined well - so the associations loaded at the right time. And the model definition worked for me in the format updated.

If done carefully, you will save some pain. Try to achieve the order described above, fix your model, define relation through Refrences, define associations and then sync DB so the relations persist. Once this is done - try to query using "Include" which will get you an associated record. Once this part is done - rest should be pretty straight forward. Try it and post back your code.

How to convert SQL query to sequelize? (I'm new)

1 Answers1