SQLSTATE[42S22]: Column not found: 1054 Unknown column 'og_membership.field_og_ref_target_id' in 'on clause'

I'll need a db dump in order to be able to reproduce on my local. Can you provide one, with everything not related to OG and the bug disabled?

I'm getting the same error.

I have a content type Organizations, and another called Project. Organization is a group. There's an og_group_ref field on Project, and data in it.

When creating a view of content type Group to show its projects, I get the error adamtong reported, and the query shown is:

SELECT node_og_membership.title AS node_og_membership_title, node_og_membership.nid AS node_og_membership_nid, node.created AS node_created
FROM 
{node} node
LEFT JOIN {og_membership} og_membership ON node.nid = og_membership.etid AND og_membership.entity_type = 'node'
INNER JOIN {node} node_og_membership ON og_membership.og_group_ref_target_id = node_og_membership.nid
WHERE (( (og_membership.gid = '7920' ) ))
ORDER BY node_created DESC

Copying the query out to a query editor, I get the same error. When I change og_membership.og_group_ref_target_id to og_membership.gid, the query works fine, and gives me the results I want.

I'm running 7.x-2.0-rc3.

Had the same issue.
Try making a relationship with ReferencING entity instead of ReferencED entity in Views Relationships.

Same bug when building a view of content that has a relationship to a group on the referenced item field.

See #1. If I can't reproduce I can't fix...

Same problem here...

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'og_membership.og_group_ref_target_id' in 'on clause'

See #6...

I can't provide any additional circumstances, but I'm getting the same issue.

Another work around is to find your way to the referenced entity via the existing group membership.:

In your view, add a relationship to the 'Group Membership' entity of the node.

- OG membership: OG membership from Node

Then add a relationship to that relationship using:

- (OG membership from node) OG membership: Group Node from OG membership

I can confirm that @dan_lennox's workaround works for me.

Weirdest thing ever, but #11 worked for me as well... I was doing this for filtering by group audience. Good to know!

DS

Ran into the same problem (I don't know if it's correct to call it as a "bug" or an "issue", though), and solved it by upgrading to og-7.x-2.1 (using entityreference-7.x-1.0 and views-7.x-3.6).

In this og-7.x-2.1 the Views integration seems to use some better wording, which is more persuasive to use the og_membership relation first, and try using the group relation only afterwards. In plain old English it means that one cannot add a direct relationship from a group content to the group itself, since there's no such relationship: first add the relation from group content to OG membership, then you will be able to add the relation from the OG membership to the group itself. (I, for one, don't yet really get the reasons behind this architecture, but haven't been thinking too much about it, since this kind of chained relationship does solve the problem for me.)

i needed to apply #11 too.

here are views exports and queries to reproduce.
creating a relation "A bridge to the Content entity that is referenced via og_group_ref" seems to create sql that is similar to the workaround but broken.

# renamed aliases to focus on difference
$ diff broken-query.txt workaround-query.txt -u
 SELECT node.title AS node_title, node.nid AS nid
 FROM 
 {node} node
 LEFT JOIN {og_membership} og_membership ON node.nid = og_membership.etid AND og_membership.entity_type = 'node'
-LEFT JOIN {node} node_og_membership ON og_membership.og_group_ref_target_id = node_og_membership.nid
+LEFT JOIN {node} node_og_membership ON og_membership.gid = node_og_membership.nid AND og_membership.group_type = 'node'

Hi,

I have the same problem. I'm getting the following error message when I try to use relationship on my view.

SQLSTATE[42S22]: Column not found: 1054 Nieznana kolumna 'og_membership.og_group_ref_target_id' w on clause

As the #11 doesn't work for me I'll be appreciated if to find any solution.

Amitaibu, what do you need exactly to be able to have a look at this issue? I may give you an access to my testing site where this error occurs.

