Dealing with nested associations in a query

Phoenix Forum Questions / Help
1

Hello There :slight_smile:

Let’s start with what data I have:

schema: tours, App.Events.Tour

 id |         title         |     tag_id 
----+-----------------------+---------------------
  2 | Tour 2                | 1    
  3 | Tour 3                | 2            
  4 | Tour 4                | 3

schema: player_tour;  Many_to_many relationship between players and tours

 id  | player_id | tour_id |
-----+-----------+--------
  4 |         1 |       2 |   
  4 |         1 |       3 |     
  5 |         2 |       2 |       
 6  |         3 |       3 |         

schema: player_tag, Many_to_many relationship between players and tags

 id | player_id | tag_id | points |     
----+-----------+--------+-------
  1 |         1 |      1 |     15 | 
  2 |         2 |      1 |     50 | 
  3 |         3 |      2 |     15 |

Function I can do: I can list all players in a given tag:

<%= for playertag <- @playertags do %>
  <%= playertag.player.name %>
  <%= playertag.points %>
<% end %>

What I want is to display in how many tours a player in a given tag is loaded.
Example: In tag_id 1 there are player_id 1, 2, 3. We can see that player_id 1 has played in 2 tours - tour_id 2 and 3 (player_tour). Furthermore, tour_id 2 has tag_id 1, but tour_id 3 has tag_id 2. If we have a given tag_id 1 the output would be 1. If tag_id is 3 - 0.

<%= Events.get_tours_by_player_and_tag(playertag.player_id, @tag.id) %>

# Events

def get_tours_by_player_and_tag(player_id, tag.id) do
  query = ???
end

Here I don’t really know how to continue with that kind of nested association, If I were to check side by side:

# all tours by player
query1 = from(pt in PlayerTour, where: pt.player_id == ^player_id)
tours_by_player = Repo.all(query1)
# all tours by tag
query2 = from(t in Tour, where: t.tag_id == ^tag_id)
tours_by_tag = Repo.all(query2)

But I can’t figure it now what I have to do…
Note that I am a rookie and I may not have explained everything good. Here I don’t what all the code ready, but some help what should I do.

Best Regards

2

If I got this right, both tours and players have tags.

  • Are those the same tags?
  • Do you want to filter tours by tag?
  • Do you want to filter tours by specific player?
  • Do you want to filter tours by player’s tag?
  • Do you want to have sum of all those conditions or an intersection?
3

hi,
Yes, they are the same tags. I want to filter tours by specific tag and specific player. If we have player with id of 1 and tag with id of 1 I want to filter all tours having those numbers. Tours have their tags in Tour schema (tag_id), however relationship between tours and players is in another schema - player_tour.

Considering this question, player_tag actually is not needed, will delete it `(if I can)

4

So here’s a query that should do the job (not tested):

SELECT
  DISTINCT ON (tours.id) *
FROM
  tours
  INNER JOIN player_tour ON player_tour.tour_id = tours.id
WHERE
  tours.tag_id = $tag_id
  AND player_tour.player_id = $player_id;

If you have associations (has_many, etc.) set up in your Ecto schemas, this could translate to:

Repo.all(
  from tour in Tour,
  distinct: tour.id,
  inner_join: tour_player in assoc(tour, :tour_players),
  where: tour.tag_id == ^tag_id,
  where: tour_player.player_id == ^player_id
)
1 Like