Views filter does not work through a relationship

Subscribing

subscribing

Patch is OK, I made similar. But I couldn't belive, that 7.x port still has this bug! Hey maintainers, don't be so lazy!

lol Ricoo :) This project has a new maintainer, you may not have noticed cause it's been that way for all of 2 days. A few things to ponder:

- When changing a version on a patch, it should have been RTBC for the prior version and then the version changed and status set to patch (to be ported).
- When marking an issue RTBC it should have actually been "reviewed and tested by the community".
- When trying to encourage the maintainers to do something whether they are new / old / missing / lazy / whatever... try politeness, it'll get you farther.

All that said :) If you can apply & test this on 6.x (a view export would be helpful), I'll re-roll this for 7.x test and apply if it's good.

Cheers,
JP

Has anyone confirmed if this is still an issue in D7 and have a fix? I am currently unable to filter results by proximity if using a relationship. I get no results. It works fine when not filtering on a referenced node.

I have two content types, Venue and Event. The Event type has an entity reference field to the Venue. If I create a view of Event types and create an entity relationship to the Venue, I get no results when using the proximity filter. If I create the a view of just Venues with no relationships, the proximity filter works fine.

I thought that I would try and modify the D7 version of the openlayers_proximity_handler_filter_circle.inc file used by the patch above. It didn't fix anything and instead gave me the error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node_field_data_field_event_venue_ref__openlayers_proximity_filter_circle.distance' in 'where clause'

Anyone with help would be greatly appreciated!

@jastylr ready comment #4

@Mac_Weber, thanks for the reply. I've looked at #4 and have tested the above patch myself on a local D6 installation. The patch does indeed work and I get views results with proximity as expected now. I have attached an export of the view that I used to test it with.

I'm actually developing on D7 and I just decided to try and manually apply the changes in the above patch to my D7 version and it did not fix it the problem as it did with D6. Instead, with the change I received the error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node_field_data_field_event_venue_ref__openlayers_proximity_filter_circle.distance' in 'where clause'

Here is the actual D7 SQL output from Views:

SELECT node_field_data_field_event_venue_ref__openlayers_proximity_filter_circle.distance AS node_field_data_field_event_venue_ref__openlayers_proximity_, node.title AS node_title, node.nid AS nid, node_field_data_field_event_venue_ref.title AS node_field_data_field_event_venue_ref_title, node_field_data_field_event_venue_ref.nid AS node_field_data_field_event_venue_ref_nid, 'node' AS field_data_field_event_venue_ref_node_entity_type, 'node' AS field_data_field_venue_address_node_entity_type, 'node' AS field_data_field_venue_geofield_node_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_event_venue_ref} field_data_field_event_venue_ref ON node.nid = field_data_field_event_venue_ref.entity_id AND (field_data_field_event_venue_ref.entity_type = 'node' AND field_data_field_event_venue_ref.deleted = '0')
LEFT JOIN {node} node_field_data_field_event_venue_ref ON field_data_field_event_venue_ref.field_event_venue_ref_target_id = node_field_data_field_event_venue_ref.nid
RIGHT JOIN {openlayers_proximity} node_field_data_field_event_venue_ref__openlayers_proximity ON node_field_data_field_event_venue_ref.nid = node_field_data_field_event_venue_ref__openlayers_proximity.nid
RIGHT JOIN (SELECT op.nid AS nid, op.lat AS lat, op.lon AS lon, (6371.0 * ACOS(SIN((lat * RADIANS(1))) * SIN(('37.5331558' * RADIANS(1))) + COS((lat * RADIANS(1))) * COS(('37.5331558' * RADIANS(1))) * COS((lon * RADIANS(1)) - ('-122.2483726' * RADIANS(1))))) AS distance
FROM 
{openlayers_proximity} op) node_field_data_field_event_venue_ref__openlayers_proximity_filter_circle ON node.nid = node_field_data_field_event_venue_ref__openlayers_proximity_filter_circle.nid
WHERE (( (node.status = '1') AND (node.type IN  ('event')) AND (node_field_data_field_event_venue_ref__openlayers_proximity_filter_circle.distance < '16.09344') ))
ORDER BY node_field_data_field_event_venue_ref__openlayers_proximity_ ASC, node_title ASC
LIMIT 10 OFFSET 0

Just in looking at the above SQL I noticed in the first line, AS node_field_data_field_event_venue_ref__openlayers_proximity_, with nothing after the underscore. Not sure if this poses any kind of problem or whether it was intentional or not. It appears again in the ORDER BY clause. It may have nothing to do with anything but thought I would point it out.

I also took a look at the most recent log entries in my D7 installation and I'm getting the following 2 items:

Notice: Undefined index: in openlayers_proximity_handler_field->render() (line 67 of /Applications/MAMP/htdocs/bandwhere7/sites/all/modules/openlayers_proximity/views/openlayers_proximity_handler_field.inc).

Notice: Undefined index: unit in openlayers_proximity_handler_field->render() (line 46 of /Applications/MAMP/htdocs/bandwhere7/sites/all/modules/openlayers_proximity/views/openlayers_proximity_handler_field.inc).

Just for reference, I have also attached a copy of my D7 views export.

Sorry if this is not the information that might be required. I'm somewhat new to this and trying to understand what needs to be done.

Thanks again and I hope that any of this helps to get the D7 version fixed and running.

@jastylr this issue was originally opened for D6. It should not be changed versions, unless it is first fixed for D6. That's what I was meant when I asked you to read #4

- When changing a version on a patch, it should have been RTBC for the prior version and then the version changed and status set to patch (to be ported).

RTBC = Ready To Be Committed

Anyway, thanks for sending your Views export. I think it will help the developers.

It would be nice if this were fixed for D7

I got it to work with relationships in D7 by manually applying the changes in the patch at top of this thread but there was still a problem as I noted in #5 which I remedied by changing one additional line of code. In the same file, openlayers_proximity_handler_filter_circle.inc, I changed the following line:

$this->query->add_table($table, 'node', $join);

to:

$this->query->queue_table($table, 'node', $join);

This is the same way that it was being called in the D6 version and I believe that "add_table()" eventually calls queue_table or vice-versa but anyway, making that one additional change allowed relationships to magically work again for me in D7.

I don't at all profess that I know what I'm doing by changing the function call and what other implications it may have but it has not caused any problems for me.

Hope this helps anyone needing to get this to work and perhaps anyone who is more skilled at debugging the problem and creating a patch to fix it.

jastyler, thanks, have you actually managed to get past all the issues you encountered? If so, if you can attach this as a patch for D7?

Okay, here is my first ever attempt at a patch. Hopefully I've followed procedure close enough. I created it and applied it on the 7.x-2.x version and it worked for me. Hopefully someone can try this out and verify.

jastylr, I have successfully applied your patch and that solved my issue. Thanks!

The patch in #12 worked for me also. Thank you!

Just curious as to what needs to happen to have this patch rolled into the next release. I am willing to help out if something needs to be done.

I can also confirm that the patch in #12 worked for me. I'm using the Relation module to connect my nodes in this scenario, not that I think it matters one way or the other with regards to this patch.