I have three content types - Leg, Transport and Ticket. Transport has a node reference to Leg. Ticket has a node reference to Transport.
Thus, Leg and Ticket have an indirect way of relationship. But,Transport and Ticket have optional relationship.

I'm creating a view which lists transports of the given leg ( contextual filter ). In the list, ticket is needed to join to show ticket price. Here is my views settings :

enter image description here

The view result is not what I expected. It lists unnecessary and duplicate records as shown in the screen shot.
The first two records ( in the red box ) should not be listed here. They are redundant of the last two records ( in the red circle ). You can see prices are not showing for the first two.

Aggregation did not help me in this case.

enter image description here

  • My first thought would be to filter out items where there is no price. What is the relationship "Content: Transport (field_transport) - reverse" for? Is that where the duplicates are being pulled from?
    – Citricguy
    Feb 7, 2012 at 5:57
  • The field_transport relationship results the duplicates and also gives me the result prices. If I remove it, it lost the prices.
    – Sithu
    Feb 7, 2012 at 8:15

2 Answers 2


I have found the most possible answer. It may need to update the query from hook_views_query_alter(). The generated query from views is here :

SELECT node.nid AS nid, 
    field_transport_node.nid AS field_transport_node_nid, 
    field_transport_node__field_data_field_price.field_price_value AS field_transport_node__field_data_field_price_field_price_val, 
    node.created AS node_created, 
    'node' AS field_data_field_dept_time_node_entity_type, 
    'node' AS field_data_field_travel_time_node_entity_type, 
    'node' AS field_data_field_transport_type_node_entity_type, 
    'node' AS field_data_field_price_node_entity_type, 
    'node' AS field_data_field_travel_optr_node_entity_type, 
    'node' AS field_data_field_ticket_office_node_entity_type
{node} node
LEFT JOIN {field_data_field_transport} field_data_field_transport ON node.nid = field_data_field_transport.field_transport_nid
LEFT JOIN {node} field_transport_node ON field_data_field_transport.entity_id = field_transport_node.nid
LEFT JOIN {field_data_field_leg} field_data_field_leg ON node.nid = field_data_field_leg.entity_id AND (field_data_field_leg.entity_type = 'node' AND field_data_field_leg.deleted = '0')
LEFT JOIN {field_data_field_price} field_transport_node__field_data_field_price ON field_transport_node.nid = field_transport_node__field_data_field_price.entity_id AND (field_transport_node__field_data_field_price.entity_type = 'node' AND field_transport_node__field_data_field_price.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('transport')) AND (field_data_field_leg.field_leg_nid = '2' ) ))
ORDER BY field_transport_node__field_data_field_price_field_price_val ASC, node_created DESC

I just need to add a condition with "OR". It gives me the correct result.

AND ( ( field_transport_node.nid IS NOT NULL AND field_price_value IS NOT NULL ) OR ( field_transport_node.nid IS NULL AND field_price_value IS  NULL ) )

How can I add this kind of OR condition from views UI ?
How can I do it with query object of the views ?

function mymodule_views_query_alter(&$view, &$query){
        case 'my_view_name' :   
            $query->where[0]['conditions'][] = array(
                'field' => '',
                'value' => '',
                'operator' => ''

My best guess here is that the relationship being optional is resulting in the use of a LEFT OUTER JOIN query that is joining with both some NULL row as well as the actual relationship row.

Why is the relationship between Ticket and Transport optional? If all Transports have ticket prices, it seems the easiest solution would be to mark the relationship as required in the view, so you are only pulling distinct transport/ticket combinations, and no transports w/o tickets.

I think that approach is also preferable to filtering out rows with no price, since it gets to the issue at the JOIN level.

  • Ticket and Transport relationship is actually optional. A ticket can be created referring to a transport. The list should show transports with tickets and w/o tickets. Thus, I cannot mark it as required.
    – Sithu
    Feb 7, 2012 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.