0

I have a very simple view that lists all the nodes, which shows the following query:

SELECT node.nid AS nid, node.title AS node_title, node.uid AS node_uid, node.created AS node_created, 'node' AS field_data_field_site_node_entity_type, 'node' AS field_data_field_status_node_entity_type
FROM 
{node} node
WHERE (( (node.status = '1') AND (node.type IN  ('ticket')) ))
ORDER BY node_created DESC
LIMIT 50 OFFSET 0

I am trying to overwrite it with a custom database query to list only nodes of the user referenced in the URL and all his invitees. This is the code I am using.

function my_custom_module_views_pre_view(&$view, &$display_id, &$args) {
  if ($view->name == "support" && $display_id == "page" ) {
    // Let's start collecting uids and put current page uid as the first member of array.
    $uids[] = arg(1);

    // Now let's collect all invitees of the page uid author and push into the same array.
   $query = db_select('invite', 'i')
     ->condition('i.uid', arg(1), '=')
     ->condition('i.status', '3', '=')
     ->fields('i', array('invitee'));
   $result = $query->execute();
   foreach ($result as $record) {
     $uids[] = $record->invitee;
   }

   // Now let's pull all the nodes of the inviter (page uid) and his invitees.
  $query = db_select('node', 'n')
     ->condition('n.type', 'ticket', '=')
     ->condition('n.status', '1', '=')
     ->condition('n.uid', $uids, 'IN')
     ->fields('n', array('nid', 'title', 'created'));
   $result = $query->execute();
   foreach ($result as $record) {
     dpm($record->nid.' | '.$record->title.' | '.$record->created);
   }

  }
}

dpm() shows all the correct nodes, but reading hook_views_query_alter() I still can't figure out how to make my view use this query.

How can I achieve this?

  • I would think it's better to use contextual filter's with a set path argument (like you do in your code) and relationships to pull in the invitees of the selected user. Would that be an option, or do you really want to code this? – Stefanos Petrakis Jan 5 '17 at 6:30
  • I already have that kind of simple view, but the problem is that this view is going to be modified further and contextual filters won't play nice. – Nick Jan 5 '17 at 6:41
  • On the second thought, maybe what you'll suggest will differ from what I already have. Can you share your code with contextual filter, so I could try? – Nick Jan 5 '17 at 6:48
  • Just had another look at your code, 'invite' is a custom table (maybe a custom entity even), right? Is it available in Views UI? – Stefanos Petrakis Jan 5 '17 at 7:01
  • Its of the Invite module drupal.org/project/invite, so yes it has its table and it is available in Views. – Nick Jan 5 '17 at 7:13
0

While exploring a site-building alternative (see comments in OP), here is a direct answer to the question:

Implementing the hook_views_query_alter should do what you want:

function my_custom_module_views_query_alter (&$view, &$query) {
  $display_id = $view->current_display;
  $uid = $view->args(0); // for a view that is loaded from a URL like /myview_url/UID

  // Your code from the OP follows, modified accordingly
  // You have a query object available as an argument by reference
  // You can add joins and filters to it
  ...
}

You would need to implement the hook_views_api in addition to placing the above code snippet inside the my_custom_module.views.inc file, a minimal implementation would be:

function my_custom_module_views_api() { return array( 'api' => '3', ); }

Cheers!

P.S.: Have a look at this one, if you haven't already => http://www.kdweb.co.uk/blog/drupal-module-views-hook-sequence

  • I had already been exploring options reading drupal.stackexchange.com/questions/135302/… and just got the correct view returned when I saw your reply. Anyway, I am accepting it as it is the correct way of modifying query. – Nick Jan 5 '17 at 7:38
  • Cool, glad it worked out. I looked at the Invite module and I still would prefer a ViewsUI-based solution, but at the moment I need to wrap my head around the Invite module and that takes some time. May update this thread at some point, if I manage to do that wrapping :-) – Stefanos Petrakis Jan 5 '17 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.