Thanks in advance !

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'og_membership.og_group_school_ref_target_id' in 'on clause'

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created, 'class:page' AS view_name
FROM 
{node} node
LEFT JOIN {og_membership} og_membership ON node.nid = og_membership.etid AND (og_membership.entity_type = 'node' AND og_membership.entity_type = 'node')
LEFT JOIN {node} node_og_membership ON og_membership.og_group_school_ref_target_id = node_og_membership.nid
WHERE (( (node.status = '1') AND (node.type IN  ('class')) ))
ORDER BY node_created DESC

------------------------------------------------------------------------------------

school: group
class : group , group content of school(og_group_school_ref)
student: group content of class(og_group_class_ref)

Nothing to add but I am getting the same error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'og_membership.field_course_reference_target_id' in 'on clause'

For me, the bug was caused by different field name in the $header and the $rows.
You can see that one was named 'award_name' but the other was 'award_item'.
This bug is fixed when I changed the 'award_name' inside the $header in to 'award_item' (which is the origin name in the sql database).

         $header = array(
		array('data'=> t('Recipent'), 'field'=>'recipient'),
		array('data'=> t('Award Title'), 'field'=>'award_name'),
		array('data'=> t('Issue Date'), 'field'=>'issue_date', 'sort' => 'asc'), 
		array('data'=> t('Details'), 'field'=>'details'),
		t('Options')
	);
        $rows = array(
			$data->recipient,
			$data->award_item,
			lions_base_format_date_from_database($data->issue_date),
			$data->details,
			$options
	);

        $output .= theme('table', array('header' => $header, 'rows' => $rows,));

	return $output;

Hello,

i got the following error when trying to do a relationsship via og_group_ref or in my case og_group_fs_ref.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'og_membership.og_group_fs_ref_target_id' in 'on clause'

Is this the same error as mentioned in the earlier posts? I tried the solution from #11 but no success for me.

How can I help to solve this error?

I'm seeing a similar error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'og_membership.og_group_ref_target_id' in 'on clause'

#11 worked for me too.

@Amitaibu - If you want access to my dev site, I can set that up.

I'm seeing this too, latest dev.

It seems to work fine with OG-ified fields, just not the default og_group_ref.

ETA: Oh. No, it doesn't.

#11 doesn't work as a workaround if you just want to get the group related to your node, regardless of membership. Yes, #11 does work. Long day. Looking through the code, it seems that the og_handler_relationship stuff doesn't get used by the Entity Reference-type view relationships, these come straight from the ER module and are having an issue with OG behaviour.

Another confirmation of #11 here.

Same issue #11 "covered" up the issue, seems there are gremlins in the views data for og_group_ref.

I saw this error during one of my migrations. It took a few hours to sort it out. In my case, I have my MigrateSQLMap set up incorrectly.
Its 'alias' value must match the 'alias' value used in the query for the primary table in the source

of if the source query contains:

db_select('tblcustomeractivity', 'cact')

Then your MigrateSQLMap should contain:

