[quote=“Lemon_Juice, post:20, topic:197054, full:true”]
well, I never said anything about not having to join!
[/quote]quite right, you said the join would involve more columns, which is kinda just “less noise and hassle” again
it was me who mentioned you’d have to do extra joins
you’re using an unnecessarily complex query which makes it harder to write than it should be
try this –
SELECT label.name
, GROUP_CONCAT(colour.colour) AS colours
FROM user_has_photo_has_label AS upl
INNER
JOIN label
ON label.id = upl.label_id
INNER
JOIN user_has_photo_has_label_has_colour AS uplc
ON uplc.user_id = upl.user_id
AND uplc.photo_id = upl.photo_id
AND uplc.label_id = upl.label_id
INNER
JOIN colour
ON colour.id = uplc.colour_id
WHERE upl.user_id = 1
AND upl.photo_id = 2
GROUP
BY label.name
(some unnecessarily verbose names were mercifully renamed)
you are welcome to rewrite this for your auto_increment scenario
These table names are hurting my brain. :-p Why wouldn’t you call that sucker “user_favorite_photo_label_colors”?
Overall I agree that your practical usage example looks like a situation where it’s better to have a single-column primary key in what would otherwise be a junction table. But I think you need to emphasize more clearly that the problem arises only when you need to reference the junction as a foreign key. Because otherwise, the composite key could span 10 columns and it wouldn’t be a problem. It’s only when you need to relate the junction as a foreign key that even a 2-column key would be inconvenient.
For what it’s worth, in Doctrine terminology (circling back to OP), we would probably stop thinking of these things as junction tables and start thinking of them as separaite entities, and rather than a single many-to-many relationship with an implicit junction table, instead there would be a new entity with two one-to-many relationships. In Juice’s example, for instance, there would be a UserFavoritePhoto entity with a one-to-many relationship with users and a one-to-many relationship with photos.
To my brain they are of equal complexity - you are doing one more join while I’m doing a subquery. But certainly yours is much better formatted and the aliases also make it more readable.
I will do it in my mind - I’m getting rid of the two conditions in the second join
This was a very quick act of designing a database and I just let Mysql Workbench leave the default table names
I wouldn’t say it is very inconvenient - it can be a slight inconvenience having to use a composite key but as r937 showed getting rid of the composite FK can have its drawbacks, too.
Correct - but I think a good ORM would be able to make out all the relations based on the composite keys without having to replace them with AI primary keys - in other words, this would be transparent to the user (developer).
Yes, but I fear that the deeper we go down the chain of relationships the farther the translated objects would be in appearance and behaviour from the database and we would end up with two very distinct worlds. That is of course the goal of ORM but I’m not sure if this would make things easier for the developer. I’ve never really queried such complex data structures with ORM but I suppose the task of getting all user’s labels with colours would be quite an undertaking without resorting to SQL.