squixy squixy - 4 months ago 8
SQL Question

How to SELECT an array of all associated records IDs in PotgreSQL?

I have the following relation:

Place(1) |----< (N)SavedPlace(N) >----| (1)Visitor


So:


  • One
    Place
    can be saved by many
    Visitors

  • One
    SavedPlace
    saves exactly one
    Place

  • One
    SavedPlace
    is saved exactly by one
    Visitor

  • One
    Visitor
    can save many
    Places



I want to:


  • count how many times one
    Place
    was saved in general - I imagine this as joining
    Places
    with
    SavedPlaces
    , grouping them by
    Place
    ID and counting how many
    SavedPlaces
    ID are per each group

  • see if a
    Visitor
    saved a
    Place
    - I imagine this as having a list of
    Visitors
    IDs for every place.



The first one I did with:

from place in query,
left_join: saved_places in assoc(place, :saved_places),
group_by: place.id,
select: %{place | saved_count: count(saved_places.id)}


For the second one, I have no idea.

How should SQL look like to query for both of these values?




EDIT:

I realized that I didn't describe the second use case correctly. I'd like to query for all
Places
and for each of them tell if they were saved by a particular
Visitor
(knowing his ID of course).

Answer

You look for two queries:

count how many times one Place was saved in general - I imagine this as joining Places with SavedPlaces, grouping them by Place ID and counting how many SavedPlaces ID are per each group

select     places.place_id, places.place_name, Count(*)
from       places
inner join savedplaces on savedplaces.place_id = places.place_id
group by   places.place_id, places.place_name

If it is possible for a visitor to save a place twice (does that make sense in your application?), and you want to count such duplicates only once, then replace Count(*) with Count(distinct savedplaces.visitor_id).

For the other request:

see if a Visitor saved a Place - I imagine this as having a list of Visitors IDs for every place.

select     places.place_id, places.place_name, visitors.visitor_id, visitors.visitor_name
from       places
inner join savedplaces on savedplaces.place_id = places.place_id
inner join visitors on visitors.visitor_id = savedplaces.visitor_id 

To know the visitors that saved a particular place, add a where clause:

where      places.place_id = ?

... where ? represents whatever place_id you want to get the visitors for.

Similarly, to know the places a particular visitor saved, use a where clause:

where      places.visitor_id = ?

... where ? represents whatever visitor_id you want to get the saved places for.

In case you want to list all places, and an indication of whether a particular visitor has saved it (yes/no), then you could use an outer (left) join:

select     places.place_id, places.place_name, 
           case when savedplaces.visitor_id is null then 'No' else 'Yes' end
from       places
left join  savedplaces on savedplaces.place_id = places.place_id
       on  savedplaces.visitor_id = ?

Similarly, in case you want to list all visitors, and an indication of whether they saved a particular place (yes/no), then you could use an outer (left) join as well:

select     visitors.visitor_id, visitors.visitor_name, 
           case when savedplaces.place_id is null then 'No' else 'Yes' end
from       visitors
left join  savedplaces on savedplaces.visitor_id = visitors.visitor_id
       on  savedplaces.place_id = ?
Comments