private async Task <IEnumerable <CollectionItem> > _filterAudience(string user, bool isOwner, int dbId, int count, int under = int.MaxValue, int above = int.MinValue, RelevantEntitiesService.IQueryStatement query = null)
{
var postfix = "order by \"CollectionItemId\" desc " + (count > 0 ? $"limit {count}" : "");
if (isOwner && query == null)
{
return(await _connection.QueryAsync <CollectionItem>("select * from \"CollectionItems\" WHERE \"CollectionItemId\" < @Under and \"CollectionItemId\" > @Above and \"CollectionId\" = @DbId " + postfix, new { Under = under, Above = above, DbId = dbId }));
}
int?userId = null;
if (user != null)
{
userId = await _entityStore.ReverseAttribute(user, false);
}
if (userId == null && query == null)
{
return(await _connection.QueryAsync <CollectionItem>("select * from \"CollectionItems\" WHERE \"CollectionItemId\" < @Under and \"CollectionItemId\" > @Above and \"IsPublic\" = TRUE and \"CollectionId\" = @DbId " + postfix, new { Under = under, Above = above, DbId = dbId }));
}
var ids = new List <int>();
foreach (var audienceId in _audienceIds)
{
var id = await _entityStore.ReverseAttribute(audienceId, false);
if (id.HasValue)
{
ids.Add(id.Value);
}
}
var queryMap = new Dictionary <string, int>();
if (query != null)
{
foreach (var typeId in query.RequiredProperties)
{
var id = await _entityStore.ReverseAttribute(typeId, true);
queryMap[typeId] = id.Value;
}
}
// select c.* from "CollectionItems" c, "TripleEntities" e WHERE e."EntityId" = c."ElementId" and "CollectionItemId" < @Under and exists(select 1 from "Triples" where "PredicateId" = any(@Ids) and "AttributeId" = @UserId and "SubjectId" = e."IdId" and "SubjectEntityId" = e."EntityId" limit 1)
return(await _connection.QueryAsync <CollectionItem>(
"select c.* from \"CollectionItems\" c, \"TripleEntities\" a WHERE a.\"EntityId\" = c.\"ElementId\" and \"CollectionItemId\" < @Under and \"CollectionItemId\" > @Above and \"CollectionId\" = @DbId "
+ (isOwner ? "" : userId == null ? " and c.\"IsPublic\" = true" : "and exists(select 1 from \"Triples\" where \"PredicateId\" = any(@Ids) and \"AttributeId\" = @UserId and \"SubjectId\" = a.\"IdId\" and \"SubjectEntityId\" = a.\"EntityId\" limit 1) ")
+ (query == null ? " " : $" and {query.BuildSQL(queryMap)} ")
+ "order by c.\"CollectionItemId\" desc " + (count > 0 ? $"limit {count}" : ""),
new { Under = under, Above = above, Ids = ids, UserId = userId ?? 0, DbId = dbId }
));
}
private async Task <List <APEntity> > _search(Dictionary <string, string> lookFor, int?inCollectionId = null)
{
var attributeMapping = new Dictionary <int, int>();
foreach (var val in lookFor)
{
var reverseId = await _entityStore.ReverseAttribute(val.Key, false);
if (reverseId == null)
{
return(new List <APEntity>());
}
var reverseVal = await _entityStore.ReverseAttribute(val.Value, false);
if (reverseVal == null)
{
return(new List <APEntity>());
}
attributeMapping[reverseId.Value] = reverseVal.Value;
}
var start = $"select a.* from \"TripleEntities\" a where ";
start += string.Join(" and ", attributeMapping.Select(a => $"exists(select 1 from \"Triples\" where \"PredicateId\" = {a.Key} and \"AttributeId\" = {a.Value} and \"SubjectId\" = a.\"IdId\" and \"SubjectEntityId\" = a.\"EntityId\")"));
if (inCollectionId != null)
{
start += $" and exists(select 1 from \"CollectionItems\" where \"CollectionId\" = {inCollectionId} and \"CollectionItemId\" = a.\"EntityId\")";
}
return(await _entityStore.GetEntities((await _connection.QueryAsync <APTripleEntity>(start)).Select(a => a.EntityId).ToList()));
}