Matching Many To Many relation

Question

I have 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 want to select every MediaObject which has at least one group in common with the one specific User.

Example:

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

User 1 has at least one same group as MediaObject

How can I build a where sql query for this? I use typeorm for building my queries, yet every sql query would help. Also, I want to understand how. Typeorm joins the tables like this

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

There must be a join table between `Group` and `User` and a join table between `Group` and `MediaObject`. What are the names of these tables? — Radim Bača, Dec 10 '18 at 20:30
Group: `group` User: `user` MediaObject: `media_object`. The join tables are `media_object_groups_group` & `user_groups_group` — Noim, Dec 10 '18 at 20:39

Radim Bača · Answer 1 · 2018-12-10T20:54:11.660

0

Using a simple JOIN you may retrieve MediaObject Id's that share at least one group with the user. Than use IN to retrieve the 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
)

edited Dec 10 '18 at 20:54

answered Dec 10 '18 at 20:46

Radim Bača

10,646
1
19
33

Could you please explain the query? My goal is it to select every MediaObject having at least one group which also has the user. Does this join this and how does this? – Noim Dec 10 '18 at 20:51

score 0 · Answer 2 · answered Dec 11 '18 at 01:41

If there can be multiple overlapping groups between same MediaObject and the same User, an EXISTS semi-join might be faster than using IN:

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

Else, Radim's query should serve just fine after adding some double-quotes.

This assumes that referential integrity is enforced with foreign key constraints, so it's safe to rely on user_groups_group."userId" without checking the corresponding user even exists.

It's unwise to use reserved words like "user" or "group" or CaMeL-case strings as identifiers. Either requires double-quoting. ORMs regularly serve poorly in this respect. See:

Are PostgreSQL column names case-sensitive?

Matching Many To Many relation

2 Answers2