Help with Database logical query result (task assigned to user or team)

Wappler General How To
,
#1

Hi everybody,
I need help to set a query correctly:

I have 4 tables

  • task: task_id, status, team_assigned_boolean (0/1), team_id (reference), user_id (reference), created_date,…
  • user: user_id, name, forename, email,…
  • teams: team_id, team_name,…
  • members: member_id, team_id (reference), user_id (reference),…

(no subtables)

A task can be assigned to a user, to a team or unasigned.
A user can be member of none or every teams.

How should I set my query(ies) to get a unique list (repeat) of tasks assigned to a unique user_id OR assigned to a team is member of?

Join, join set, inner, left, right… I can’t find the way to do that.
Many thanks.

#2

Perhaps a simple logic change will simplify this.
At present the issue is the need to effectively perform two searches and join the results ( a custom query with a union would perhaps be the solution) however if you were to create an extra team called, for example, “not in a team” and assign all those not in a team there then the query should resolve to a simple left joins on task-> team -> members-> users removing the need to separately search on the user_id field directly

1 Like
#3

Hi everybody,
Thanks @Hyperbytes for your proposal - from my understanding this does not answer my needs

I finally created a MR sub-table with user_id and team_id, also I added a array field in user table with all the team_id the user is member of.

But I don’t know how to get all the tasks assigned to a specific member or to a team is member of in a single query.

If someone have an idea?
Many thanks

#4

I think a better solution would have been to use another subtable rather then an array as searching is much simpler on tables

#5

I did both (a array field + sub-table) but don’t know how to deal with it.

#6

Hi all,
I used this {{query1.groupBy('id').keys()}} with a IN condition and it works great.
Thanks