用于ExtJS网格的MySQL UNION ALL - 需要唯一的id列

Question

I am using an ExtJS 3.0 grid and generate data from PHP using MySQL. I have setup my idProperty of the grid on the id column. The problem is that I have 2 tables for incoming invoices and one for outgoing invoices with same columns. I want to make a UNION ALL on these 2 tables and show the result into the ExtJS grid. The problem is that there are some duplicate id's coming out from these tables that causes the ExtJS grid to skip them rows. I know I can do simple UNION to skip duplicates but I need every row in the grid, even if they have the same id. There are actually no distinct data that I can relate to.

Any ideas ? Thanks.

Answer 1

Why not add some prefix to the id-column when SELECTing the data:

(SELECT CONCAT('in-', id) AS id, ... FROM in_table)
UNION ALL
(SELECT CONCAT('out-', id) AS id, ... FROM out_table)

You'll have to make sure that the field-definition for the id-field in your reader configuration has the correct type (auto or string in this case).

If you need the real id afterwards, change the query to select the real id as well:

(SELECT CONCAT('in-', id) AS gid, id, 'in' AS type ... FROM in_table)
UNION ALL
(SELECT CONCAT('out-', id) AS gid, id, 'out' AS type, ... FROM out_table)

Your idProperty will be gid now.