Combine "mine" and "friend" into one river tab

I'm trying to figure out how to combine "mine" and "friends" into one river tab. This is what I got so far but it isn't working. Any suggestions?

pages/river.php

switch ($page_type) {
    case 'mine':
        $title = elgg_echo('river:mine');
        $page_filter = 'mine';
        $options['subject_guid'] = elgg_get_logged_in_user_guid();
        break;
    case 'friends':
        $title = elgg_echo('river:friends');
        $page_filter = 'friends';
        $options['relationship_guid'] = elgg_get_logged_in_user_guid();
        $options['relationship'] = 'friend';
        break;
    case 'combined':
        $title = elgg_echo('river:mine');
        $page_filter = 'combined';
        $options['relationship_guid' . 'subject_guid'] = elgg_get_logged_in_user_guid();
        $options['relationship'] = 'friend';
        break;
    default:
        $title = elgg_echo('river:all');
        $page_filter = 'all';
        break;
}
  • @stuartsloan, I am not on Upwork. They lost my profile, and I never re-registered due to their shady practices.

    Instead of echoing the results just add the subquery as $options['wheres'][] = '<subquery>';

    @rohit, there are two options, either use target guid of the river entry, or do a join with a subquery. But I don't know if these queries are efficient at all, perhaps another join for both subject and target would be better, do some testing.

    echo elgg_list_river([
       'wheres' => [
          "
                    rv.subject_guid = $user->guid 
                    
                    OR 
                    
                    rv.subject_guid IN (
                   SELECT guid_two FROM {$dbprefix}entity_relationships
                   WHERE relationship = 'friend'
                   AND guid_one = $user->guid
                    )
                    
                    OR 
                    
                    rv.target_guid IN (
                       SELECT guid_two FROM {$dbprefix}entity_relationships
                       WHERE relationship = 'member'
                       AND guid_one = $user->guid
                    )
            "
       ],
    ]);
    
    
    echo elgg_list_river([
       'joins' => [
          "JOIN {$dbprefix}entities oe ON oe.guid = rv.object_guid",
       ],
       'wheres' => [
          "
                    rv.subject_guid = $user->guid 
                    
                    OR 
                    
                    rv.subject_guid IN (
                       SELECT guid_two FROM {$dbprefix}entity_relationships
                       WHERE relationship = 'friend'
                       AND guid_one = $user->guid
                    )
                    
                    OR 
                    
                    oe.container_guid IN (
                       SELECT guid_two FROM {$dbprefix}entity_relationships
                       WHERE relationship = 'member'
                       AND guid_one = $user->guid
                    )
            "
       ],
    ]);
  • @Ismayil Khayredinov I tried and failed tragically. 

     

    	case 'combined':
    		$title = elgg_echo('river:mine');
    		$page_filter = 'combined';
            $user = elgg_get_logged_in_user_entity();
            $dbprefix = elgg_get_config('dbprefix');
            $options['wheres'][] = 'rv.subject_guid = $user->guid OR rv.subject_guid IN (SELECT guid_two FROM {$dbprefix}entity_relationships WHERE relationship = 'friend' AND guid_one = $user->guid)"';
    break;
  • You can't enclose single quotes within single quotes without escaping them. Also you have double quote followed by single quote in the end.

    $options['wheres'][] = "rv.subject ... ";

    or 

    $options['wheres'][] = 'rv.subject ... \'friend\' ...';

  • @Ismayil Khayredinov omg you are absolutely right! I made the change and everything works great! I've been scratching my head on this problem for months! Thank you! How can I pay you to show you my gratitude?

  • @Ismayil Khayredinov I absolutely will. Thanks again!

  • @ismayil

    I have a new problem and it's making my brain hurt trying to solve. So I want to build a filter where only non-friends are shown.

    $options['wheres'][] = 'rv.subject_guid = $user->guid OR rv.subject_guid IN (SELECT guid_two FROM {$dbprefix}entity_relationships WHERE relationship = 'friend' AND guid_one = $user->guid)"';
    break;

    Will return all guid_two who I'm friends with. I would like to exclude these guid's and return everything else. I cannot use the WHERE NOT option because if I'm friends with someone they will still show in the results if they are friends with someone else.

    guid_two | relationship | guid_one

    2, friend, 1

    2, friend, 3

    guid 2 will return because the second row is NOT friends with me but in the first row, 2 is friends with me. I don't want to see guid's who are friends with me!

    This is a little frustrating. Any help would be greatly appreciated.

  • Ok, can you help me understand how that will work in this table?

    $options['wheres'][] = 'rv.subject_guid = $user->guid OR rv.subject_guid IN (SELECT guid_two FROM {$dbprefix}entity_relationships WHERE relationship NOT IN ('friend' AND guid_one = $user->guid))"';
    break;