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.
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
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_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.