How to use joins to get this data

Kevin Wasie

I have three tables:

property
• id
• address


properties_to_users
• id
• user_id
• property_id
• role_name


user
• id
• name


How do I get the result of a query that shows the properties associated with a user with the users roles. I only want to show the property once. 

This is for an authenticated user, so the input is the user_id.

It should look like this:
user_properties = [
{
id: 001
address: "1234 main st"
user_roles: [ "editor","admin","common" ]
},
{
id: 004
address: "59 Halifax Rd"
user_roles: [ "editor","common" ]
},
{
id: 008
address: "50 MLK Jr. Blvd"
user_roles: [ "common","admin" ]
}
]
I can figure out how to join and show the property multiple times, with the associated role. But, I cannot figure out how to only show the property once, with the associated roles.

Sean Montgomery

This would be a query all records on properties.

Then you would add a join to properties_to_users and link property.id to property_to_users.property_id within the join block.

Last in the main block you can add property_to_users.user_id = auth id

Kevin Wasie

  how do you get the array of roles inside the property?

The user can have several roles associated with the property.

I've tried addon's and evals but cannot get it.

Ray Deck

I would loop over the data in the function stack to create a new, summarized list that you can then pass back through your output. You already have the data you need coming out of the query. Looping over and reassembling into the more concise list is a bit of work but straightforward. I've helped other users do this this kind of reshaping in my sessions. I would estimate maybe 30-45 minutes to get that done and have you understanding the process so you can apply it in the future. (Which you will - transforming data is super-useful!)  

Kevin Wasie

 Awesome! Thank you. I'll give that a shot.