unrec / imdb-searcher Goto Github PK
View Code? Open in Web Editor NEWMicroservice for searching within' IMDB database. Stack: Gradle, Kotlin, Spring Boot, GraphQL.
imdb-searcher's People
Contributors
Watchers
imdb-searcher's Issues
Add fields 'episodeNames' and 'episodes' to type 'Season'
Update type Season:
type Season {
season: Int
totalEpisodes: Int
...
episodeNames: [String]
episodes: [Episode]
}Add field 'startYear' to type 'Season'
Update type Season:
type Season {
season: Int
totalEpisodes: Int
startYear: Int
}To get this data within' single SQL this query is to be run:
SELECT parent_id, e.season_number, max_seasons.max_episode_number, b.start_year FROM episodes e
JOIN basics b USING(title_id)
JOIN (
SELECT e.season_number, max(episode_number) AS max_episode_number FROM episodes e
WHERE e.parent_id = 944947
GROUP BY e.season_number, parent_id
) AS max_seasons USING(season_number)
WHERE e.parent_id = 944947 AND e.episode_number=1Corresponding kotlin-exposed query failed for mapping:
fun findSeasonsByTitleId(id: Int): List<Season> = transaction {
val episodeMax = episode.max()
val subQuery = EpisodeTable.slice(season, episodeMax)
.select { parentId eq id }
.groupBy(season, parentId).alias("sub_query")
EpisodeTable
.join(subQuery, INNER, season, subQuery[season])
.join(BasicTable, INNER, BasicTable.titleId, titleId)
.slice(parentId, season, subQuery[episodeMax], BasicTable.startYear)
.select { (parentId eq id) and (episode eq 1) }
.map { row ->
Season(parentId = id,
season = row[season],
totalEpisodes = row[subQuery[episodeMax]],
startYear = row[BasicTable.startYear])
}
}See question on stackoverflow here.
Same was tried to be resolved with this code, but it produces N+1 SQL separate queries:
fun findSeasonStartYear(season: Season): Short? = {
val parentId = season.parentId
val seasonNum = season.season
val startId = episodeRepository.findStartEpisodeIdForSeason(parentId!!, seasonNum!!)
return basicRepository.getStartYearForTitleId(startId!!)
}
fun getStartYearForTitleId(id: Int) = transaction {
BasicTable.slice(startYear)
.select { titleId eq id }
.map { it[startYear] }
.firstOrNull()
}Add formatting for number of votes
"numVotes": 1229515 is to be "numVotes": 1,229,515
Try to switch to other DB access interfaces
At this stage JPA seems to be obsolete, as we only fetch data from the database and don't put or update any rows.
We can try to use plain jdbcTemplate or Exposed library.
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.