N1QL queries and indices

Description

This file documents the N1QL queries being used in the application and the indices required to make them work

Auth Token DAO

To fetch auth token data for a given user

Query

SELECT authToken.*
FROM `dashboard-data` AS authToken
WHERE authToken.type = 'AuthToken'
    AND authToken.userId = <userId>

Index

CREATE INDEX `IDX_userId` ON `dashboard-data`(`userId`)

Explanation

This index covers the userId property on all documents including those of the Auth Token type.

---

User DAO

To fetch user data matching given first name, last name and email address

Query

SELECT b.*
FROM `dashboard-data` AS b
WHERE firstName = <first name>
    AND lastName = <last name>
    AND email = <email address>

Index

CREATE INDEX `IDX_email` ON `dashboard-data`(`email`)

Explanation

The email property is sufficient to cover documents needed to be queried by this query. Also, only User documents have the email property so no need to make it a partial index to limit the documents indexed.

To fetch user data matching given email address

Query

SELECT b.* FROM `%s` AS b WHERE email = <email address>

Index

See above

Explanation

See above

---

Club DAO

To fetch club data for a given user

Query

SELECT club.id AS clubId,
       club.name,
       club.logo,
       club.createdDate
FROM `dashboard-data` AS club
WHERE club.type = 'Club'
    AND club.userId = <userId>

Index

Same as the index used when fetching auth token data. Since userId is covered for all documents, it is covered for this query as well.

To fetch player and match performance data for players associated with given club id

Query

SELECT player.metadata.name,
       player.metadata.country,
       player.id,
       player.metadata.countryLogo AS countryFlag,
       player.ability.`current` AS currentAbility,
       player.roles,
       matchPerformance.matchRating.history AS matchRatingHistory
FROM `dashboard-data` player
    JOIN `dashboard-data` matchPerformance ON player.id = matchPerformance.playerId
WHERE player.type = 'Player'
    AND matchPerformance.type = 'MatchPerformance'
    AND player.clubId = <clubId>

Index

CREATE INDEX `IDX_playerId` ON `dashboard-data`(`playerId`) WHERE (`type` = "MatchPerformance")

Explanation

This index covers the playerId property on documents of type MatchPerformance. It is mandatory since we need to cover the keys on the RHS on an ANSI join.

---

Player DAO

To fetch the userId associated with the player from its corresponding parent club document

Query

SELECT club.userId
FROM `dashboard-data` AS player
    JOIN `dashboard-data` AS club ON player.clubId = club.id
WHERE club.type = 'Club'
    AND player.type = 'Player'
    AND player.id = <playerId>

Indices

1. CREATE INDEX `IDX_playerClubId` ON `dashboard-data`(`clubId`) WHERE (`type` = "Player")
2. CREATE INDEX `IDX_clubId` ON `dashboard-data`(`id`) WHERE (`type` = "Club")

Explanation

The first index covers the clubId property in documents of type Player and the second one covers the id property in documents of type Club. The latter is mandatory since we need to cover the keys on the RHS on an ANSI join.

---

Board Objective DAO

To fetch the userId associated with the board objective from its corresponding parent club document

Query

SELECT club.userId
FROM `dashboard-data` AS boardObjective
    JOIN `dashboard-data` AS club ON boardObjective.clubId = club.id
WHERE club.type = 'Club'
    AND boardObjective.type = 'BoardObjective'
    AND boardObjective.id = <boardObjectiveId>

Index

1. CREATE INDEX `IDX_boardObjectiveClubId` ON `dashboard-data`(`clubId`) WHERE (`type` = "BoardObjective")
2. CREATE INDEX `IDX_clubId` ON `dashboard-data`(`id`) WHERE (`type` = "Club")

Explanation

Similar to the IDX_playerClubId index, the first one covers the clubId property in documents of type BoardObjective. The second one covers the id property in documents of type Club and is mandatory for the same reasons as mentioned above.

To fetch the board objective entries corresponding to a given club id

Query

SELECT boardObjective.*
FROM `dashboard-data` AS boardObjective
WHERE boardObjective.type = 'BoardObjective'
    AND boardObjective.clubId = <clubId>

Index

The IDX_boardObjectiveId index is used here to cover the properties used in the WHERE clause.