protected async Task <T> QuerySingleAsync <T>(string sql, object parameters = null)
{
using (IDbConnection connection = new Npgsql.NpgsqlConnection(this.connectionString))
{
try
{
connection.Open();
return(await connection.QuerySingleAsync <T>(sql, parameters));
}
catch (Exception exc)
{
Console.WriteLine(exc);
throw exc;
}
}
}
/// <summary>
/// Fetches multiple Venues
/// </summary>
/// <param name="page">The current page number</param>
/// <param name="count">The page size</param>
/// <returns>Returns a result containing an optional list of items</returns>
public async Task <Result <Maybe <PaginatedResult <Venue> > > > FetchCountedQueriedCompanyVenues(int companyId, string query, int page, int count)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
var lookup = new Dictionary <int, Venue>();
var totalCount = await con.QuerySingleAsync <int>(@"SELECT COUNT(*) FROM ""Venue"" where companyId = @CompanyId AND (@Query <-> ""Venue"".venuename > 0.3 or @Query <-> ""Venue"".venueaddress > 0.3);", new { CompanyId = companyId, Query = query }).ConfigureAwait(false);
var data = (await con.QueryAsync <Venue, Tag, Image, Venue>(@"
select ""Venue"".*, ""Tag"".*, ""Image"".* from ""Venue""
left outer join ""VenueTag""
on ""VenueTag"".venueId = ""Venue"".venueId
left outer join ""Tag""
on ""Tag"".tagId = ""VenueTag"".tagId
left outer join ""Image""
on ""Image"".imageid = ""Venue"".imageid
where ""Venue"".companyId = @CompanyId
and (@Query <-> ""Venue"".venuename > 0.3
or @Query <-> ""Venue"".venueaddress > 0.3)
group by ""Venue"".venueid, ""Image"".imagekey, ""VenueTag"".venuetagid, ""Tag"".tagid, ""Image"".imageid
order by @Query <-> ""Venue"".venuename, @Query <-> ""Venue"".venueaddress
LIMIT @Limit OFFSET @Offset",
map: (Venue venue, Tag tag, Image image) =>
{
Venue curVenue;
if (!lookup.TryGetValue(venue.VenueId, out curVenue))
{
lookup.Add(venue.VenueId, curVenue = venue);
}
if (venue.Tags == null)
{
venue.Tags = new List <string>();
}
if (tag != null)
{
venue.Tags.Add(tag.TagName);
}
if (image != null)
{
venue.ImageUrl = $"https://s3-eu-west-1.amazonaws.com/{settings.Connection.S3BucketName}/images/{curVenue.CompanyId}__{image.ImageKey}__img";
}
return(curVenue);
},
splitOn: "tagId,imageId",
param: new { CompanyId = companyId, Limit = count, Offset = page * count, Query = query }
).ConfigureAwait(false)).GroupBy(x => x.VenueId).Select(y => y.First()).ToList();
if (data == null)
{
return(Result.Ok(Maybe <PaginatedResult <Venue> > .None));
}
return(Result.Ok(Maybe <PaginatedResult <Venue> > .From(new PaginatedResult <Venue>
{
Count = totalCount,
Data = data
})));
}
catch (Exception ex)
{
return(Result.Fail <Maybe <PaginatedResult <Venue> > >(ex.ToString()));
}
}