• Jobs
  • Bootcamp
  • About Us
  • For professionals
    • Home
    • Jobs
    • Courses and challenges
    • Questions
    • Teachers
    • Bootcamp
  • For business
    • Home
    • Our process
    • Pricing
    • Assessments
    • Payroll
    • Blog
    • Sales
    • Salary Calculator

0

216
Views
sequelize - Specifying attributes for SELECT queries while using inner join

I want to fetch from the main table account and compare it with account history. there is a relationship On To many between the two table

SELECT account."userName", (account."postNumber" - accountHistory."postNumber") AS postNumber
    FROM public."Accounts" AS account  INNER JOIN public."AccountHistories" AS accountHistory
    ON account."userName" = accountHistory."userName" 
    WHERE accountHistory."scrappingDate" = '2022-01-08 23:59:39+01'
    ORDER BY postNumber DESC;

My question is how can I make the query with sequilize? I tried but I could not change the name of the attribute and do the subtraction

await this.accountRepository.findAll({    
    where: {'$accountHistory.createdAt$' : '2022-01-08 23:59:39+01'},
    include: [{
      model: AccountHistory,
      required: false
    }],
    attributes: ['userName',['$accountHistory.postNumber $', 'postNumber '],postNumber ],
   order: [[$accountHistory.createdAt$', 'DESC']],
                    })
9 months ago · Juan Pablo Isaza
1 answers
Answer question

0

You probably need to use Sequelize.literal to perform the calculation as an aliased attribute, e.g.:

const { literal } = require('sequelize')

await this.accountRepository.findAll({    
    where: {'$accountHistory.createdAt$' : '2022-01-08 23:59:39+01'},
    include: [{
      model: AccountHistory,
      required: false
    }],
    attributes: [
      'userName',
      [
        literal('"account"."postNumber" - "accountHistory"."postNumber"'),
        'postNumber'
      ]
    ],
   order: [[$accountHistory.createdAt$', 'DESC']],
})

That exact syntax might not work depending on your SQL dialect - I'm not familiar with the $ symbols you are using

9 months ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post job Pricing Our process Sales
Legal
Terms and conditions Privacy policy
© 2023 PeakU Inc. All Rights Reserved.