I’m using hook_views_data to add a custom table and its fields to Views. The table relates to nodes, and looks like this:
+ node id | my_value | time_period + |-----------|------------|--------------| | 1 | 100 | 1 year | | 1 | 200 | 1 month | | 2 | 300 | 1 year | | 2 | 400 | 1 month | +---------------------------------------+ I have defined an implicit relationship to my custom table, so I have a view with nodes as the base table and I can access the my_value field from my custom table.
The time_period column is new, however. Now there are multiple rows per node, with different time_period values, and each of these results in a different output row in my view.
Rather than having a my_value field for each row, I would like to have my_value (1 year) and my_value (1 month) fields, so that each output row represents one node – i.e. rows for the same node are collapsed and their fields are separated.
I’ve cobbled together the SQL that achieves this, but I can’t figure out how to accomplish this with hook_views_data:
SELECT node.title AS node_title, node.nid AS nid, custom_table_1month.my_value AS my_value_1month, custom_table_1year.views AS my_value_1year FROM node LEFT JOIN custom_table custom_table_1month ON custom_table_1month.node_id = node.nid AND custom_table_1month.time_period = '1 month' LEFT JOIN custom_table custom_table_1year ON custom_table_1year.node_id = node.nid AND custom_table_1year.time_period = '1 year' I have tried to add two base tables to views, called custom_table_1month and custom_table_1year, but these value names are used verbatim as the table name whereas I really want them as aliases, so Views complains that these tables don’t exist, and I can’t seem to specify the correct table name anywhere.
I’m feeling like this has to be possible, because I’ve got the SQL for it! I just can’t for the life of me figure out how to do this with Views.
Sponsored by SupremePR
