<commit_before>﻿using System.Data;
using Relisten.Api.Models;
using Dapper;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.Linq;
using System;
using Relisten.Import;
using System.Text;
using System.Reflection;

namespace Relisten.Data
{
	public class ArtistService : RelistenDataServiceBase
	{
		public ImporterService _importService { get; set; }

		public ArtistService(DbService db, ImporterService importService) : base(db)
		{
			_importService = importService;
		}

		private static Func<Artist, Features, Artist> joiner = (Artist artist, Features features) =>
			{
				artist.features = features;
				return artist;
			};

		public async Task<IEnumerable<ArtistWithCounts>> AllWithCounts()
		{
			return await FillInUpstreamSources(await db.WithConnection(con => con.QueryAsync(@"
                WITH show_counts AS (
                    SELECT
                        artist_id,
                        COUNT(*) as show_count
                    FROM
                        shows
                    GROUP BY
                        artist_id
                )

                SELECT
                    a.*, show_count, source_count, f.*
                FROM
                    artists a
                    LEFT JOIN features f on f.artist_id = a.id
                    LEFT JOIN show_counts sh ON sh.artist_id = a.id
                    LEFT JOIN show_source_information src ON src.artist_id = a.id
				ORDER BY
					a.featured DESC, a.name
            ", (ArtistWithCounts artist, Features features) =>
			{
				artist.features = features;
				return artist;
			})));
		}

		public Task<int> RemoveAllContentForArtist(Artist art)
		{
			return db.WithConnection(con => con.ExecuteAsync(@"
				delete from setlist_songs where artist_id = @ArtistId;
				delete from setlist_shows where artist_id = @ArtistId;
				delete from shows where artist_id = @ArtistId;
				delete from sources where artist_id = @ArtistId;
				delete from tours where artist_id = @ArtistId;
				delete from venues where artist_id = @ArtistId;
				delete from years where artist_id = @ArtistId;
				delete from eras where artist_id = @ArtistId;
			", new {
				ArtistId = art.id
			}));
		}
<commit_msg>Fix super broken query<commit_after>﻿using System.Data;
using Relisten.Api.Models;
using Dapper;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.Linq;
using System;
using Relisten.Import;
using System.Text;
using System.Reflection;

namespace Relisten.Data
{
	public class ArtistService : RelistenDataServiceBase
	{
		public ImporterService _importService { get; set; }

		public ArtistService(DbService db, ImporterService importService) : base(db)
		{
			_importService = importService;
		}

		private static Func<Artist, Features, Artist> joiner = (Artist artist, Features features) =>
			{
				artist.features = features;
				return artist;
			};

		public async Task<IEnumerable<ArtistWithCounts>> AllWithCounts()
		{
			return await FillInUpstreamSources(await db.WithConnection(con => con.QueryAsync(@"
                WITH show_counts AS (
                    SELECT
                        artist_id,
                        COUNT(*) as show_count
                    FROM
                        shows
                    GROUP BY
                        artist_id
                )

                SELECT
                    a.*, show_count, source_count, f.*
                FROM
                    artists a
                    LEFT JOIN features f on f.artist_id = a.id
                    LEFT JOIN show_counts sh ON sh.artist_id = a.id
                    LEFT JOIN (
						SELECT
							ssi.artist_id
							, SUM(ssi.source_count) as source_count
						FROM
							show_source_information ssi
						GROUP BY
							ssi.artist_id
					) src ON src.artist_id = a.id
				ORDER BY
					a.featured DESC, a.name
            ", (ArtistWithCounts artist, Features features) =>
			{
				artist.features = features;
				return artist;
			})));
		}

		public Task<int> RemoveAllContentForArtist(Artist art)
		{
			return db.WithConnection(con => con.ExecuteAsync(@"
				delete from setlist_songs where artist_id = @ArtistId;
				delete from setlist_shows where artist_id = @ArtistId;
				delete from shows where artist_id = @ArtistId;
				delete from sources where artist_id = @ArtistId;
				delete from tours where artist_id = @ArtistId;
				delete from venues where artist_id = @ArtistId;
				delete from years where artist_id = @ArtistId;
				delete from eras where artist_id = @ArtistId;
			", new {
				ArtistId = art.id
			}));
		}

{'fraction_non_alphanumeric': 0.07399347116430903, 'fraction_numerical': 0.0, 'mean_word_length': 2.14149008885851, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1999650', 'n_tokens_mistral': 1542, 'n_tokens_neox': 1287, 'n_words': 363}