36

I'm trying to get a union of a user's nodes and comments sorted by "post date". This post links to sandbox projects for D6 but there is nothing for 7.

This post has an example of using hook_views_pre_execute() and a SQL UNION in D6. This doesn't work for D7 with views 3.

I came across merlinofchaos' comment

Because we're now using Drupal's new query generator, the query is a SelectQuery object which you will have to modify or replace. Look up Drupal 7's new database layer for more information.

Does anyone have an example of how to do this or any other solution to combine two views?

  • this is more of an idea than a real answer : you should be able to create a view based on users, that would join both the nodes and comments (arent they both just entities after all :). On my first attempt i failed to do this because views dont offer the relation from user to coment. But that should be easy to alter. (or i just forgot something). – mojzis May 21 '12 at 22:48
  • I think it's the same answer as below: kt would require two contextual filters (content.author=logged in user OR comment.author = logged in user). – uwe May 23 '12 at 15:45
  • i dont think so :) i mean a view based on users, joining the nodes and comments. but i have a feeling that there is some problem with the relation between user and comment - i didnt manage to show the comments. – mojzis May 23 '12 at 17:31
  • i'm just guessing but can't you use searchapi to index multiple entity types at the same time? Once you have that and you have a field which is used by both parts you could use that to buily a view like that. – Daniel Wehner May 29 '12 at 16:25
  • 1
    There is a sandbox project 'Views Unionize' for drupal 7, please check drupal.org/sandbox/jalama/1785294 , – Anoop Joseph Oct 26 '12 at 6:20
15
+50

Here's a working and tested example:

/**
 * Implements hook_views_pre_execute().
 */
function mymodule_views_pre_execute(view &$view) {
  if ($view->name == 'my_view') {
    $query1 = &$view->build_info['query'];

    // Basic setup of the second query.
    $query2 = db_select('another_table', 'at')
      ->condition('some_field', 0, '>')
      ->condition('some_other_field', 12345);

    // The number of fields (and their aliases) must match query1.
    // Get the details with:
    // dpm($query1->getFields());
    $query2->addField('at', 'some_field', 'alias1');
    $query2->addField('at', 'some_other_field', 'alias2');
    $query2->addField('at', 'some_other_field2', 'alias3');
    $query2->addField('at', 'some_other_field3', 'alias4');

    // Verify that queries are very similar.
    // dpq($query1);
    // dpq($query2);

    // Matrimony.
    $query1 = $query2->union($query1, 'UNION ALL');

    // Manual test.
    // dpm($query1->execute()->fetchAll());

  }
}

This works for most views. However some style plugins may do fancy stuff that won't work with this technique (Calendar module I'm looking at you).

3

You can use the Views extra handlers module to generate SQL UNION queries from two different views/displays.

A detailed tutorial on drupal.org here

https://www.drupal.org/project/views_extra_handlers screenshot of views alter settings

2

I ended up using db_query() to create the SQL UNIONs and then rendering it into a table layout including pagers using the theme() function.

To the user it looks like default views. The other benefit was that I could optimize the query a lot. I'm showing "my friend's activities" and if you would use views for that it would create a list of your friends and use it in a SQL "IN" clause which is very slow if you have more than 50 or a 100 records.

I could narrow that list of friends to only the ones who have been logged into the site in the last x days.

This is a code sample:

  // Two queries are required (friendships can be represented in 2 ways in the
  // same table). No point making two db calls though so a UNION it is.

  // Build up the first query.
  $query = db_select('flag_friend', 'f')
    ->condition('f.uid', $account->uid)
    ->condition('u.login', $timestamp, '>');
  $query->addExpression('f.friend_uid', 'uid');
  $query->innerJoin('users', 'u', 'u.uid = f.friend_uid');

  // Build up the second query.
  $query2 = db_select('flag_friend', 'f')
    ->condition('f.friend_uid', $account->uid)
    ->condition('u.login', $timestamp, '>');
  $query2->addExpression('f.uid', 'uid');
  $query2->innerJoin('users', 'u', 'u.uid = f.uid');

  // Return the results of the UNIONed queries.
  return $query->union($query2)->execute()->fetchCol();
