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())); }