I have created my own OG reference field with Entityreference to reference to a Group.

However, when I use Views to build a relationship via the og reference field, it shows the following error (see attachment):

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

I have tried to use the core field_og_ref OG reference field to test and the error is still there.

I have tested both the lastest 7.x-2.x-dev and 7.x-2.0-rc2.

Comments

amitaibu’s picture

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?

smichel’s picture

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.

alesr’s picture

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

btopro’s picture

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

trainingcity’s picture

Getting error...

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

...when adding (author) Entity Reference: Groups audience relationship to what is supposed to be a very simple View.

Does anyone have a link to some explanation info for the 7.x-2 release? The migration from 1.5 has been a painful process. The (even more) cryptic relationships/contextual filter selections are a challenge to navigate. I wanted to try out the OG example but I am having a separate problem with the message notify module dependency...

amitaibu’s picture

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

trainingcity’s picture

Thanks Amitaibu. Problem resolved after I cleaned db as per instructions in a link from the migrate issues. Can't find link at the moment but I will look again and post.

Will be sure to provide cleaned db dump next time.

Still struggling with the views relationships, can't figure out what is meant by each og relationship from their titles. Having a hard time creating a view of member's subscribed og groups for example. In general I'm back in business after migration from x-1.5 to x-2.dev.

bdecarne’s picture

Same problem here...

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

amitaibu’s picture

See #6...

jienckebd’s picture

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

dan_lennox’s picture

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

alesr’s picture

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

puppyman’s picture

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

DS

Boobaa’s picture

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.)

axel.rutz’s picture

Version:7.x-2.0-rc2» 7.x-2.x-dev
StatusFileSize
new8.81 KB
new927 bytes
new8.47 KB
new865 bytes

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'
jarjar’s picture

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 !

shoufeng.xi’s picture

StatusFileSize
new107.34 KB

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

og  1054 column not found
------------------------------------------------------------------------------------

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

shoufeng.xi’s picture

StatusFileSize
new102.51 KB

og  1054 column not found

shoufeng.xi’s picture

StatusFileSize
new50.73 KB
new102.17 KB

og  1054 column not foundog  1054 column not found

ecoluke’s picture

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'

blacktheon’s picture

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;

fruitsalad’s picture

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?

fenns’s picture

I'm seeing a similar error:

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

bjlewis2’s picture

#11 worked for me too.

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

Renee S’s picture

I'm seeing this too, latest dev.

Renee S’s picture

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

ETA: Oh. No, it doesn't.

Renee S’s picture

#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.

mpgeek’s picture

Another confirmation of #11 here.

dobe’s picture

Issue summary:View changes

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

hoytman’s picture

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'

Alan D.’s picture

Component:og.module» Og Views

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)

Alan D.’s picture

StatusFileSize
new8 KB

The settings:

andrewbelcher’s picture

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:

<?php
        $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'],
        );
?>
dobe’s picture

StatusFileSize
new828 bytes
PASSED: [[SimpleTest]]: [MySQL] 1,223 pass(es).
[ View ]

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.

dobe’s picture

Status:Active» Needs review
dobe’s picture

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.

laboratory.mike’s picture

Status:Needs review» Reviewed & tested by the community

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.

laboratory.mike’s picture

Status:Reviewed & tested by the community» Needs review

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

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

Needs to become

<?php
   
// 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();
        }
    }
?>
dobe’s picture

@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.

laboratory.mike’s picture

@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.

dobe’s picture

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.

tcmug’s picture

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.

vensires’s picture

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!

dobe’s picture

Status:Needs review» Reviewed & tested by the community

Have had 2 community members confirm it fixes issue.

mErilainen’s picture

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?

dobe’s picture

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

Renee S’s picture

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

amitaibu’s picture

Status:Reviewed & tested by the community» Fixed

Merged, thanks!

  • amitaibu committed 4ab860d on 7.x-2.x authored by dobe
    Issue #1890370 by dobe: SQLSTATE[42S22]: Column not found: 1054 Unknown...
NancyDru’s picture

Status:Fixed» Needs work

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.

NancyDru’s picture

Priority:Normal» Major
StatusFileSize
new4.07 MB

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

dobe’s picture

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

NancyDru’s picture

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

NancyDru’s picture

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