3

I have three node types that I have created relations between using the Relation module. Seaon --> Player --> Week

The player node has a numeric field called cost. The relationship between the player and the week has a numeric field called score.

The following is a views query (with some alias changes to make it a tad more intelligible) that I use to summarise data per season. The view works fine but I need to add a calculated field that takes the sum of the weekly scores and divides it by the player cost.

SELECT  PLAYER.title AS PLAYER_title
        ,PLAYER.nid AS PLAYER_nid
        ,PLAYER_COST.field_cost_value AS PLAYER_COST_field_cost_value
        ,MIN(PLAYER.nid) AS PLAYER_nid_1
        ,'node' AS field_data_field_cost_node_entity_type
        ,MIN(PLAYER2WEEK.rid) AS PLAYER2WEEK_rid
        ,'relation' AS field_data_field_score_relation_entity_type
        ,SUM(PLAYER2WEEK__field_data_field_score.field_score_value) AS PLAYER2WEEK__field_data_field_score_field_score_value
FROM    {node} SEASON
INNER JOIN 
        {field_data_endpoints} FDE 
ON      node.nid = FDE.endpoints_entity_id 
AND     (FDE.bundle = 'player_in_season' 
AND     FDE.endpoints_entity_type = 'node' 
AND     FDE.endpoints_r_index = '1')
INNER JOIN 
        {field_data_endpoints} FDE2 
ON      FDE.entity_id = FDE2.entity_id 
AND     (FDE2.endpoints_entity_type = 'node' 
AND     FDE2.endpoints_r_index !=  FDE.endpoints_r_index 
AND     1 = '1')
INNER JOIN 
        {node} PLAYER 
ON      FDE2.endpoints_entity_id = PLAYER.nid 
AND     FDE2.endpoints_entity_type = 'node'
LEFT JOIN 
        {field_data_endpoints} PLAYER_FDE 
ON      PLAYER.nid = PLAYER_FDE.endpoints_entity_id 
AND     (PLAYER_FDE.bundle = 'has_weekly_score' 
AND     PLAYER_FDE.endpoints_entity_type = 'node' 
AND     PLAYER_FDE.endpoints_r_index = '0')
LEFT JOIN 
        {relation} PLAYER2WEEK 
ON      PLAYER_FDE.entity_id = PLAYER2WEEK.rid
LEFT JOIN 
        {field_data_field_cost} PLAYER_COST 
ON      PLAYER.nid = PLAYER_COST.entity_id 
AND     (PLAYER_COST.entity_type = 'node' 
AND     PLAYER_COST.deleted = '0')
LEFT JOIN 
        {field_data_field_score} PLAYER2WEEK__field_data_field_score 
ON      PLAYER2WEEK.rid = PLAYER2WEEK__field_data_field_score.entity_id 
AND     (PLAYER2WEEK__field_data_field_score.entity_type = 'relation' 
AND     PLAYER2WEEK__field_data_field_score.deleted = '0')
WHERE   (( (node.nid = '3' ) )
AND     (( (node.type IN  ('SEASON')) AND (node.status = '1') )))

From all the reasearch I've done, it looks like the correct option is to use hook_views_query_alter() to add the new field. This is where I lose it, I have no idea how to write the code to build the new field. Essentialy, based on the query above the field needs to be

SUM(PLAYER2WEEK__field_data_field_score.field_score_value) / PLAYER_COST.field_cost_value AS new_field

I just need some help in getting strated on the code in my custom module

This answer seems to meet my requirements conceptually, I am just lost on how to construct the function.

0

Turns out that I didn't need the hook at all. I was able to achieve the result by using a Math Expression within the views module

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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