Need help getting rid of duplicated data based off a certain column
.leftJoinAndMapOne('itemHistory.customer', CustomerEntity, 'customer','itemHistory.customerId = customer.id')
.leftJoinAndMapOne('itemHistory.receipt', ReceiptEntity, 'receipt', 'itemHistory.receiptId = receipt.id')
.leftJoinAndMapOne('itemHistory.picklist', PicklistEntity, 'picklist', 'itemHistory.picklistId = picklist.id')
.leftJoinAndMapOne('itemHistory.asn', ASNEntity, 'asn', 'itemHistory.asnId = asn.id')
.select(
[
'itemHistory.publicId',
'MIN(itemHistory.currentStatus)',
'MIN(itemHistory.description)',
'MIN(itemHistory.sku)',
'MIN(itemHistory.partNumber)',
'MIN(itemHistory.packageType)',
'MIN(itemHistory.width)',
'MIN(itemHistory.height)',
'MIN(itemHistory.length)',
'MIN(itemHistory.weight)',
'MIN(itemHistory.creationDate)',
])
.addSelect('customer.name')
.addSelect('customer.id')
.addSelect('receipt.publicId')
.addSelect('receipt.id')
.addSelect('picklist.publicId')
.addSelect('picklist.id')
.addSelect('asn.publicId')
.addSelect('asn.id')
.where('itemHistory.wmsCustomerId = :wmsCustomerId', { wmsCustomerId: wmsCustomerId })
.andWhere('itemHistory.currentStatus = :currentStatus', { currentStatus: params.statusCode })
.andWhere('itemHistory.creationDate BETWEEN :from AND :to', { from: startDate, to: endDate })
.groupBy('itemHistory.publicId');
I am using typeORM query builder method, which translates to the next sql
SELECT
\
itemHistory`.`publicId` AS `itemHistory_publicId`,
`itemHistory`.`id` AS `itemHistory_id`,
`customer`.`id` AS `customer_id`,
`customer`.`name` AS `customer_name`,
`receipt`.`id` AS `receipt_id`,
`receipt`.`publicId` AS `receipt_publicId`,
`picklist`.`id` AS `picklist_id`,
`picklist`.`publicId` AS `picklist_publicId`,
`asn`.`id` AS `asn_id`,
`asn`.`publicId` AS `asn_publicId`,
MIN(`itemHistory`.`currentStatus`),
MIN(`itemHistory`.`description`),
MIN(`itemHistory`.`sku`),
MIN(`itemHistory`.`partNumber`),
MIN(`itemHistory`.`packageType`),
MIN(`itemHistory`.`width`),
MIN(`itemHistory`.`height`),
MIN(`itemHistory`.`length`),
MIN(`itemHistory`.`weight`),
MIN(`itemHistory`.`creationDate`)
FROM `item_history` `itemHistory`
LEFT JOIN
`customers` `customer` ON `itemHistory`.`customerId` = `customer`.`id`
LEFT JOIN
`receipts` `receipt` ON `itemHistory`.`receiptId` = `receipt`.`id`
LEFT JOIN
`picklists` `picklist` ON `itemHistory`.`picklistId` = `picklist`.`id`
LEFT JOIN
`asns` `asn` ON `itemHistory`.`asnId` = `asn`.`id`
WHERE
`itemHistory`.`wmsCustomerId` = ?
AND `itemHistory`.`currentStatus` = ?
AND `itemHistory`.`creationDate` BETWEEN ? AND ?
AND `itemHistory`.`warehouseId` = ? GROUP BY `itemHistory`.`publicId``
The problem that i am getting is
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wms_glb.itemHistory.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"
the only thing i am trying to achieve here and it is not working, is delete duped results based on itemHistory.publicId
i would really appreciate any help