MySQL Using Field Definitions to Define Columns in View

Question

I have two tables- one called "orders" that has 3 columns: "id", "name", and "created_by", and the second is called "fields". The fields table holds custom fields applicable to only some kinds of orders. It has 4 columns: "id", "name", "value", and "order_id". The fields table also contains a foreign key relationship on "orders_id" to the id column in orders.

To illustrate the reason for this architecture, in one scenario an order might be for a physical product and so it needs fields for "price", "cost", and "profit". In a second scenario, the order may be for a service that carries an hourly rate, so it needs fields for "price", "hourly_rate", "profit", and "estimated hours".

Next, I created a view to join the two tables like so:

            CREATE 
                ALGORITHM = MERGE 
                DEFINER = `root`@`localhost` 
                SQL SECURITY DEFINER
            VIEW `test_view` AS
                SELECT 
                    `orders`.`id` AS `id`,
                    `orders`.`name` AS `name`,
                    `orders`.`created_by` AS `created_by`,
                    `fields`.`name` AS `field_name`,
                    `fields`.`value` AS `field_value`,
                    `fields`.`order_id` AS `order_id`
                FROM
                    (`orders`
                    LEFT JOIN `fields` ON ((`fields`.`order_id` = `orders`.`id`)))

Results:

Results

However, this is not the ideal format. I would prefer to use the field names as columns inside the view, but I'm not quite sure how to do that other than to use external code to manually combine the tables in the manner below.

This is what I would like to see instead:

Ideal Output

Is there any way to accomplish this?

Thanks in advance :)

You are looking for a pivot type solution, see this: http://stackoverflow.com/questions/7674786/mysql-pivot-table — cerd, Feb 19 '15 at 03:42

WorkSmarter · Accepted Answer · 2015-02-19T03:56:04.727

1

It is possible to merge subqueries within your select statement as so you would have something like this. You will probably take a performance hit for the multiple trips to the database.

select
    id,
    name,
    created_by,
    (select field_value from test_view where id = id and field_name = "price") as price,
    (select field_value from test_view where id = id and field_name = "cost") as cost,
    (select field_value from test_view where id = id and field_name = "profit") as profit,
    (select field_value from test_view where id = id and field_name = "hourly rate") as "hourly rate",
    (select field_value from test_view where id = id and field_name = "estimated_hours") as estimated_hours
from test_view

edited Feb 19 '15 at 03:56

answered Feb 19 '15 at 03:46

WorkSmarter

3,738
3
29
34

Huh, now why didn't I think of that? I can query the field names first, and then loop through the results to define the view in a manner similar to above. Thanks! – Joshua Dannemann Feb 19 '15 at 03:53
BTW, the performance hit would be negligible given that the app is for a small audience. – Joshua Dannemann Feb 19 '15 at 03:55

score 1 · Answer 2 · edited Feb 19 '15 at 06:17

1

Here is the simple solution....without using subquery and making it complex...

select id, name , created_by , 
   GROUP_CONCAT(if(field_name = 'price',field_value, NULL)) AS price,
   GROUP_CONCAT(if(field_name = 'cost',field_value, NULL)) AS cost,
   GROUP_CONCAT(if(field_name = 'profit',field_value, NULL)) AS profit, 
   GROUP_CONCAT(if(field_name = 'hourlyrate',field_value, NULL)) AS hourlyrate, 
   GROUP_CONCAT(if(field_name = 'estimated_hours', field_value, NULL)) AS estimated_hours
   FROM test_order GROUP BY id;

edited Feb 19 '15 at 06:17

Anurag Sharma

4,839
13
59
101

answered Feb 19 '15 at 05:31

Vaibhav Kumar

518
3
12

Please Refer this link too http://www.artfulsoftware.com/infotree/qrytip.php?id=78 – Vaibhav Kumar Feb 19 '15 at 05:47

MySQL Using Field Definitions to Define Columns in View

2 Answers2