1

For future reference, this is how I combined two views basing on same table together. Same principles should also apply on views basing on different tables with same amount of fields.

In below case only id is selected since the format is set to rendered entity. But if you are going with fields you can always add additional dummy fields to the query that has less fields as I added timestamp below.

/**
 * Implements hook_views_pre_execute().
 */
function MY_MODULE_views_pre_execute(&$view) {
  if ($view->name == 'VIEW_1' && $view->current_display == 'DISPLAY_OF_VIEW_1') {

    $view2 = views_get_view('VIEW_2');
    $view2->build('DISPLAY_OF_VIEW_2');

    $view->build_info['query']
    ->fields('table_alias', array('timestamp'))
    ->union(
        $view2->build_info['query']
        ->range()
        ->fields('table_alias', array('timestamp'))
        ->orderBy('timestamp', 'DESC')
    );

    $view->build_info['count_query']
    ->union(
        $view2->build_info['count_query']
        ->range()
    );
  };
}
0

I imagine its something along these lines:

/** 
* Implements hook_views_pre_execute().
*/     
function mymodule_views_pre_execute(&$view) {
  if ($view->name == 'myview') {
    $query = $view->query;
    $other_view = views_get_view('otherview');
    $other_query = $other_view->query;
    $query = $query->union($other_query);
    $view->query = $query;
  }
}

Although I haven't tested it.

Some links that may help:

http://api.drupal.org/api/drupal/includes!database!select.inc/function/SelectQueryInterface%3A%3Aunion/7

http://drupal.org/node/557318#comment-1991910

  • 1
    This does not appear to fully work. $view->query is the intermediate object that Views uses to build the query. The SelectQuery is $view->build_info['query']. When you edit accordingly, I cannot get beyond a "Fatal error: Call to undefined method SelectQuery::render_pager()" error. – mpdonadio Jun 13 '12 at 13:04
  • 1
    The database test code has examples of unions api.drupal.org/api/drupal/… and api.drupal.org/api/drupal/… – mikeytown2 Nov 6 '12 at 0:32
  • another example drupal.org/node/748844#comment-7070234 – uwe Feb 17 '13 at 0:29
  • The only way this might possibly work is if both views are almost exactly the same. – Dalin Oct 30 '13 at 15:29
0

I came across a module called Views Field View, which allows you to embed a view as a field in another view. I haven't tried this out yet myself yet, but it might be helpful to you.

  • 2
    While Views Field View can indeed get both comments and nodes, I don't believe there is a way to sort across the fields, only within them. – Letharion Jun 4 '12 at 18:26
0

The EntityFieldQuery Views Backend support querying for multiple entity types at the same time. So it should be usable to query for both nodes and comments. Both entity types uses a uid property to link to their author so at the API level EntityFieldQuery::propertyCondition() should be usable to select the nodes and comments from a single user. I guess the views backend provides the same feature.

0

A different approach could be to create feeds of nodes and comments (with a contextual filter of the user's identifier in the URL) and then combine the two feeds into a new feed, and display this by post date.

-2

Used Global: PHP fields? You can use them to duct tape together a View that combines the two.

Create a View of content with Content: Title and Content: Comments fields (Excluded from display).

Add a PHP field that calculates which is more recent, the date last updated, or the date of the last comment by the user, and set the field's value to be that date. Add that field as a sort condition.

Add a similar field that outputs either a link to the comment or to the node.

It sounds good to me!

  • interesting idea. It would require two contextual filters (content.author=logged in user OR comment.author = logged in user). – uwe May 23 '12 at 15:44
  • Hoping to get the epic recovery on this one... ;) – Johnathan Elmore Jun 4 '12 at 20:32
  • 2
    The performance hit from this approach would be scary. The potential number of database queries performed could boarder on astronomical. – Rider_X Sep 2 '12 at 18:57

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.