How can I add the non-standard node fields? Part2
lelizondob - March 3, 2008 - 12:07
Hi everyone, I've been trying to do the exact same thing that this patch does, http://drupal.org/node/70380, but with a custom date field named "field_datereference" but I just don't know how. This patch gets the standard fields from a node reference, not only the node title...
Here's the patch code:
/**
* Implementation of hook_views_tables().
*
* Join in another node table and all the standard node fields for each nodereference field.
*/
function nodereference_views_tables() {
$field_types = _content_field_types();
$tables = array();
foreach (content_fields() as $field) {
$db_info = content_database_info($field);
$module = $field_types[$field['type']]['module'];
if ($module == 'nodereference') {
$name = $field_types[$field['type']]['label'] .': '. $field['widget']['label'];
$tables['node_data_'. $field['field_name'] .'_node'] = array(
'name' => 'node',
'provider' => 'internal',
'join' => array(
'left' => array(
'table' => 'node_data_'. $field['field_name'],
'field' => $field['field_name'] .'_nid',
),
'right' => array(
'field' =>'nid',
),
),
'fields' => array(
'title' => array(
'name' => t('%name Referenced Node: Title', array('%name' => $name)),
'handler' => 'views_handler_nodereference_field_nodelink',
'option' => array(
'#type' => 'select',
'#options' => array(
'link' => 'As link',
'nolink' => 'Without link'
),
),
'sortable' => true,
'addlfields' => array('changed', 'nid'),
'help' => t('Display the title of the node.'),
),
'nid' => array(
'name' => t('%name Referenced Node: ID', array('%name' => $name)),
'sortable' => true,
'help' => t('Display the NID of a node.'),
),
'created' => array(
'name' => t('%name Referenced Node: Created Time', array('%name' => $name)),
'sortable' => true,
'handler' => views_handler_field_dates(),
'option' => 'string',
'help' => t('Display the post time of the node.'),
),
'changed' => array(
'name' => t('%name Referenced Node: Updated Time', array('%name' => $name)),
'sortable' => true,
'handler' => views_handler_field_dates(),
'option' => 'string',
'help' => t('Display the last time the node was updated.'),
),
'type' => array(
'name' => t('%name Referenced Node: Type', array('%name' => $name)),
'handler' => 'views_handler_nodetype',
'sortable' => true,
'help' => t("The Node Type field will display the type of a node (for example, 'blog entry', 'forum post', 'story', etc)"),
),
'link' => array(
'name' => t('%name Referenced Node: Link to node', array('%name' => $name)),
'handler' => 'views_handler_field_node_link',
'sortable' => false,
'option' => 'string',
'notafield' => 'true',
'help' => t("This will create a link to the node; fill the option field with the text for the link. If you want titles that link to the node, use Node: Title instead."),
),
'body' => array(
'name' => t('%name Referenced Node: Body', array('%name' => $name)),
'handler' => array(
'views_handler_field_body' => t('Full Text'),
'views_handler_field_teaser' => t('Teaser')
),
'addlfields' => array('nid'),
'notafield' => TRUE,
'help' => t('Display the Main Content.'),
),
'view' => array(
'name' => t('%name Referenced Node: View link', array('%name' => $name)),
'handler' => 'views_handler_node_view',
'notafield' => TRUE,
'option' => 'string',
'help' => t('Display a link to view the node. Enter the text of this link into the option field; if blank the default "view" will be used.'),
),
'edit' => array(
'name' => t('%name Referenced Node: Edit link', array('%name' => $name)),
'handler' => 'views_handler_node_edit',
'notafield' => TRUE,
'addlfields' => array('type', 'uid'),
'option' => 'string',
'help' => t('Display a link to edit the node. Enter the text of this link into the option field; if blank the default "edit" will be used.'),
),
'delete' => array(
'name' => t('%name Referenced Node: Delete link', array('%name' => $name)),
'handler' => 'views_handler_node_delete',
'notafield' => TRUE,
'addlfields' => array('type', 'uid'),
'option' => 'string',
'help' => t('Display a link to delete the node. Enter the text of this link into the option field; if blank the default "delete" will be used.'),
),
),
'sorts' => array(
'nid' => array(
'name' => t('%name Referenced Node: ID', array('%name' => $name)),
'help' => t('Sort by the database ID of the node.'),
),
'created' => array(
'name' => t('%name Referenced Node: Created Time', array('%name' => $name)),
'handler' => 'views_handler_sort_date',
'option' => views_handler_sort_date_options(),
'help' => t('Sort by the submission date of the node.'),
),
'changed' => array(
'name' => t('%name Referenced Node: Updated Time', array('%name' => $name)),
'handler' => 'views_handler_sort_date',
'option' => views_handler_sort_date_options(),
'help' => t('Sort by the last update date of the node.'),
),
'sticky' => array(
'name' => t('%name Referenced Node: Sticky', array('%name' => $name)),
'help' => t('Sort by whether or not the node is sticky. Choose descending to put sticky nodes at the top.'),
),
'title' => array(
'name' => t('%name Referenced Node: Title', array('%name' => $name)),
'help' => t('Sort by the node title, alphabetically'),
),
'type' => array(
'name' => t('%name Referenced Node: Type', array('%name' => $name)),
'help' => t('Sort by the node type, alphabetically'),
),
'random' => array(
'name' => t('Referenced Random', array('%name' => $name)),
'handler' => 'views_handler_sort_random',
'help' => t('By choosing random, nodes will be ordered completely randomly. This is a good way to choose X random nodes from a group of nodes.'),
),
),
'filters' => array(
'status' => array(
'name' => t('%name Referenced Node: Published', array('%name' => $name)),
'operator' => array('=' => t('Equals')),
'list' => 'views_handler_operator_yesno',
'list-type' => 'select',
'help' => t('Filter by whether or not the node is published. This is recommended for most Views!'),
),
'promote' => array(
'name' => t('%name Referenced Node: Front Page', array('%name' => $name)),
'operator' => array('=' => t('Equals')),
'list' => 'views_handler_operator_yesno',
'list-type' => 'select',
'help' => t('Filter by whether or not the node has been promoted to Front Page.'),
),
'sticky' => array(
'name' => t('%name Referenced Node: Sticky', array('%name' => $name)),
'operator' => array('=' => t('Equals')),
'list' => 'views_handler_operator_yesno',
'list-type' => 'select',
'help' => t('Filter by whether or not the node is set sticky.'),
),
'moderate' => array(
'name' => t('%name Referenced Node: Moderated', array('%name' => $name)),
'operator' => array('=' => t('Equals')),
'list' => 'views_handler_operator_yesno',
'list-type' => 'select',
'help' => t('Filter by whether or not the node is moderated.'),
),
'type' => array(
'name' => t('%name Referenced Node: Type', array('%name' => $name)),
'list' => 'views_handler_filter_nodetype',
'list-type' => 'list',
'operator' => 'views_handler_operator_or',
'value-type' => 'array',
'help' => t('Include or exclude nodes of the selected types.'),
),
'anon' => array(
'field' => 'uid',
'name' => t('%name Referenced Node: Author is Anonymous', array('%name' => $name)),
'operator' => 'views_handler_operator_eqneq',
'list' => 'views_handler_filter_useranon',
'value-type' => 'array',
'help' => t('This allows you to filter by whether or not the node author is anonymous.'),
),
'currentuid' => array(
'field' => 'uid',
'name' => t('%name Referenced Node: Author is Current User', array('%name' => $name)),
'operator' => 'views_handler_operator_eqneq',
'list' => 'views_handler_filter_usercurrent',
'list-type' => 'select',
'help' => t('This allows you to filter by whether or not the node was authored by the logged in user of the view.'),
),
'currentuidtouched' => array(
'field' => 'uid',
'name' => t('%name Referenced Node: Current User Authored or Commented', array('%name' => $name)),
'operator' => array('=' => 'touched by'),
'list' => 'views_handler_filter_usercurrent',
'list-type' => 'select',
'handler' => 'views_handler_filter_uid_touched',
'help' => t('This allows you to filter by whether or not the logged in user authored or commented on the node.'),
),
'distinct' => array(
'name' => t('%name Referenced Node: Distinct', array('%name' => $name)),
'operator' => array('=' => 'is'),
'list' => array('distinct' => 'distinct'),
'handler' => 'views_handler_filter_distinct',
'value-type' => 'array',
'help' => t('This filter ensures that each node may only be listed once, even if it matches multiple criteria. Use this if multiple taxonomy matches return duplicated nodes.'),
),
'title' => array(
'name' => t('%name Referenced Node: Title', array('%name' => $name)),
'operator' => 'views_handler_operator_like',
'handler' => 'views_handler_filter_like',
'help' => t('This filter allows nodes to be filtered by their title.'),
),
'created' => array(
'name' => t('%name Referenced Node: Created Time', array('%name' => $name)),
'operator' => 'views_handler_operator_gtlt',
'value' => views_handler_filter_date_value_form(),
'handler' => 'views_handler_filter_timestamp',
'option' => 'string',
'help' => t('This filter allows nodes to be filtered by their creation date. Enter dates in the format: CCYY-MM-DD HH:MM:SS. Enter \'now\' to use the current time. You may enter a delta (in seconds) to the option that will be added to the time; this is most useful when combined with now. If you have the jscalendar module from jstools installed, you can use a popup date picker here.'),
),
'changed' => array(
'name' => t('%name Referenced Node: Updated Time', array('%name' => $name)),
'operator' => 'views_handler_operator_gtlt',
'value' => views_handler_filter_date_value_form(),
'handler' => 'views_handler_filter_timestamp',
'option' => 'string',
'help' => t('This filter allows nodes to be filtered by their creation date. Enter dates in the format: CCYY-MM-DD HH:MM:SS. Enter \'now\' to use the current time. You may enter a delta (in seconds) to the option that will be added to the time; this is most useful when combined with now. If you have the jscalendar module from jstools installed, you can use a popup date picker here.'),
),
'body' => array(
'name' => t('%name Referenced Node: Body', array('%name' => $name)),
'operator' => 'views_handler_operator_like',
'handler' => 'views_handler_filter_body',
'help' => t('This filter allows nodes to be filtered by their body.'),
),
),
);
}
}
return $tables;
}Any help? Thanks.
Luis
...
You want to reference all nodes that have this extact date?
The following section,
'join' => array('left' => array(
'table' => ...the noderef field table...
'field' => ...the noderef value column...,
),
'right' => array(
'field' =>'nid',
),
tells Views to connect the tables using nodereference <-> nid. If you want to connect them using youdatefield <-> node.created do:
'join' => array('left' => array(
'table' => ...the date field table...
'field' => $field['field_name'] .'_value',
),
'right' => array(
'field' => 'created',
),
But your date field must be of the 'timestamp' type, or else your SQL server won't understand how to compare the two fields. If it's of the 'ISO' type, you'd have to use some conversion SQL. See use of 'extra' field here. If you want more flexible join conditions, switch to Views2 and implement a join handler.
Thanks for the reply. You
Thanks for the reply.
I have a node type (members) with the first name, last name and some other fields, this would be like a member's database. Inside this node type I have a nodereference multiselect field (field_eventreference) which is a reference to the Event Node Type, this is just the events he/she attended.
The Event Node Type has only a title and one field: "field_datereference".
So what I wanna do is Create an Event, type the Event's Title, the field_datereference (which is a date in the future) and then I could edit the Member and Select the event he/she assisted. As you might know I can only reference the node title (in this case the Event Title), not the date of the event (field_datereference).
I'll try to explain myself with the next example:
I have an Event with the following info:
Event Title: Conference with Steve Jobs.
Event Date (field_datereference): Feb/10/2008
And I have two Members:
Member 1:
Member Title: John Smith
Member First Name: John
Member Last Name: Smith
Event (field_eventreference): Conference with Steve Jobs. (this is the reference to the event node)
Member 2:
Member Title: Martha Smith
Member First Name: Martha
Member Last Name: Smith
Event (field_eventreference): Conference with Steve Jobs. (this is the reference to the event node)
Then I want to show a table of each member's history of attended events like this:
First Name|Last Name| Event (Event Title)| Event Date (field_datereference)
John| Smith| Conference with Steve Jobs | Feb/10/2008
Martha | Smith | Conference with Steve Jobs | Feb/10/2008
Right now, I can only have this:
First Name | Last Name | Event (Event Title)
John | Smith | Conference with Steve Jobs
Martha | Smith | Conference with Steve Jobs
with the patch I can create something like this:
First Name | Last Name | Event (Event Title) | Event Created Date
John | Smith | Conference with Steve Jobs | Jan/1/2008
Martha | Smith | Conference with Steve Jobs | Jan/1/2008
But I don't want the Event Created Date, as you might know, I create the event and publish it before the Event takes place, sometimes a month before.
Thanks
Luis
...
Ah, your new question is indeed saner. Ignore my previous reply.
Use 'Views Fusion'. This module lets you combine two views. If you setup one view to show fields from the 'members' nodes (First Name, Last Name), and another view to show fields from the 'event' nodes (Node: Title, Event Date), then this module lets you fuse them together. It needs something to fuse them on, and in our case this something will be the nodereference field.
Here's how to use Views Fusion with a nodereference:
http://drupal.org/node/130944
(Ignore the second paragraph, which talks about the "nodefamily module"; it isn't relevant in your case.)
That page leads you to a patch you need to apply. (It's a pity, because there's nothing in it that prevents it form being a stand-alone module.)
I've just checked this and it works. The patch didn't apply cleanly for me, but it's simple enough to do it by hand. And note comment #43, about extending some column.
(Fortunately, we won't need these tricks in View2.)
it works
Thanks, this solution is far more simple than the one I was trying and works great.
Just one more thing, what if I want to display only one event by member. Let's say John Smith attended 3 events and Martha Smith attended 2 events. what if I want to display only the last one? The other method let me do it by just adding the select distinct filter.
I've tried using SELECT distinct in one view, and then in both views but I just get a mysql error about SELECT distinct.
Is this possible? am I asking too much? Maybe the patch didn't apply correctly?
Thanks.
Luis
...
It turns out the problem was reported already. I've just proposed a patch. If that patch works for you, please mark that issue as "reviewed and tested by community".
No, using 'distinct' won't solve your problem. You want to choose the _last_ event, not any event.
One solution is to ditch Views Fusion and instead load our secondary data --the event title and date-- in the theming layer. The drawback is that since this data now doesn't exist in the 'data layer', you won't be able to, say, sort by it.
Otherwise, if you want to keep using Views Fusion, right now I can think of a quick hack: to use sql views. You now have a DB table, probably 'content_field_eventreference', that associates all members with their events. You need to find out the SQL that associates all members with their _last_ event, then use CREATE VIEW to create a pseudo table, then implement hook_views_query_substitutions() to replace the original table with this new one. But I can't do this for you, so you're on your own here. Whatever, this lesson shows Views has its limitations.
clue
Ok, so now I have the query which shows pretty much what views fusion did, but I can group to show only one record by member. I created a table using the link you provided. The query is actually working fine. this query is working in mysql query browser.
Now, let's face reality: I have not a clue of what's next. I found this link (http://drupal.org/node/180924) but I don't know if this is the right way to do it and if I have to create a views-nameofmyview.tpl file or this goes in template.php or somewhere else. I'm also a confused about using the pseudo-table I created or the complete query.
I'm having serious problems to do this part:
One more thing maybe I forgot... will I be able to filter my table by date o do I have to do this in the query?... the filter I will apply is to show me only the members with an event attended in the last year.
So the question is what do I have to read/learn to do this?
This is the query if it helps:
SELECTterm_data.name AS 'Member Type',
node.title AS 'Name',
content_type_member.field_firstname_value AS 'First Name',
content_type_member.field_lastname_value AS 'Last Name',
content_type_event.field_event_name_value AS 'Event Title',
content_type_event.field_event_date_value AS 'Event Date'
FROM
node
INNER JOIN content_type_member ON (node.vid = content_type_member.vid)
INNER JOIN content_field_event ON (node.nid = content_field_event.nid)
INNER JOIN content_type_event ON (content_field_event.field_event_nid = content_type_event.nid)
INNER JOIN term_node ON (node.nid = term_node.nid)
INNER JOIN term_data ON (term_node.tid = term_data.tid)
INNER JOIN vocabulary_node_types ON (term_data.vid = vocabulary_node_types.vid)
INNER JOIN vocabulary ON (vocabulary_node_types.vid = vocabulary.vid)
WHERE (node.`type` = 'member')
GROUP BY
title
Thanks..
Luis
...
Were you trying to implement the 'sql views' idea?
Then you're in a wrong direction. Here's a more detailed explanation (scrap everything you've done). But note that sql views are supported only in MySQL5 and above, or Postgres (I expected you to read this fact).
You have a 'field_eventreference' field. It's in the 'content_field_eventreference' table:
+-----+-------+-----+----------------------+| vid | delta | nid | field_eventreference |
+-----+-------+-----+----------------------+
| 29 | 1 | 29 | 27 |
| 29 | 2 | 29 | 45 |
| 29 | 0 | 29 | 23 |
| 32 | 0 | 32 | 31 |
| 32 | 1 | 32 | 14 |
| 41 | 0 | 41 | 40 |
+-----+-------+-----+----------------------+
The "problem" in this table is that it connects every member (the nid column) with _all_ his events (the field_eventreference column). We don't like this, because that's the reason our view displays all events. So first you have to find out the SQL that turns this table into, e.g.:
+-----+-------+-----+----------------------+| vid | delta | nid | field_eventreference |
+-----+-------+-----+----------------------+
| 29 | 1 | 29 | 27 |
| 32 | 1 | 32 | 14 |
| 41 | 0 | 41 | 40 |
+-----+-------+-----+----------------------+
In other words, turn it into a table that picks only the latest event for each member.
Then turn this SQL statement into a (pseudo) table using CREATE VIEW. Say you named this table 'content_field_eventreference_latest'.
Finally, you need to make Views use this new table instead of the original. In a module 'mymodule' do:
function mymodule_views_query_substitutions($view) {if ($view->name == 'MY_VIEW') {
return array('{content_field_eventreference}' => 'content_field_eventreference_latest');
}
}
That's all.
Thanks. It Works.
So this is working, I'll try to explain myself what I did, maybe it’ll help others.
I created a new module and it worked, but I still had one problem left. I could not filter by date since I only wanted the members who had an attended event in the last year.
I had to create a new content type, I called mine tempevent. This content type has two fields: 'futuredate' and 'pastdate'.
This way I could create a filter outside the view. Let me explain:
I create an event with two dates, this dates will only work as a filter in a mysql query. So I have this Tempevent #1 with:
1. Futuredate: November/12/2008
2. PastDate: November/12/2007.
This way I have a filter of one year.
Next, I created the next pseudotables, each one works as a variable inside another query. Let’s see the querys:
CREATE VIEW futuredateview AS SELECT field_futuredate_value FROM content_type_tempevent ORDER BY nid DESC LIMIT 1;CREATE VIEW pastdateview AS SELECT field_pastdate_value FROM content_type_tempevent ORDER BY nid DESC LIMIT 1;This way I only show the last record in the table and I have two new pseudotables, I will use them in another query.
So, next, I created a query that shows The Table the way I want it. This query does not include the date filter.
SELECTnode.title AS `Full Name`,
content_type_member.field_firstname_value AS First Name,
content_type_member.field_lastname_value AS Last Name,
content_type_event.field_event_name_value AS `Event Name`,
content_type_event.field_date_event_value AS `Event Date`
FROM
node
INNER JOIN content_type_member ON (node.vid = content_type_member.vid)
INNER JOIN content_field_event ON (node.nid = content_field_event.nid)
INNER JOIN content_type_event ON (content_field_event.field_event_nid = content_type_event.nid)
INNER JOIN term_node ON (node.nid = term_node.nid)
INNER JOIN term_data ON (term_node.tid = term_data.tid)
INNER JOIN vocabulary_node_types ON (term_data.vid = vocabulary_node_types.vid)
INNER JOIN vocabulary ON (vocabulary_node_types.vid = vocabulary.vid),
futuredate,
pastdate
GROUP BY
title
In this case, title is the member’s node title (using the auto title node this is just First Name + Last Name). "GROUP BY title" is just to show only one record for every member.
Next, I added the date filter using the two variables I created with pseudotables.
WHERE(content_type_event.field_date_event_value BETWEEN pastdateview.field_pastdate_value AND futuredateview.field_futuredate_value)
GROUP BY nid;
The complete query is this one:
CREATE VIEW finalquery AS
SELECT
content_field_event.field_event_nid,
content_field_event.nid,
content_field_event.delta,
content_field_event.vid
FROM
node
INNER JOIN content_type_member ON (node.vid = content_type_member.vid)
INNER JOIN content_field_event ON (node.nid = content_field_event.nid)
INNER JOIN content_type_event ON (content_field_event.field_event_nid = content_type_event.nid)
INNER JOIN term_node ON (node.nid = term_node.nid)
INNER JOIN term_data ON (term_node.tid = term_data.tid)
INNER JOIN vocabulary_node_types ON (term_data.vid = vocabulary_node_types.vid)
INNER JOIN vocabulary ON (vocabulary_node_types.vid = vocabulary.vid),
futuredate,
pastdate
// IMPORTANT //
WHERE
(content_type_event.field_date_event_value BETWEEN pastdateview.field_pastdate_value AND futuredateview.field_futuredate_value)
GROUP BY nid;
As you might see now, I’m using the field_date_event_value and I tell the query to do a filter between the two dates of the last record in my tempevent table. This way, I just take the field_pastdate_value from the pseudotable pastdateview and the field_futuredate_value from the futuredateview and those are the limits of my date.
Next, I just use the finalquery (pseudotable) and and create mymodule:
<?phpfunction mymodule_views_query_substitutions($view) {
if ($view->name == 'myviewname') {
return array('{content_field_event}' => 'finalquery');
}
}
Maybe this was not the best way to do what I wanted but I had to do this because Views could not load the nodereference fields (It shows only the nodetitle) and with the patch it shows all the standards fields of a node, like created date and updated date, but If I create a new content field, this will not be loaded. Views fusion does this but with views fusion I could not filter by a date range.
Thanks again mooffie, I hope this could help someone else. And maybe in a future version of cck and nodereference, views will be able to load every created field.
Luis