More complicated test cases for queries with sorting and filtering

1. Sorting and filtering with `abilities` query

  abilities(
    skip: Int = 0, 
    take: Int = 104,
    onlyKingdoms: [TowerKingdom!] = [KR, KRF, KRO, KRV],
    onlyTowerTypes: [TowerType!] = [BARRACKS, ARCHER, ARTILLERY, MAGE], 
    sortDefinition: [AbilitySortDefinitionElement!] = [
     {column: towerName, sortOrder: ASCEND},
     {column: abilityName, sortOrder: ASCEND}], 
  ): [Ability!]!

2. Sorting and filtering with `attackTowers` query

  attackTowers(
    skip: Int = 0, 
    take: Int = 104,
    onlyKingdoms: [TowerKingdom!] = [KR, KRF, KRO, KRV], 
    onlyLevels: [TowerLevel!] = [LVL1, LVL2, LVL3, LVL4],
    onlyTowerTypes: [AttackTowerType!] = [ARCHER, ARTILLERY, MAGE],
    sortDefinition: [AttackSortDefinitionElement!] = [{column: id, sortOrder: ASCEND}], 
  ): [AttackTower!]!

3. Sorting and filtering with `barracksTowers` Query

  barracksTowers(
    skip: Int = 0,
    take: Int = 104,
    onlyKingdoms: [TowerKingdom!] = [KR, KRF, KRO, KRV],
    onlyLevels: [TowerLevel!] = [LVL1, LVL2, LVL3, LVL4],
    sortDefinition: [BarracksSortDefinitionElement!] = [{column: id, sortOrder: ASCEND}],
  ): [BarracksTower!]!

4. Sorting and filtering with `buildSequences` Query

  buildSequences(
    skip: Int = 0,
    take: Int = 104,
    onlyKingdoms: [TowerKingdom!] = [KR, KRF, KRO, KRV],
    onlyTowerTypes: [TowerType!] = [BARRACKS, ARCHER, ARTILLERY, MAGE],
    sortDefinition: [BuildSequenceSortDefinitionElement!] = [
      {column: kingdom, sortOrder: ASCEND}, 
      {column: towerType, sortOrder: ASCEND},
      {column: towerName, sortOrder: ASCEND}],
  ): [BuildSequence!]!

5. Sorting and filtering with `Towers` Query

towers(
  skip: Int = 0,
  take: Int = 104,
  onlyKingdoms: [TowerKingdom!] = [KR, KRF, KRO, KRV], 
  onlyLevels: [TowerLevel!] = [LVL1, LVL2, LVL3, LVL4],
  onlyTowerTypes: [TowerType!] = [BARRACKS, ARCHER, ARTILLERY, MAGE],
  sortDefinition: [SortDefinitionElement!] = [{column: id, sortOrder: ASCEND}],
): [TowerWithStats!]!

To read

Collect all images for tower thumbnails

References:

Improve GraphQL API, best practices

Is sorting like this the best way?

Towers(
    skip: 5,
    take: 10,
    onlyLevels: [1, 2, 3],
    onlyTypes: ["BARRACKS", "MAGE"]
    onlyKingdoms: ["KR", "KRV"],
    sortBy: [
        {column: "name", order: "ASCENDING"},
        {column: "buildCost", order: "DESCENDING"},
        {column: "damageMaximum", order: "ASCENDING"},
        {column: "kingdom", order: "DESCENDING"},
        {column: "towerType", order: "ASCENDING"},
        {column: "towerLevel", order: "ASCENDING"},
        {column: "id", order: "ASCENDING"},
        {column: "damageMinimum", order: "ASCENDING"},
    ]
)

sorting

Hasura
https://hasura.io/docs/1.0/graphql/core/api-reference/graphql-api/query.html#orderbyexp

query {
  author(order_by: {rating: desc}) {
    name
    rating
  }
}

query {
  article(order_by: [{id: desc}, {author: {id: asc}}]) {
    title
    rating
  }
}

Possible readings:

