manticore.conf

common {
    plugin_dir = /usr/local/lib/manticore
}

searchd {
    listen = 9306:mysql41
    listen = /var/run/mysqld/mysqld.sock:mysql41
    listen = 9312
    listen = 9308:http
    log = /var/log/manticore/searchd.log
    query_log = /var/log/manticore/query.log
    pid_file = /var/run/manticore/searchd.pid
    query_log_format = sphinxql
}

source games_source {
  type             = mysql
  # default host for local game-node-server
  # edit in prod
  sql_host         = db
  # default username for local game-node-server
  # edit in prod
  sql_user         = gamenode
  # default password for local game-node-server
  # edit in prod
  sql_pass         = gamenode
  sql_db           = gamenode
  sql_query_pre    = SET CHARACTER_SET_RESULTS=utf8
  sql_query_pre    = SET NAMES utf8
  sql_query       = \
  SELECT game.id, \
         game.name, \
         game.slug, \
         game.category, \
         game.status, \
         game.url, \
         UNIX_TIMESTAMP(game.firstReleaseDate) first_release_date, \
         UNIX_TIMESTAMP(game.createdAt)        created_at, \
         UNIX_TIMESTAMP(game.updatedAt)        updated_at, \
         gameCover.url AS                      cover_url, \
         alternativeNames                      alternative_names, \
         platformsNames                        platforms_names, \
         platformsAbbreviations                platforms_abbreviations, \
         genresNames                           genres_names, \
         keywordsNames                         keywords_names, \
         themesNames                           themes_names, \
         game_statistics.likesCount                 num_likes, \
         game_statistics.viewsCount                 num_views, \
         'manticore'   AS                      source \
  FROM game \
           LEFT JOIN gamenode.game_cover AS gameCover on gamenode.game.id = gameCover.gameId \
           LEFT JOIN (SELECT gan.gameId, GROUP_CONCAT(DISTINCT gan.name SEPARATOR ', ') alternativeNames FROM game_alternative_name gan GROUP BY gan.gameId) \
                     gameAlternativeName ON gameAlternativeName.gameId = game.id \
           LEFT JOIN (SELECT gpgg.gameId, \
                             GROUP_CONCAT(DISTINCT game_platform.name SEPARATOR ', ')         platformsNames, \
                             GROUP_CONCAT(DISTINCT game_platform.abbreviation SEPARATOR ', ') platformsAbbreviations \
                      FROM game_platform \
                               INNER JOIN game_platform_games_game gpgg ON gpgg.gamePlatformId = game_platform.id \
                      GROUP BY gpgg.gameId) AS gamePlatform ON game.id = gamePlatform.gameId \
           LEFT JOIN (SELECT gggg.gameId, GROUP_CONCAT(DISTINCT game_genre.name SEPARATOR ', ') AS genresNames \
                      FROM game_genre \
                               INNER JOIN game_genres_game_genre gggg ON game_genre.id = gggg.gameGenreId \
                      GROUP BY gggg.gameId) gameGenre ON gameGenre.gameId = game.id \
           LEFT JOIN (SELECT gkgk.gameId, GROUP_CONCAT(DISTINCT game_keyword.name SEPARATOR ', ') AS keywordsNames \
                      FROM game_keyword \
                               INNER JOIN game_keywords_game_keyword gkgk on game_keyword.id = gkgk.gameKeywordId \
                      GROUP BY gkgk.gameId) gameKeywords ON gameKeywords.gameId = game.id \
           LEFT JOIN (SELECT gtgt.gameId, GROUP_CONCAT(DISTINCT game_theme.name SEPARATOR ', ') AS themesNames \
                      FROM game_theme \
                               INNER JOIN game_themes_game_theme gtgt ON game_theme.id = gtgt.gameThemeId \
                      GROUP BY gtgt.gameId) gameTheme ON gameTheme.gameId = game.id \
          LEFT JOIN game_statistics ON game.id = game_statistics.gameId;


  sql_attr_timestamp = created_at
  sql_attr_timestamp = updated_at
  sql_attr_timestamp = first_release_date
  sql_attr_string = url
  sql_attr_string = cover_url
  sql_attr_string = source
  sql_attr_bigint = category
  sql_attr_bigint = status
  sql_attr_bigint = num_views
  sql_attr_bigint = num_likes

}

source users_source {
    type             = mysql
    # default host for local game-node-server
    sql_host         = db
    # default username for local game-node-server
    sql_user         = gamenode
    # default password for local game-node-server
    sql_pass         = gamenode
    sql_db           = gamenode
    sql_query_pre    = SET CHARACTER_SET_RESULTS=utf8
    sql_query_pre    = SET NAMES utf8
    sql_query       = \
    SELECT ROW_NUMBER() over (ORDER BY createdAt) AS 'id', userId AS user_id, username, UNIX_TIMESTAMP(createdAt) AS created_at, UNIX_TIMESTAMP(updatedAt) AS updated_at \
    FROM profile \
    ORDER BY createdAt DESC;
    sql_attr_timestamp = created_at
    sql_attr_timestamp = updated_at
    sql_attr_string = user_id


}

table games {
  dict = keywords
  type = plain
  source = games_source
  path = /var/lib/manticore/games
}

table users {
  dict = keywords
  type = plain
  source = users_source
  path = /var/lib/manticore/users
  min_infix_len = 3
}