public MalUserLookupResults GetAnimeListForUser(string user)
{
using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
{
conn.Open();
// ILIKE does a table scan. :( The ideal way of doing a case-insensitive search would be to use the citext
// data type, but that's an add-on and not part of a standard Postgres install.
// This class is only intended to be used for development anyway.
string sql = @"
SELECT mal_user.mal_name, mal_user.mal_user_id FROM mal_user WHERE mal_name ILIKE :UserName ORDER BY mal_user_id LIMIT 1;
SELECT
mal_anime.mal_anime_id, mal_anime.title, mal_anime.mal_anime_type_id, mal_anime.num_episodes, mal_anime.mal_anime_status_id,
mal_anime.start_year, mal_anime.start_month, mal_anime.start_day, mal_anime.end_year, mal_anime.end_month, mal_anime.end_day,
mal_anime.image_url,
mal_list_entry.rating, mal_list_entry.mal_list_entry_status_id, mal_list_entry.num_episodes_watched,
mal_list_entry.started_watching_year, mal_list_entry.started_watching_month, mal_list_entry.started_watching_day,
mal_list_entry.finished_watching_year, finished_watching_month, finished_watching_day, mal_list_entry.last_mal_update,
mal_list.synonyms, mal_list.tags
FROM
(
SELECT mal_user.mal_user_id AS mal_user_id, mal_anime.mal_anime_id AS mal_anime_id, array_agg(DISTINCT mal_anime_synonym.synonym) AS synonyms, array_agg(DISTINCT mal_list_entry_tag.tag) AS tags
FROM mal_user
JOIN mal_list_entry ON mal_user.mal_user_id = mal_list_entry.mal_user_id
JOIN mal_anime ON mal_list_entry.mal_anime_id = mal_anime.mal_anime_id
LEFT OUTER JOIN mal_anime_synonym ON mal_anime.mal_anime_id = mal_anime_synonym.mal_anime_id
LEFT OUTER JOIN mal_list_entry_tag ON mal_list_entry.mal_anime_id = mal_list_entry_tag.mal_anime_id AND mal_list_entry.mal_user_id = mal_list_entry_tag.mal_user_id
WHERE mal_user.mal_user_id = (SELECT mal_user_id FROM mal_user WHERE mal_name ILIKE :UserName ORDER BY mal_user_id LIMIT 1)
GROUP BY mal_user.mal_user_id, mal_anime.mal_anime_id
) AS mal_list
JOIN mal_user ON mal_list.mal_user_id = mal_user.mal_user_id
JOIN mal_anime ON mal_list.mal_anime_id = mal_anime.mal_anime_id
JOIN mal_list_entry ON mal_list.mal_user_id = mal_list_entry.mal_user_id AND mal_list.mal_anime_id = mal_list_entry.mal_anime_id
";
int userId;
string canonicalUserName = null;
List<MyAnimeListEntry> entries = new List<MyAnimeListEntry>();
using (SqlMapper.GridReader results = conn.QueryMultiple(sql, new { UserName = user }))
{
User u = results.Read<User>().FirstOrDefault();
if (u == null)
{
throw new MalUserNotFoundException(string.Format("No MAL list exists for {0}.", user));
}
userId = u.mal_user_id;
canonicalUserName = u.mal_name;
foreach (UserListEntry dbEntry in results.Read<UserListEntry>())
{
MalAnimeInfoFromUserLookup animeInfo = new MalAnimeInfoFromUserLookup(
animeId: dbEntry.mal_anime_id,
title: dbEntry.title,
type: (MalAnimeType)dbEntry.mal_anime_type_id,
synonyms: dbEntry.synonyms.Where(syn => syn != null).ToList(),
status: (MalSeriesStatus)dbEntry.mal_anime_status_id,
numEpisodes: dbEntry.num_episodes,
startDate: new UncertainDate(year: dbEntry.start_year, month: dbEntry.start_month, day: dbEntry.start_day),
endDate: new UncertainDate(year: dbEntry.end_year, month: dbEntry.end_month, day: dbEntry.end_day),
imageUrl: dbEntry.image_url
);
MyAnimeListEntry entry = new MyAnimeListEntry(
score: dbEntry.rating,
status: (CompletionStatus)dbEntry.mal_list_entry_status_id,
numEpisodesWatched: dbEntry.num_episodes_watched,
myStartDate: new UncertainDate(year: dbEntry.started_watching_year, month: dbEntry.started_watching_month, day: dbEntry.started_watching_day),
myFinishDate: new UncertainDate(year: dbEntry.finished_watching_year, month: dbEntry.finished_watching_month, day: dbEntry.finished_watching_day),
myLastUpdate: dbEntry.last_mal_update,
animeInfo: animeInfo,
tags: dbEntry.tags.Where(tag => tag != null).ToList()
);
entries.Add(entry);
}
return new MalUserLookupResults(userId: userId, canonicalUserName: canonicalUserName, animeList: entries);
}
}
}