graphql mastery
https://course.graphqlmastery.com/course/graphql-technology/why-do-we-need-graphql

Custom errors:

https://medium.com/@estrada9166/return-custom-errors-with-status-code-on-graphql-45fca360852

Convert `float!` to `Int` for all queries and types etc

type Ability {
  abilityDescription: String!
  abilityId: Float!
  abilityName: String!
  kingdom: TowerKingdom!
  levelCosts: [Float!]!
  numberOfLevels: Float!
  totalAbilityCost: Float!
  totalCostWithTowers: Float!
  towerId: Float!
  towerImageUrl: String!
  towerName: String!
  towerType: TowerType!
}

FIX: SQL statement not sorting ability name alphabetically

This SQL statement is suppose to sort by tower name then by ability name, but it is not sorting by ability name

SELECT
ability_table."towerName" AS "towerName",
ability_table."abilityName" AS "abilityName",
ability_table."abilityDescription",
ability_table."totalAbilityCost",
ability_table."towerId",
ability_table."abilityId",
ability_table.kingdom,
ability_table."towerType",
ability_table."numberOfLevels",
ability_table."levelCosts",
(m4."buildCost" + m3."buildCost" + m2."buildCost" + m1."buildCost" + "totalAbilityCost") AS "totalCostWithTowers"
FROM build_sequence as bs
INNER JOIN "Towers" AS t4 ON t4.id = bs."level4Id" INNER JOIN main_stats AS m4 ON t4.id = m4.id
INNER JOIN "Towers" AS t3 ON t3.id = bs."level3Id" INNER JOIN main_stats AS m3 ON t3.id = m3.id
INNER JOIN "Towers" AS t2 ON t2.id = bs."level2Id" INNER JOIN main_stats AS m2 ON t2.id = m2.id
INNER JOIN "Towers" AS t1 ON t1.id = bs."level1Id" INNER JOIN main_stats AS m1 ON t1.id = m1.id
INNER JOIN (
    SELECT
        "Towers".name AS "towerName",
        ability.name AS "abilityName",
        ability.description AS "abilityDescription",
        SUM(cost) AS "totalAbilityCost",
        "Towers".id AS "towerId",
         ability.id AS "abilityId",
        "Towers".kingdom AS kingdom,
        "Towers"."towerType" AS "towerType",
        COUNT(ability_level.level) AS "numberOfLevels",
        ARRAY_AGG (ability_level.cost) "levelCosts"
    FROM "Towers"
        INNER JOIN ability ON ability."towerId" = "Towers".id
        INNER JOIN ability_level ON ability.id = ability_level."abilityId"
    GROUP BY "Towers".name, ability.name, "Towers".id, ability.id
) AS ability_table ON t4.name = ability_table."towerName"
WHERE 
  (t4.kingdom = 'kingdom rush' OR t4.kingdom = 'kingdom rush: frontiers' OR t4.kingdom = 'kingdom rush: origins' OR t4.kingdom = 'kingdom rush: vengeance') AND
  (t4."towerType" = 'barracks' OR t4."towerType" = 'archer' OR t4."towerType" = 'artillery' OR t4."towerType" = 'mage') 
ORDER BY "towerName" ASC, "abilityName" ASC 
LIMIT 104 OFFSET 0

SAMPLE RESULT