array(
                'someField' => array(
                'type' => 'int',
                'not null' => TRUE,
                'description' => 'Original ID in old database sys',
                'alias' => 'cact',
            )

Note the use of 'cact'

To finish of the view I was doing, I was going to add a contextual filter that was accepting the group nid (which bridges to the group content that is used in the heartbeat table) using the relationship from #4

So adding Referenced is the wrong way. Actually getting an SQL error both ways :/

To paraphrase users are bridging to the group from their base view, rather than bridging from the group to some relationship

Update
I think that the weighting of the relationships were at fault. Ensuring that these were first in the relationships section appears to have resolved the query errors. Discovered after other relationships in unrelated views were causing similar strange results depending on their ordering. (Fairly complex site with node access, i18n, og, ....)

Screenshot coming of the settings that worked. These were the same as the settings that triggered the error; difference is just having the organic group relationship first rather than last. View is of type Heartbeat activity (an entity type)

The settings:

I think the issue here is that the join is an entity reference join which would typically go:

SOURCE_BASE_TABLE.ID_KEY => field_data_FIELD_NAME.entity_id
field_data_FIELD_NAME.FIELD_NAME_target_id => DESTINATION_BASE_TABLE.ID_KEY

Because the OG behavior moves the storage out of field_data_FIELD_NAME into og_membership, the
relationship needs to be updated. It appears the first half is happening, but the second isn't. It should go:

SOURCE_BASE_TABLE.ID_KEY => og_membership.etid
og_membership.gid => DESTINATION_BASE_TABLE.ID_KEY

But it is actually doing:

SOURCE_BASE_TABLE.ID_KEY => og_membership.etid
og_membership.FIELD_NAME_target_id=> DESTINATION_BASE_TABLE.ID_KEY

I suspect the issue is an incomplete/incorrect OgBehaviorHandler::views_data_alter() that only updates half or the join. Possibly the following bit of code:

        $data['og_membership'] = array(
          'table' => array(
            'join' => array(
              $entity_info['base table'] => array(
                // Join entity base table on its id field with left_field.
                'left_field' => $entity_info['entity keys']['id'],
                'field' => 'etid',
                'extra' => array(
                  0 => array(
                    'field' => 'entity_type',
                    'value' => $entity_type,
                  ),
                ),
              ),
            ),
          ),
          // Copy the original config from the table definition.
          $field['field_name'] => $data['field_data_' . $field['field_name']][$field['field_name']],
          $field['field_name'] . '_target_id' => $data['field_data_' . $field['field_name']][$field['field_name'] . '_target_id'],
        );

Thanks @andrewbelcher for narrowing this down. I have added a patch to fix the issue. At line 226 the gid field is getting added to filter, sort and arguments but gets left out of the relationship. The patch adds the field to the relationship outside the $op loop as the relationship field does not need the table value, so it looks cleaner.

I need to make mention that you will need to flush your views cache in the advanced views settings as we are modifying vews_data.

Dobe, I applied and tested your patch, and it looks like this has fixed the issue for the use cases covered in this thread. I am marking this as reviewed.

Wait, nope, it seems like fixing this error created another error. Since I'm working on my Win7 machine i don't have ready access to cli, so here's what needs to be done in a patch:

In og/plugins/entityreference/selection/OgSelectionHandler.class.php

	// Don't try to validate the IDs.
	if (!$ids = entityreference_prepopulate_get_values($this->field, $this->instance, FALSE)) {
		return array();
	}

Needs to become

    // Don't try to validate the IDs.
    if (function_exists('entityreference_prepopulate_get_values')) {
		if (!$ids = entityreference_prepopulate_get_values($this->field, $this->instance, FALSE)) {
			return array();
		}
	}

@laboratory.mike I would say that second issue is a whole different issue. In addition, checking to see if the function exists right after the check to see if the entityreference_prepopulate module exists (that function is in the .module file) seems to be redundant. I am curious how your achieving the second error though.

@dobe: Thinking about it, since I'm testing on a complete site, the second issue may be caused by something else. A little overzealous after I cleared caches and got an error, I guess. I will make a clean site on my main box and test in a day or so, then mark as approved when I test again.

Has anyone else had a chance to test the patch in #34? This issue has been around for 2+ years it would be good one to clean up.

I ran into this exact problem when trying to use a relationship on a referenced group (Entity Reference: Group membership). I'm using the latest og with the og_subgroup module.

Patch #34 fixed the problem for me.

Patch in #34 fixed the problem for me too. I couldn't patch the module itself since I am currently using a distribution with OG included but it was easy to fix using hook_views_data_alter() in a custom module. So +1 for #34 too!

Have had 2 community members confirm it fixes issue.

I'm not sure if this needs still work, our environment suddenly lost relationships and we had to re-create some views with an additional "OG membership from node" relationship to make them work again. I can't backtrace what has happened, maybe some others have experienced the same?

@mErilainen Yeah that is one of the things the patch resolved for me.

Me also. I think @amitaibu tends to hang around github rather than the issues queues, so, doing a summoning :D

Merged, thanks!

I just pulled the DEV version and I am still seeing this problem.

I'm just using the normal OG reference, not my own entity reference.

I have just recreated this in a pretty minimal site. I've attached a dump of the site.

Go to /ogtest and then click the group column to sort - everything disappears.

Go to the view and click that column in the preview and you will get the error message.

Modules:
admin_menu
block
color
contextual
ctools
dblog
devel
devel_generate
entity
entityreference
features
field
field_sql_storage
field_ui
file
filter
help
image
list
menu
node
number
og
og_ui
options
path
standard
submitted_by (not really needed)
system
taxonomy
text
token
user
views
views_bulk_operations
views_ui

@NancyDru after upgrading to the latest dev did you flush your "views" cache in advanced views settings?

No, I did not specifically do that. I did run update.php and clear the caches several times.

I just cleared the Views cache twice and all the other caches as well. Same result.

Anyone else have ideas?

Same problem, I can confirm the patch in #34 solves the problem for me also.

deleted

Am I the only one that it did not help?

Is it because I am trying to sort on the gid?

Not only did I clear Views and other caches, I disabled Views caching altogether.

As for #34, dobe says, "At line 226 the gid field is getting added to filter, sort and arguments but gets left out of the relationship." It looks to me like relationships get it too.

foreach (array('filter', 'argument', 'sort', 'relationship') as $op) {

My data dump shows the field set, even without the code in #34.

Screen shot attached.

I am not sure what your doing. You will need to upload a export of your view.

$view = new view();
$view->name = 'ogtest';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'OG Test';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'OG Test';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '200';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['pager']['options']['id'] = '0';
$handler->display->display_options['pager']['options']['quantity'] = '9';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'title' => 'title',
  'og_group_ref' => 'og_group_ref',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'title' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'og_group_ref' => array(
    'sortable' => 1,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
);
/* Field: Content: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = FALSE;
/* Field: Content: Group */
$handler->display->display_options['fields']['og_group_ref']['id'] = 'og_group_ref';
$handler->display->display_options['fields']['og_group_ref']['table'] = 'og_membership';
$handler->display->display_options['fields']['og_group_ref']['field'] = 'og_group_ref';
$handler->display->display_options['fields']['og_group_ref']['label'] = 'Groups audience';
$handler->display->display_options['fields']['og_group_ref']['settings'] = array(
  'link' => 0,
);
$handler->display->display_options['fields']['og_group_ref']['delta_offset'] = '0';
/* Sort criterion: Content: Post date */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'node';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'ogcontent' => 'ogcontent',
);

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'ogtest';

Click on the "Groups audience" column title, and it will go bonkers. Edit the view and click that in the preview and you will see the error.

I don't believe this is the same issue. This issue is in regards to using a og_group_ref as a "relationship" to gather information from the group a node is referencing.

I would move your issue to a new issue queue. As I didn't run into any errors with your view. So I can neither confirm or deny the error your receiving is the same.

If your wanting to sort by a gid or a group title. Add og_group_ref as a relationship (there are 2 -- one for referenced and the other for referencing).

Then use that relationship on either the nid or title fields/sorts/filter.

#2538430: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node_og_membership__og_membership.og_group_ref_target_id' in 'order clause'

Was fixed until I misunderstood.

#34 fixed the error but I didn't get what I expected..

I thought "referenced" would bring the parents (groups audience) of the current group into the view. Is that not the case?

I tried the #11 solution, and it works. But i'm trying to do the same from Services Views, and it works in the admin part, but breaks in the request https://www.dropbox.com/s/1h2zrzb97tzmso4/%D0%A1%D0%BA%D1%80%D0%B8%D0%BD...

I've applied the #34 patch, but still no success. Does this need to be fixed elsewhere in the code?

I am getting the error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node_og_membership__og_membership.og_group_ref_target_id' in 'field list' whilst trying to create a view to show a count of nodes that reference a group using aggregation. Post #11 does not fix the problem for me.

I'm getting exactly the same error as in #68 after having tried #11
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node_og_membership__og_membership.target_id' in 'field list'

Is this still an issue with Entityreference newer than 1.2 ?