is work well, no SQL error,
SQL query
SELECT `AccountTrade`.`id`, `AccountTrade`.`user_id`, `AccountTrade`.`account_number`, `AccountTrade`.`platform`, `AccountTrade`.`currency`, `AccountTrade`.`account_type`, `AccountTrade`.`password`, `AccountTrade`.`createdAt`, `AccountTrade`.`updatedAt`, `account_trade_transactions`.`amount` FROM `AccountTrades` AS `AccountTrade` LEFT OUTER JOIN `AccountTradeTransactions` AS `account_trade_transactions` ON `AccountTrade`.`id` = `account_trade_transactions`.`account_trade_id`;
but is not working by this way.
const datatable = require(sequelize-datatable); const data = await datatable(AccountTrade, req.query, { raw: true, attributes: { include: [db.sequelize.fn('SUM', db.sequelize.col('account_trade_transactions.amount'))], }, include: [{ model: AccountTradeTransaction, as: 'account_trade_transactions', }], })
SQL error is
Unknown column 'account_trade_transactions.amount' in 'field list'
the Query of SQL
SELECT `AccountTrade`.*, `account_trade_transactions`.`id` AS `account_trade_transactions.id`, `account_trade_transactions`.`account_trade_id` AS `account_trade_transactions.account_trade_id`, `account_trade_transactions`.`date` AS `account_trade_transactions.date`, `account_trade_transactions`.`amount_type` AS `account_trade_transactions.amount_type`, `account_trade_transactions`.`amount` AS `account_trade_transactions.amount`, `account_trade_transactions`.`note` AS `account_trade_transactions.note`, `account_trade_transactions`.`version` AS `account_trade_transactions.version`, `account_trade_transactions`.`createdAt` AS `account_trade_transactions.createdAt`, `account_trade_transactions`.`updatedAt` AS `account_trade_transactions.updatedAt` FROM (SELECT `AccountTrade`.`id`, `AccountTrade`.`user_id`, `AccountTrade`.`account_number`, `AccountTrade`.`platform`, `AccountTrade`.`currency`, `AccountTrade`.`account_type`, `AccountTrade`.`password`, `AccountTrade`.`createdAt`, `AccountTrade`.`updatedAt`, SUM(`account_trade_transactions`.`amount`) FROM `AccountTrades` AS `AccountTrade` ORDER BY `AccountTrade`.`id` DESC LIMIT 0, 10) AS `AccountTrade` LEFT OUTER JOIN `AccountTradeTransactions` AS `account_trade_transactions` ON `AccountTrade`.`id` = `account_trade_transactions`.`account_trade_id` ORDER BY `AccountTrade`.`id` DESC;
there is another way for add custom attributes? which is include with another model?
pim97
commented
3 years ago
query with model.findAll
const data = await AccountTrade.findAll({ raw: true, attributes: { include: [db.sequelize.col('account_trade_transactions.amount')] }, include: [{ model: AccountTradeTransaction, as: 'account_trade_transactions', required: false, attributes: [] }] })is work well, no SQL error, SQL query
SELECT `AccountTrade`.`id`, `AccountTrade`.`user_id`, `AccountTrade`.`account_number`, `AccountTrade`.`platform`, `AccountTrade`.`currency`, `AccountTrade`.`account_type`, `AccountTrade`.`password`, `AccountTrade`.`createdAt`, `AccountTrade`.`updatedAt`, `account_trade_transactions`.`amount` FROM `AccountTrades` AS `AccountTrade` LEFT OUTER JOIN `AccountTradeTransactions` AS `account_trade_transactions` ON `AccountTrade`.`id` = `account_trade_transactions`.`account_trade_id`;but is not working by this way.
const datatable = require(sequelize-datatable);const data = await datatable(AccountTrade, req.query, { raw: true, attributes: { include: [db.sequelize.fn('SUM', db.sequelize.col('account_trade_transactions.amount'))], }, include: [{ model: AccountTradeTransaction, as: 'account_trade_transactions', }], })SQL error is
Unknown column 'account_trade_transactions.amount' in 'field list'the Query of SQL
SELECT `AccountTrade`.*, `account_trade_transactions`.`id` AS `account_trade_transactions.id`, `account_trade_transactions`.`account_trade_id` AS `account_trade_transactions.account_trade_id`, `account_trade_transactions`.`date` AS `account_trade_transactions.date`, `account_trade_transactions`.`amount_type` AS `account_trade_transactions.amount_type`, `account_trade_transactions`.`amount` AS `account_trade_transactions.amount`, `account_trade_transactions`.`note` AS `account_trade_transactions.note`, `account_trade_transactions`.`version` AS `account_trade_transactions.version`, `account_trade_transactions`.`createdAt` AS `account_trade_transactions.createdAt`, `account_trade_transactions`.`updatedAt` AS `account_trade_transactions.updatedAt` FROM (SELECT `AccountTrade`.`id`, `AccountTrade`.`user_id`, `AccountTrade`.`account_number`, `AccountTrade`.`platform`, `AccountTrade`.`currency`, `AccountTrade`.`account_type`, `AccountTrade`.`password`, `AccountTrade`.`createdAt`, `AccountTrade`.`updatedAt`, SUM(`account_trade_transactions`.`amount`) FROM `AccountTrades` AS `AccountTrade` ORDER BY `AccountTrade`.`id` DESC LIMIT 0, 10) AS `AccountTrade` LEFT OUTER JOIN `AccountTradeTransactions` AS `account_trade_transactions` ON `AccountTrade`.`id` = `account_trade_transactions`.`account_trade_id` ORDER BY `AccountTrade`.`id` DESC;there is another way for add custom attributes? which is include with another model?