What is the functioning of “->” operator in MYSQL joins?

I’m using Sequelize, and trying to learn from the queries it constructs. I have some complicated one-to-many relationship between few models, so i come across this query:

SELECT `ScrumBoard`.`id`, `ScrumBoard`.`title`, `ScrumBoard`.`createdAt`, `ScrumBoard`.`updatedAt`, `ScrumLists`.`id` AS `ScrumLists.id`, `ScrumLists`.`title` AS `ScrumLists.title`, `ScrumLists`.`createdAt` AS `ScrumLists.createdAt`, `ScrumLists`.`updatedAt` AS `ScrumLists.updatedAt`, `ScrumLists`.`ScrumBoardId` AS `ScrumLists.ScrumBoardId`, `ScrumLists->ScrumCards`.`id` AS `ScrumLists.ScrumCards.id`, `ScrumLists->ScrumCards`.`title` AS
`ScrumLists.ScrumCards.title`, `ScrumLists->ScrumCards`.`content` AS `ScrumLists.ScrumCards.content`, `ScrumLists->ScrumCards`.`createdAt` AS `ScrumLists.ScrumCards.createdAt`, `ScrumLists->ScrumCards`.`updatedAt` AS `ScrumLists.ScrumCards.updatedAt`, `ScrumLists->ScrumCards`.`ScrumListId` AS `ScrumLists.ScrumCards.ScrumListId`, `ScrumLists->ScrumCards->ScrumComments`.`id` AS `ScrumLists.ScrumCards.ScrumComments.id`, `ScrumLists->ScrumCards->ScrumComments`.`title` AS `ScrumLists.ScrumCards.ScrumComments.title`, `ScrumLists->ScrumCards->ScrumComments`.`content` AS `ScrumLists.ScrumCards.ScrumComments.content`, `ScrumLists->ScrumCards->ScrumComments`.`createdAt` AS `ScrumLists.ScrumCards.ScrumComments.createdAt`, `ScrumLists->ScrumCards->ScrumComments`.`updatedAt` AS `ScrumLists.ScrumCards.ScrumComments.updatedAt`, `ScrumLists->ScrumCards->ScrumComments`.`ScrumCardId` AS `ScrumLists.ScrumCards.ScrumComments.ScrumCardId`, `ScrumLists->ScrumCards->Labels`.`id` AS `ScrumLists.ScrumCards.Labels.id`, `ScrumLists->ScrumCards->Labels`.`title` AS `ScrumLists.ScrumCards.Labels.title`, `ScrumLists->ScrumCards->Labels`.`color` AS `ScrumLists.ScrumCards.Labels.color`, `ScrumLists->ScrumCards->Labels`.`createdAt` AS `ScrumLists.ScrumCards.Labels.createdAt`, `ScrumLists->ScrumCards->Labels`.`updatedAt` AS `ScrumLists.ScrumCards.Labels.updatedAt`, `ScrumLists->ScrumCards->Labels->scrumcard_labels`.`createdAt` AS `ScrumLists.ScrumCards.Labels.scrumcard_labels.createdAt`, `ScrumLists->ScrumCards->Labels->scrumcard_labels`.`updatedAt` AS `ScrumLists.ScrumCards.Labels.scrumcard_labels.updatedAt`, `ScrumLists->ScrumCards->Labels->scrumcard_labels`.`LabelId` AS `ScrumLists.ScrumCards.Labels.scrumcard_labels.LabelId`, `ScrumLists->ScrumCards->Labels->scrumcard_labels`.`ScrumCardId` AS `ScrumLists.ScrumCards.Labels.scrumcard_labels.ScrumCardId` FROM `ScrumBoards` AS `ScrumBoard` LEFT OUTER JOIN `ScrumLists` AS `ScrumLists` ON `ScrumBoard`.`id` = `ScrumLists`.`ScrumBoardId` LEFT OUTER JOIN `ScrumCards` AS `ScrumLists->ScrumCards` ON `ScrumLists`.`id` = `ScrumLists->ScrumCards`.`ScrumListId` LEFT OUTER JOIN `ScrumComments` AS `ScrumLists->ScrumCards->ScrumComments` ON `ScrumLists->ScrumCards`.`id` = `ScrumLists->ScrumCards->ScrumComments`.`ScrumCardId` LEFT OUTER JOIN ( `scrumcard_labels` AS `ScrumLists->ScrumCards->Labels->scrumcard_labels` INNER JOIN `Labels` AS `ScrumLists->ScrumCards->Labels` ON `ScrumLists->ScrumCards->Labels`.`id` = `ScrumLists->ScrumCards->Labels->scrumcard_labels`.`LabelId`) ON `ScrumLists->ScrumCards`.`id` = `ScrumLists->ScrumCards->Labels->scrumcard_labels`.`ScrumCardId`
WHERE `ScrumBoard`.`id` = '1';

The query itself is not important, but i would like to understand the meaning of the “->” operator, like in:

ScrumLists->ScrumCards.content AS ScrumLists.ScrumCards.content

Answer

In this context, the -> has no special meaning. It just belongs to a table alias that is defined in the query, here:

LEFT OUTER JOIN `ScrumCards` AS `ScrumLists->ScrumCards`

Once the alias is defined, it is used to refer to the corresponding table in the query, like:

`ScrumLists->ScrumCards`.`id`

Note that this is really a bad choice to use such table alias. -> is meaningful in MySQL (it’s a JSON operator that is a synonym for JSON_EXTRACT()). So using it in a table alias requires quoting the identifier everytime you use it. I would strongly suggest changing the table alias to something that is less tricky, and does not require quoting (ScrumLists_ScrumCards would be good enough).

Advertisement

Answer