[Question] How/Where to put Join Selects?

[polaroidkidd]

I'm working on a Multi-Secret-Santa side-project. I'm not sure if your familiar with the concept, but basically a group of people are secretly assigned another member of the group to give a gift at Christmas. The problem with that was that when last year multiple families got together to do their secret santa thing, they all happened to be given members from their own family. It's not a huge problem but it sort of defeats the purpose of it.

So my goal is to build a secret-santas matcher to mitigate the above problem. I've created a DB diagram with a bunch of junction tables: https://dbdiagram.io/d/616c66dc940c4c4eec9968d9

The junction tables are used to allow a user to be part of multiple groups, and a group to have multiple users.

In summery: User = User Group = Group of people who are not supposed to be each other's secret santa Party = Multiple groups.

Constraint: A member of a group cannot be the secret santa of another member of the same group.

So, on to my question:

Where (and how) would I place the join selects in your architecture? I thought of putting it in the GroupAPI, but it doesn't quite feel right to have it at that level. Should I use it in the UsersGroupDao? But how would I make use of the UserDao and GroupDao to create the joins?

Any input would be greatly appreciated! In the mean time, I'm going to watch the videos you mentioned in my other issue :)

[mathias21]

Hahahaha lol, I've also done a Secret Santa app and this was the way to develop the backend.

From what you have shared, I think you can place the joints at repository level and do it by code (not by SQL query). As you have a relational DB, you can link by Id and combine table results in your model object. Here it would be important to differentiate entity level models (those that come out from DAOs) from your repository models (those that you would use in your business logic). I'm not a big fan of big SQL queries, but if you feel like it would be better, then you can have a DAO that executes the entire query and returns the full object already merged (that means one single DAO for all the tables)

[polaroidkidd]

lol, what a coincidence!

I just realised I forgot to link to the repo. It's located here: https://github.com/santa-matcher/backend/tree/feat/follow-ktoreasy-example

I have daos for User, UserGroup, Group, GroupParty and Party.

you can link by Id and combine table results in your model object

You mean I should link the UserDao and the GroupDao to the UserGroupDao?

I've seen the discussion on linking tables in the exposed repo, am I heading in the right direction here or am I totally wrong?

[mathias21]

You mean I should link the UserDao and the GroupDao to the UserGroupDao? I've seen the discussion on linking tables in the exposed repo, am I heading in the right direction here or am I totally wrong?

What I've meant is to not use the SQL/Exposed to do the joints. For example, you can have one repository to provide users and when recovering them they can be already populated from repository level with a list of the groups it belongs to. From this repository, you inject the userDao, the userGroupDao and the GroupDao. When you query for a user by id, for example, you make use userDao to recover user info + userGroupDao that will be queried to recover all the groupIds for that user id + groupDao to recover all groups info.

So the idea is to assign Repository the responsibility of providing the user with the info already populated.

[polaroidkidd]

Hmm, I'm reading through the exposed documentation and am getting some ideas. I'm going to try some things.

On a related note, regarding the authentication, I was wondering if you'd have any ideas how to allow a secret Santa to be authenticated to view the profile of the assigned user (I wanted to make profiles private by default).

Btw, this project is really helpful in getting to grips with backend development and specifically ktor.

[mathias21]

Hmm, I'm reading through the exposed documentation and am getting some ideas. I'm going to try some things.

👍

On a related note, regarding the authentication, I was wondering if you'd have any ideas how to allow a secret Santa to be authenticated to view the profile of the assigned user (I wanted to make profiles private by default).

This seems more like a business question. I hope you understand that I will try to help you with the doubts related with this repository, but I'm not here to help you with your app. Sorry, my time is limited.

Btw, this project is really helpful in getting to grips with backend development and specifically ktor.

That is the aim of this repository and the articles I've written. I hope this helps you to grow and learn.

[polaroidkidd]

but I'm not here to help you with your app

I^m aware of that. I was hoping to get some pointers on how to expand your existing template with more a tad more functionality. Maybe I'll make a MR when I'm done with mine and we can discuss it then.

[mathias21]

👍 All MR are welcome.