const allItems = await findAndPaginate(CartItems, req.query, {
attributes: [
["cart_items_id", "cartId"],
["cart_item_mrp", "mrp"],
["cart_item_discount", "discount"],
["cart_item_no_of_sessions", "sessionCount"],
["cart_item_status", "status"],
["cart_item_emi", "emi"],
["cart_item_created_at", "createdAt"],
],
include: [
{
model: User,
as: "createdBy",
// attributes: []
attributes: ["name"]
},
{
model: CartPaymentLog,
attributes: [
["cart_payment_log_id", "paymentLogId"],
["cart_payment_log_payable_amount", "payableAmt"],
["cart_payment_log_created_at", "createdDate"],
[Sequelize.literal(`
CASE WHEN \`CartPaymentLogs\`.\`cart_payment_log_payment_invoice_id\` IN (SELECT payment_capture_invoice_id from payment_capture) THEN TRUE ELSE FALSE END
`), "paidStatus"]
],
},
],
where: {
cart_item_lead_id: leadId,
cart_item_status: {
[Sequelize.Op.in]: isWishList ? ['4'] : ['0', '1']
},
cart_item_deleted_status: '0',
},
order : [["cart_item_created_at","desc"]]
})
I need to arrange all the inner attributes fields into its parent attributes array
Need : I have already wrote a function to handle with search,filter,sorting etc(findAndPaginate) which uses findAndCountAll method of Sequalize
findAndPaginate fn only works if the attributes are in the outermost attributes array
I tried by using Sequalize.col() and Sequalize.literal()
, but its not working since the above generated sql is in weird way
SELECT `CartItems`.*,
`createdBy`.`userID` AS `createdBy.userID`,
`createdBy`.`name` AS `createdBy.name`,
`CartPaymentLogs`.`cart_payment_log_id` AS `CartPaymentLogs.cart_payment_log_id`,
`CartPaymentLogs`.`cart_payment_log_id` AS `CartPaymentLogs.paymentLogId`,
`CartPaymentLogs`.`cart_payment_log_payable_amount` AS `CartPaymentLogs.payableAmt`,
`CartPaymentLogs`.`cart_payment_log_created_at` AS `CartPaymentLogs.createdDate`,
createdBy.name AS `CartPaymentLogs.createdBy`,
CASE
WHEN `CartPaymentLogs`.`cart_payment_log_payment_invoice_id` IN
(SELECT payment_capture_invoice_id
FROM payment_capture) THEN TRUE
ELSE FALSE
END AS `CartPaymentLogs.paidStatus`
FROM
(SELECT `CartItems`.`cart_items_id`,
`CartItems`.`cart_items_id` AS `cartId`,
`CartItems`.`cart_item_mrp` AS `mrp`,
`CartItems`.`cart_item_discount` AS `discount`,
`CartItems`.`cart_item_no_of_sessions` AS `sessionCount`,
`CartItems`.`cart_item_status` AS `status`,
`CartItems`.`cart_item_emi` AS `emi`,
`CartItems`.`cart_item_created_at` AS `createdAt`,
`CartItems`.`cart_item_created_by`
FROM `cart_items` AS `CartItems`
WHERE ((`CartItems`.`cart_item_lead_id` = '200481'
AND `CartItems`.`cart_item_status` IN ('4')
AND `CartItems`.`cart_item_deleted_status` = '0'))
ORDER BY `CartItems`.`cart_item_created_at` DESC
LIMIT 0,
10) AS `CartItems`
LEFT OUTER JOIN `user` AS `createdBy` ON `CartItems`.`cart_item_created_by` = `createdBy`.`userID`
LEFT OUTER JOIN `cart_payment_log` AS `CartPaymentLogs` ON `CartItems`.`cart_items_id` = `CartPaymentLogs`.`cart_payment_log_cart_items_id`
ORDER BY `createdAt` DESC;
You need to sign in to view this answers