{
  "data": {
    "abilities": [
      {
        "towerName": "500mm big bertha",
        "abilityName": "cluster launcher xtreme",
        "kingdom": "KR"
      },
      {
        "towerName": "500mm big bertha",
        "abilityName": "dragonbreath launcher",
        "kingdom": "KR"
      },
      {
        "towerName": "arcane archers",
        "abilityName": "Slumber Arrows",
        "kingdom": "KRO"
      },
      {
        "towerName": "arcane archers",
        "abilityName": "burts arrors",
        "kingdom": "KRO"
      },
      {
        "towerName": "arcane wizard",
        "abilityName": "death ray",
        "kingdom": "KR"
      },

Query #7 Single Tower

towerById(id: 0) {
  allTowerStats
  abilities?
  buildSequence?
}

allTowerStats = {
    id: Number
    towerType: TowerType
    level: TowerLevel
    name: string
    kingdom: TowerKingdom
    imageUrl: string
    buildCost: Number
    damageMinimum: Number
    damageMaximum: Number
    fireInterval? Number
    range? Number
    numberOfUnits? Number
    armor? Number
    health? Number
    respawnInterval? Number
}

Set up CI and Deploy to heroku

You can now run graphql queries at

http://kingdom-rush-graphql.herokuapp.com/

FIX: Add abilities for barbarian mead hall

Currently there is no information about this tower in any abilities.yml please provide.

FIX: Some IDs are still floats, convert them to ints

    abilitiesByTowerId(id: Float!): [Ability!]!
    abilityById(id: Float!): Ability
    buildSequenceById(id: Float!): BuildSequence
    buildSequenceByTowerId(id: Float!): BuildSequence
    towerById(id: Float!): TowerVerbose

Query #9: More build sequence queries

buildSequenceById()
buildSequenceByTowerId()
buildSequenceByTowerName()

Towers

Towers(
    skip: 5,
    take: 10,
    onlyLevels: [1, 2, 3],
    onlyTypes: [BARRACKS, MAGE]
    onlyKingdoms: [KR, KRV],
    sortBy: [
        {column: "name", order: "ASCENDING"},
        {column: "kingdom", order: "ASCENDING"},
        {column: "towerType", order: "ASCENDING"},
        {column: "towerLevel", order: "ASCENDING"},
        {column: "id", order: "ASCENDING"},
        {column: "buildCost", order: "ASCENDING"},
        {column: "damageMinimum", order: "ASCENDING"},
        {column: "damageMaximum", order: "ASCENDING"},
    ]
)

Abilities

type Ability {
  abilityDescription: String!
  abilityId: Float!
  abilityName: String!
  kingdom: TowerKingdom!
  levelCosts: [Float!]!
  numberOfLevels: Float!
  totalAbilityCost: Float!
  totalCostWithTowers: Float!
  towerId: Float!
  towerImageUrl: String!
  towerName: String!
  towerType: TowerType!
}

Abilities(
  skip,
  take,
  onlyKingdom = []
  onlyTowerType = []
  sortDefinition [
    towerNames
    abilityName 
    totalCost
    totalCostWithTowers
    kingdom
    towerType
  ]
)

SearchAbilityName(text: "")

New Query: towerByName

And accompanying tests:

does not exist
exists, not level 4, barracks
exists, not level 4, not barracks
exists, level 4, barracks
exists, level4, not barracks

Change constraint names to more meaningful names

Currently the foreign key is not readable IE

        constraint_name         |   table_name   | column_name | foreign_table_name | foreign_column_name 
--------------------------------+----------------+-------------+--------------------+---------------------
 PK_8c82d7f526340ab734260ea46be | migrations     | id          | migrations         | id
 PK_a9fe6ef57784aff6b73159e9b4d | main_stats     | id          | main_stats         | id
 REL_38a6e0a47022a1c957b9018646 | main_stats     | towerId     | main_stats         | towerId
 PK_06005d4dc8ba963484acae34726 | barracks_stats | id          | barracks_stats     | id
 REL_d5dcdad1a2b257a2445db6106f | barracks_stats | towerId     | barracks_stats     | towerId
 PK_e7cb32239cb7dfd5eebae4d0eaf | attack_stats   | id          | attack_stats       | id
 REL_a183facc6b66a4851553798f60 | attack_stats   | towerId     | attack_stats       | towerId
 PK_d35a4e5481305c4848b560a3354 | Towers         | id          | Towers             | id
 unique_tower                   | Towers         | name        | Towers             | kingdom
 unique_tower                   | Towers         | name        | Towers             | name
 unique_tower                   | Towers         | kingdom     | Towers             | kingdom
 unique_tower                   | Towers         | kingdom     | Towers             | name
 FK_38a6e0a47022a1c957b90186462 | main_stats     | towerId     | Towers             | id
 FK_d5dcdad1a2b257a2445db6106fb | barracks_stats | towerId     | Towers             | id
 FK_a183facc6b66a4851553798f608 | attack_stats   | towerId     | Towers             | id
 PK_433b7560ea75956d78120228a2c | ability_level  | id          | ability_level      | id
 PK_5643559d435d01ec126981417a2 | ability        | id          | ability            | id
 FK_0d9185e58cdacbdb7787c410d62 | ability_level  | abilityId   | ability            | id
 FK_b8d8816b111ff43dc4f8a9f6afe | ability        | towerId     | Towers             | id
 PK_11cbd4cf88c203da6f6e0c22dbe | build_sequence | id          | build_sequence     | id
 REL_c598f4f8ace2c65225b034987f | build_sequence | level4Id    | build_sequence     | level4Id
 FK_a0485cb10760fbbe70d7bfea439 | build_sequence | level1Id    | Towers             | id
 FK_e7720dd1227b31428bdd710bac9 | build_sequence | level2Id    | Towers             | id
 FK_fbdaaafa5aaf958509352c74637 | build_sequence | level3Id    | Towers             | id
 FK_c598f4f8ace2c65225b034987f3 | build_sequence | level4Id    | Towers             | id
(25 rows)

Add attacks stats_table

BaseAttackStats / UpgradedAttackStats

fire_interval
range

SpecialTowers

SpecialTowers(
  skip: 5,
  take: 10,
  onlyTypes: [BARRACKS, MAGE]
  onlyKingdoms: [KR, KRV],
  sortBy: [
        {column: "Total build cost", order: "ASCENDING"},
        {column: "Total build cost with full upgrades", order: "ASCENDING"},
        {column: "name", order: "ASCENDING"},
        {column: "kingdom", order: "ASCENDING"},
        {column: "towerType", order: "ASCENDING"},
        {column: "towerLevel", order: "ASCENDING"},
        {column: "id", order: "ASCENDING"},
        {column: "buildCost", order: "ASCENDING"},
        {column: "damageMinimum", order: "ASCENDING"},
        {column: "damageMaximum", order: "ASCENDING"},
  ],
  buildSequence: [tower1, tower2, tower3, tower4]
)

Directory Restructure

Right now it's structured like this:

args
- tower
- buildSequence
- abilities

inputs
- tower
- buildSequence
- abilities

objects
- tower
- buildSequence
- abilities

enums
- tower
- buildSequence
- abilities

It would be better to structure it like this

tower
- towers
- objects
- inputs
- enums

buildSequence
- towers
- objects
- inputs
- enums

abilities
- towers
- objects
- inputs
- enums

Allow updates in the `seed` function not just `inserts`

seed should update data if it already exists in the database
The current seed function only allows insertion of new data. Currently, if the data exists, it will not update the existing data;seed/populate.js only allows for inserts and not updates.
The current implementation of db:reset is that the use/db/tables are dropped and recreated and all the data are populated all over again . This is VERY inefficient. https://github.com/mithi/kingdom-rush-graphql/blob/main/src/seed

Refactor python scripts that generate json.

Refactor python script because they contain a lot of duplicate code.
https://github.com/mithi/kingdom-rush-graphql/tree/main/scripts

Add new scripts for seeding

Write a python script to convert generated json to csv

Update pipeline

Update data saving pipeline

The current pipeline is like this:

update yaml
python scripts generate json file from yaml
Populate the database using the json files with populate.js
Populate the csv

It might be so much faster if the pipeline is like this:

update yaml
python scripts generate json files form yaml
use python script to generate csv files from json files
populate the database using the csv with the psql command COPY

Dockerize this project

References

Add a description column for each tower

There is currently no description for each tower. Add a one for each

Add small thumbnails for each tower

Current size of each tower image: 470 px x 470 px

Run smaller versions (maybe 128px by 128px ?) of each image
Store in google file storage
store thumbnail url in a column in the database

from os.path import isfile, join
from os import listdir, walk, path
from PIL import Image

kr_dir = "./raw"
all_image_file_path = []
all_image_names = []
for (dirpath, dirnames, filenames) in walk(kr_dir):

    if dirpath in ["./raw"]:
        continue

    for i, filename in enumerate(filenames):
        [title, extension] = filename.split(".")
        kingdom = dirpath.split("/")[2]

        if extension.lower() not in ["jpg", "jpeg", "png"]:
            continue
        new_filename = kingdom + "-" + title +  ".png"

        image_file_path = path.join(dirpath, filename)
        print("new name", new_filename)
        all_image_names.append(new_filename)
        print("image_file_path", image_file_path)
        all_image_file_path.append(image_file_path)

fill_color = '#ffffff'
for i, (image_path, image_name) in enumerate(zip(all_image_file_path, all_image_names)):
    new_path =  path.join("./flat", image_name)
    print(i, new_path)

    image = Image.open(image_path)

    if image.mode in ('RGBA', 'LA'):
        background = Image.new(image.mode[:-1], image.size, fill_color)
        background.paste(image, image.split()[-1])
        image = background
        image = image.resize((470, 470))
        image.save(new_path, quality=95)
    else:
        image = image.resize((470, 470))
        image.save(new_path, quality=95)

Query #8 More ability queries

AbilityById(id: "")
AbilityByName(name: "")
AbilitiesByTowerId(id: "")
AbilitiesByTowerName(name: "")

How to use global setup for setting up typeorm database and seeding it?

I want to run a global set up (creating the database and seeding it) before executing any test files.
I don't want to run them in every test file, I only need to run them once. I can't seem to find a way to do this.

Related issues

Guide / Sample for integration Testing [Jest] #5308

typeorm/typeorm#5308

So current problem is only how to pass connection properly so typeorm only uses 1 connection on multiple jest workers.
I'm having the same issue. When the connection is created in jest globalSetup it cannot be found within the tests. It's like the tests are running in a sandbox mode.

Using globalSetup and globalTeardown with TypeScript

jestjs/jest#10178

The code to create a connection and close it should be executed before all tests and after all tests are done, that's why we've added it to globalSetup.ts and globalTeardown.ts:

Organizing tests

https://homoly.me/posts/organizing-tests-with-jest-projects

Current test performance

Rename enum columns for sorting with a prefix "sortColumn"

It is a much more meaninful name

enum TowerSortColumn {
  buildCost
  damageMaximum
  damageMinimum
  id
  kingdom
  level
  name
  towerType
}

enum AttackTowerSortColumn {
  buildCost
  damageMaximum
  damageMinimum
  fireInterval
  id
  kingdom
  level
  name
  range
  towerType
}

enum BarracksTowerSortColumn {
  armor
  buildCost
  damageMaximum
  damageMinimum
  health
  id
  kingdom
  level
  name
  numberOfUnits
  respawnInterval
}

enum BuildSequenceSortColumn {
  kingdom
  totalAbilitiesCost
  totalBuildCost
  totalCostFullyUpgraded
  towerName
  towerType
}

enum AbilitySortColumn {
  abilityName
  kingdom
  totalCost
  totalCostWithTowers
  towerName
  towerType
}

Populate main_stats tables

BaseMainStats / UpgradedMainStats

build_cost
minimum_damage
maximum_damage

Free heroku database does not enable SSL validation

Because node-postgres enables SSL validation by default while free heroku hosting doesn’t provide it automatically, you need to turn it off

heroku config:set PGSSLMODE=no-verify

IMPORTANT: This is bad practice if you have sensitive data on your database.
We don't have sensitive data on our database.

Update models

Ability
- name column is unique and indexed
Ability level
- ability and level pair is unique
Towers
- include a imgUrl column
main_stats
- tower should be unique and indexed
attack_stats
- tower should be unique and indexed
barracks_stats
- tower should be unique and indexed
build_sequence
- build sequence should have unique level 4 id tower

FIX: SQL statement not sorting tower type alphabetically

SELECT * FROM "Towers" 
INNER JOIN main_stats ON "Towers".id = main_stats."towerId" 
INNER JOIN attack_stats ON main_stats."towerId" = attack_stats."towerId" 
WHERE (level = '1' OR level = '2' OR level = '3' OR level = '4') AND 
(kingdom = 'kingdom rush' OR kingdom = 'kingdom rush: frontiers') AND 
("towerType" = 'archer' OR "towerType" = 'artillery' OR "towerType" = 'mage') 
ORDER BY "towerType" ASC, "Towers".name DESC LIMIT 10 OFFSET 0

Sample result

{
  "data": {
    "attackTowers": [
      {
        "towerType": "ARCHER",
        "name": "tribal axethrowers"
      },
      {
        "towerType": "ARCHER",
        "name": "sharpshooter tower"
      },
      {
        "towerType": "ARCHER",
        "name": "sharpshooter tower"
      },
      {
        "towerType": "ARCHER",
        "name": "rangers hideout"
      },
      {
        "towerType": "ARCHER",
        "name": "musketeer garrison"
      },
      {
        "towerType": "ARCHER",
        "name": "marksman tower"
      },
      {
        "towerType": "ARCHER",
        "name": "marksman tower"
      },
      {
        "towerType": "ARCHER",
        "name": "crossbow fort"
      },
      {
        "towerType": "ARCHER",
        "name": "archer tower"
      },
      {
        "towerType": "ARCHER",
        "name": "archer tower"
      }
    ]
  }
}

Chores

Add ability level csv
Move data/generated/*json to data/generated/json/*.json update all related files
- data/scripts/*.py
- src/seed/*ts
Move .yaml files to raw directory, update related files
- data/scripts/*.py
Move seed testing to separate file from towers_db.test.js to seed_db.test.js
Convert enum from "kingdom rush: origin" to "kingdom rush: origins"

Create a staging environment for heroku + other things

Read: https://devcenter.heroku.com/articles/multiple-environments

Setup github actions test passing badge
Setup code coverage badge
Setup code climate badge

Unable to fetch schema

I was able to fetch schema before but now I am unable to. I don't know why.

Fix broken links

https://github.com/mithi/kingdom-rush-graphql/blob/main/docs/GOOD_FIRST_ISSUE.md

Add additional tests / test cases

Build Sequence

By Id

Id exists
Id does not exist

By Tower Id

Tower Id does not exists
Tower Id exists but is not level 4 tower
Tower Id exists and is a level 4 tower

By Tower Name

Tower name does not exists
Tower name exists but is not level 4 tower
Tower name exists and is a level 4 tower

(Verbose)Towers By Id

FIX: Sort definition array must have atleast one element

Convert from

sortDefinition: BuildSequenceSortDefinitionElement[]

to

sortDefinition: [BuildSequenceSortDefinitionElement]

Update the description for any ability

The descriptions of the tower abilities are a mess, and it needs your help! The description for each ability of each tower could be greatly improved. You can checkout Kingdom Rush TD fandom
for ideas to make it better, it contains more information about each ability.

Please checkout the `Good First Issue` document

https://github.com/mithi/kingdom-rush-graphql/blob/main/docs/GOOD_FIRST_ISSUE.md

Files to update

The current description of each ability can be found in these yaml files

Refactor seed test

Split the test into smaller tests

Create a build sequence table

level1: towerId
level2: towerId
level3: towerId
level4: towerId

Query #3: Barracks Towers

BarracksTowers

BarracksTowers(
    skip: 5,
    take: 10,
    onlyLevels: [1, 2, 3],
    onlyKingdoms: [KR, KRV],
    sortBy: [
        {column: "cooldown", order: "ASCENDING"},
        {column: "health", order: "ASCENDING"},
        {column: "armor", order: "ASCENDING"},
        {column: "numberOfUnits", order: "ASCENDING"}
        {column: "name", order: "ASCENDING"},
        {column: "kingdom", order: "ASCENDING"},
        {column: "towerType", order: "ASCENDING"},
        {column: "towerLevel", order: "ASCENDING"},
        {column: "id", order: "ASCENDING"},
        {column: "buildCost", order: "ASCENDING"},
        {column: "damageMinimum", order: "ASCENDING"},
        {column: "damageMaximum", order: "ASCENDING"},
    ]
)

Populate abilities and abilities level tables

abilities

tower
name
description

ability-level

ability
cost
level

Restructure directory

Move https://github.com/mithi/kingdom-rush-graphql/blob/main/data/notes/image-urls.txt to ./raw directory
Move scripts from ./data/scripts to ./scripts make sure all the scripts are still working, update all of them accordingly

Query #6 Build Sequences

buildSequences(
  skip: 1
  take: 5
  onlyKingdoms: []
  onlyTowerTypes: []
  sortBy: [
    tower4Name,
    towerType
    kingdom
    totalBuildCost
    totalAbilitiesCost
    totalCostFullyUpgraded
  ]
) {
  level1: { name, id, buildCost, imageUrl }
  level2: { name, id, buildCost, imageUrl }
  level3: { name, id, buildCost, imageUrl }
  level4: { name, id, buildCost, imageUrl }
  buildSequenceId
  kingdom
  towerType
  totalBuildCost
  totalAbilitiesCost
  totalBuildCostFullyUpgraded
}

buildSequenceById()
buildSequenceByTower4()

FIX: Unpredictability of `AbilityByTowerId` query

The problem with the old sql statement is that the elements of the array abilities could be in a jumbled or in any order.
Also the array of levels. by ability levels could also be jumbled or in any order.
In the new query, they would be sorted in ascending order by ability id and ability level respectively.
Changes

# before
WHERE t4.id = 5

# after
WHERE t4.id = 5
ORDER BY ability_table."abilityId" ASC;

# before
ARRAY_AGG (ability_level.cost) "levelCosts"

# after
ARRAY_AGG (ability_level.cost ORDER BY ability_level.level) "levelCosts"

This is the wrong (old) query

SELECT
ability_table."towerName" AS "towerName",
ability_table."abilityName" AS "abilityName",
ability_table."towerImageUrl" AS "towerImageUrl",
ability_table."abilityDescription",
ability_table."totalAbilityCost",
ability_table."towerId",
ability_table."abilityId",
ability_table.kingdom,
ability_table."towerType",
ability_table."numberOfLevels",
ability_table."levelCosts",
(m4."buildCost" + m3."buildCost" + m2."buildCost" + m1."buildCost" + "totalAbilityCost") AS "totalCostWithTowers"
FROM build_sequence as bs
INNER JOIN "Towers" AS t4 ON t4.id = bs."level4Id" INNER JOIN main_stats AS m4 ON t4.id = m4.id
INNER JOIN "Towers" AS t3 ON t3.id = bs."level3Id" INNER JOIN main_stats AS m3 ON t3.id = m3.id
INNER JOIN "Towers" AS t2 ON t2.id = bs."level2Id" INNER JOIN main_stats AS m2 ON t2.id = m2.id
INNER JOIN "Towers" AS t1 ON t1.id = bs."level1Id" INNER JOIN main_stats AS m1 ON t1.id = m1.id
INNER JOIN (
    SELECT
        "Towers".name AS "towerName",
        "Towers"."imageUrl" AS "towerImageUrl",
        ability.name AS "abilityName",
        ability.description AS "abilityDescription",
        SUM(cost) AS "totalAbilityCost",
        "Towers".id AS "towerId",
         ability.id AS "abilityId",
        "Towers".kingdom AS kingdom,
        "Towers"."towerType" AS "towerType",
        COUNT(ability_level.level) AS "numberOfLevels",
        ARRAY_AGG (ability_level.cost) "levelCosts"
    FROM "Towers"
        INNER JOIN ability ON ability."towerId" = "Towers".id
        INNER JOIN ability_level ON ability.id = ability_level."abilityId"
    GROUP BY "Towers".name, ability.name, "Towers".id, ability.id
) AS ability_table ON t4.name = ability_table."towerName"
WHERE t4.id = 5

This is the correct (new) query

SELECT
ability_table."towerName" AS "towerName",
ability_table."abilityName" AS "abilityName",
ability_table."towerImageUrl" AS "towerImageUrl",
ability_table."abilityDescription",
ability_table."totalAbilityCost",
ability_table."towerId",
ability_table."abilityId",
ability_table.kingdom,
ability_table."towerType",
ability_table."numberOfLevels",
ability_table."levelCosts",
(m4."buildCost" + m3."buildCost" + m2."buildCost" + m1."buildCost" + "totalAbilityCost") AS "totalCostWithTowers"
FROM build_sequence as bs
INNER JOIN "Towers" AS t4 ON t4.id = bs."level4Id" INNER JOIN main_stats AS m4 ON t4.id = m4.id
INNER JOIN "Towers" AS t3 ON t3.id = bs."level3Id" INNER JOIN main_stats AS m3 ON t3.id = m3.id
INNER JOIN "Towers" AS t2 ON t2.id = bs."level2Id" INNER JOIN main_stats AS m2 ON t2.id = m2.id
INNER JOIN "Towers" AS t1 ON t1.id = bs."level1Id" INNER JOIN main_stats AS m1 ON t1.id = m1.id
INNER JOIN (
    SELECT
        "Towers".name AS "towerName",
        "Towers"."imageUrl" AS "towerImageUrl",
        ability.name AS "abilityName",
        ability.description AS "abilityDescription",
        SUM(cost) AS "totalAbilityCost",
        "Towers".id AS "towerId",
         ability.id AS "abilityId",
        "Towers".kingdom AS kingdom,
        "Towers"."towerType" AS "towerType",
        COUNT(ability_level.level) AS "numberOfLevels",
        ARRAY_AGG (ability_level.cost ORDER BY ability_level.level) "levelCosts"
    FROM "Towers"
        INNER JOIN ability ON ability."towerId" = "Towers".id
        INNER JOIN ability_level ON ability.id = ability_level."abilityId"
    GROUP BY "Towers".name, ability.name, "Towers".id, ability.id
) AS ability_table ON t4.name = ability_table."towerName"
WHERE t4.id = 5
ORDER BY ability_table."abilityId" ASC;

Add barracks_stats table

BaseBarracksStats / UpgradedBarracksStats

health
respawn_interval
armor
number_of_units

Make all ability names, tower names, ability descriptions all lower case before storing in json or in the table

To be more consistent.

As a corollary, convert to query parameters to lower case before querying them in the database

Chores

Add header to CSVs
Rename test to tests

Query #2: AttackTowers

AttackTowers

AttackTowers(
    skip: 5,
    take: 10,
    onlyLevels: [1, 2, 3],
    onlyTypes: [MAGE, RANGED, ARTILLERY]
    onlyKingdoms: [KR, KRV],
    sortBy: [
        {column: "fireRate", order: "ASCENDING"},
        {column: "range", order: "ASCENDING"},
        {column: "name", order: "ASCENDING"},
        {column: "kingdom", order: "ASCENDING"},
        {column: "towerType", order: "ASCENDING"},
        {column: "towerLevel", order: "ASCENDING"},
        {column: "id", order: "ASCENDING"},
        {column: "buildCost", order: "ASCENDING"},
        {column: "damageMinimum", order: "ASCENDING"},
        {column: "damageMaximum", order: "ASCENDING"}
    ]
)

mithi / kingdom-rush-graphql Goto Github PK

kingdom-rush-graphql's Introduction

🌸 ✊

kingdom-rush-graphql's People

Contributors

Stargazers

Watchers

Forkers

kingdom-rush-graphql's Issues