Connecting Multiple Relationships with Many-To-Many Matches

Question

Connecting Multiple Relationships with Many-To-Many Matches

My database consists of the following entities:

@Entity
class User {
    @ManyToMany(type => Group)
    @JoinTable()
    groups: Group[];
}

@Entity
class MediaObject {
    @ManyToMany(type => Group)
    @JoinTable()
    groups: Group[];
}

@Entity
class Group {
    // [...]
}

Now, I am looking to retrieve all MediaObjects that share at least one group with a specific User.

For instance, consider the following scenario:

User 1          MediaObject 1
-----------------------------
Group 1    |--- Group 2 
Group 2 ---|    Group 3

User 1 has at least one common group with MediaObject

How can I construct a SQL query to achieve this? I am utilizing Typeorm for my queries, so any SQL query or insight into how Typeorm performs table joins would be greatly appreciated.

LEFT JOIN "group" "groups" ON "groups"."id" = "media_groups"."groupId"

sql postgresql typescript typeorm

Answer 1

Answer №1

To fetch the IDs of MediaObjects that have at least one group in common with the user, a simple JOIN can be used. After that, utilize IN to retrieve those MediaObject's.

select *
from MediaObject mo
where mo.id in
(
    select moJoin.mediaObjectId
    from media_object_groups_group moJoin
    join user_groups_group uJoin on moJoin.groupId = uJoin.groupId
    where uJoin.userId = 1
)

Answer 2

To fetch the IDs of MediaObjects that have at least one group in common with the user, a simple JOIN can be used. After that, utilize IN to retrieve those MediaObject's.

select *
from MediaObject mo
where mo.id in
(
    select moJoin.mediaObjectId
    from media_object_groups_group moJoin
    join user_groups_group uJoin on moJoin.groupId = uJoin.groupId
    where uJoin.userId = 1
)

Answer 3

Answer №2

If there are multiple overlapping groups within the same MediaObject and for the same User, utilizing an EXISTS semi-join could potentially yield faster results compared to using IN:

SELECT m.*
FROM   "MediaObject" m
WHERE  EXISTS (
   SELECT -- the select list may be empty in this case
   FROM   user_groups_group         gu
   JOIN   media_object_groups_group gm USING ("groupId")
   WHERE  gu."userId" = 1
   AND    gm."mediaObjectId" = m.id
   );

Alternatively, you can also consider using Radim's query, which should work well with some added double-quotes.

This method assumes that referential integrity is maintained through foreign key constraints, making it safe to utilize user_groups_group."userId" without verifying the existence of the corresponding user.

It's advisable to avoid using reserved words like "user" or "group", as well as CaMeL-case strings as identifiers. In such cases, double-quoting is mandatory. ORMs often struggle in handling these scenarios effectively. Refer to:

Are PostgreSQL column names case-sensitive?

Answer 4

If there are multiple overlapping groups within the same MediaObject and for the same User, utilizing an EXISTS semi-join could potentially yield faster results compared to using IN:

SELECT m.*
FROM   "MediaObject" m
WHERE  EXISTS (
   SELECT -- the select list may be empty in this case
   FROM   user_groups_group         gu
   JOIN   media_object_groups_group gm USING ("groupId")
   WHERE  gu."userId" = 1
   AND    gm."mediaObjectId" = m.id
   );

Alternatively, you can also consider using Radim's query, which should work well with some added double-quotes.

This method assumes that referential integrity is maintained through foreign key constraints, making it safe to utilize user_groups_group."userId" without verifying the existence of the corresponding user.

It's advisable to avoid using reserved words like "user" or "group", as well as CaMeL-case strings as identifiers. In such cases, double-quoting is mandatory. ORMs often struggle in handling these scenarios effectively. Refer to:

Are PostgreSQL column names case-sensitive?

Connecting Multiple Relationships with Many-To-Many Matches

Answer №1

Answer №2

Similar questions

The optimal time to register for events within the Vue lifecycle

I am interested in updating the content on the page seamlessly using Angular 6 without the need to reload

Encountered an issue while attempting to integrate Nebular into my Angular application

The React namespace is missing the exported member 'InputHTMLAttributes', and the MenuItemProps interface is incorrectly extending the ListItemProps interface

PostgreSQL logic/query for tracking user preferences

Enhancing Web Service Calls with Angular 2 - The Power of Chaining

Utilize a list of Data Transfer Objects to populate a dynamic bar chart in recharts with

The Angular Ivy strictTemplates error message states that the type 'Event' cannot be assigned to the type 'InputEvent' parameter

"Utilize React and Redux to fetch data from the server after making changes with an API

combining duplicate values using group_concat and join operations

Angular does not update the progress bar

Send information as FormData object

Here is a guide on showcasing information obtained from ASP.NET API in Angular version 13

Tips for managing the error message "The key 'myOptionalKey' is optional in the 'myObject' type but necessary in the '{...}' type"

Guide to importing a function from a Javascript module without declaration

What are some effective strategies for optimizing the deletion of records from a table with millions of rows to increase efficiency?

Angular 4: Transform a string into an array containing multiple objects

How can I display an agm-polyline within a map in Angular 7?

Troubleshooting MySQL Concatenation and Trimming in a Select Query to Validate Existence in Another Table - Where is my code failing?

What is the best way to implement an IN clause in iBATIS?