Sorting and Advanced Search on CONCAT field

Scriptcase 8 Discussion
, ,
#1

I have grid application with sql like below

SELECT
DATE(t.stamp) AS transactionDate,
CONCAT_WS(’ ‘,s.username,"[",s.timeStart,"-",s.timeFinish,"]",s.memo) AS sessionLabel,
t.transactionNo,
TIME(t.stamp) as transactionTime,
t.name,
t.address,
t.memo,
t.printMemo,
t.status,
CONCAT(p.barcode,’ - ',p.name) AS productName,
d.quantity,
d.unitPrice,
(d.quantity * d.unitPrice) AS lineTotal
FROM
transaction_session s LEFT OUTER JOIN transaction t ON s.id = t.sessionFk
LEFT OUTER JOIN transaction_detail d ON t.id = d.transactionFk
INNER JOIN product p ON d.productFk = p.id
ORDER BY
DATE(t.stamp) DESC, TIME(t.stamp) ASC

My problem is

  1. ORDER BY clause cannot use alias field name (transactionDate DESC, transactionTime ASC => result will not be ordered by DATE(t.stamp) DESC, instead I need to use DATE() and TIME() function.

  2. As per 1, I cannot setup to sort by transactionDate column.

  3. Advanced Search also gives me error. For example for productName which is concatenation for barcode and product name. When user enters in productName field while searching, I would like to search it in product and barcode field in table product. How to achieve this?

Can anyone help me on these?

TIA,

Daniel