Announcement

Collapse
No announcement yet.

Sorting and Advanced Search on CONCAT field

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sorting and Advanced Search on CONCAT field

    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
Working...
X