Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
excerpt from the IngressDB to show some of the queries used as wished in reaction to https://blog.faboo.org/2018/12/ingressdb-neo4j/
package org.faboo.ingressdb.repository;
import java.util.List;
import java.util.Map;
import org.faboo.ingressdb.domain.Fraction;
import org.faboo.ingressdb.domain.database.Agent;
import org.faboo.ingressdb.domain.database.Portal;
import org.faboo.ingressdb.domain.database.PortalAttack;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.neo4j.annotation.Query;
import org.springframework.data.neo4j.repository.GraphRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
@Repository
public interface AgentRepository extends GraphRepository<Agent> {
@Query(value = "match (a:Agent) where lower(a.agentName) =~{sub} and a.hideAgentName = false " +
" with a optional match (a)-[r:MAIN_ACC]-(u:User)" +
" return a, r ,u order by size(a.agentName) limit {limit}")
List<Agent> findBySubName(@Param("sub") String sub, @Param("limit") Long limit);
@Query(value = "match (a:Agent) where lower(a.agentName) =~{sub} and a.hideAgentName = false " +
" and not (a)-[:MAIN_ACC]-(:User)" +
" return a order by size(a.agentName), a.agentName limit {limit}")
List<Agent> findBySubNameNotAssignedToUser(@Param("sub") String sub, @Param("limit") Long limit);
Page<Agent> findByFraction(Fraction fraction, Pageable page);
@Query("match (a:Agent) where a.agentName = {agentName} return a")
Agent findByAgentName(@Param("agentName") String agentName);
@Query("match (a:Agent) where a.fraction = {fraction} return count(a)")
Long countByFraction(@Param("fraction") Fraction fraction);
@Query("match (a:Agent) where a.uid = {uid} with a " +
" optional match (a)-[r:MAIN_ACC]-(u:User) with a,u,r " +
" optional match (a)-[r1:USES]->(p:Portal) return a,r,u, p, r1")
Agent findByUid(@Param("uid") String uid);
@Query("match (a:Agent) where ID(a) = {id} with a " +
" optional match (a)-[r:MAIN_ACC]-(u:User) with a,u,r " +
" optional match (a)-[r1:USES]->(p:Portal) return a,r,u, p, r1")
Agent findByID(@Param("id") Long id);
@Query("match (a:Agent)-[:ATTACKED_BY]->(pa:PortalAttack) where a.uid = {uid} return count(pa)")
Long getDefendCount(@Param("uid") String uid);
@Query("match (a:Agent)-[:TAKES_PART]->(pa:PortalAttack) where a.uid = {uid} return count(pa)")
Long getAttackCount(@Param("uid") String uid);
@Query("match (a:Agent {uid:{uid}} )-->(pa:PortalAttack)-->(p:Portal) return p.wkt as wkt, count(pa) as count")
List<Map<String, Object>> getWeightedAgentAttacks(@Param("uid") String uid);
@Query("match (a:Agent {uid:{uid}})-->(pa:PortalAttack)-->(p:Portal) " +
" where pa.dayOfWeek = {weekday} and pa.quarterHourBucket >= {bucketFrom} and pa.quarterHourBucket <= {bucketTo}" +
" return pa.dayOfWeek as weekday, pa.quarterHourBucket as bucket, p.wkt as wkt")
List<Map<String, Object>> getWeekdayBucketsAttacks(@Param("uid") String uid
, @Param("weekday") Integer weekday
, @Param("bucketFrom") Long bucketFrom
, @Param("bucketTo") Long bucketTo);
@Query("match (a:Agent {uid:{uid}})-->(pa:PortalAttack)-->(p:Portal) " +
" return pa.dayOfWeek as weekday, pa.quarterHourBucket as bucket, p.wkt as wkt")
List<Map<String, Object>> getWeekdayBucketsAttacks(@Param("uid") String uid);
@Query(value = "match (a:Agent {uid:{uid}})-[]->(pa:PortalAttack)-->(p:Portal) return pa order by pa.attackTime desc limit 1")
PortalAttack findLastAttackForAgent(@Param("uid") String uid);
@Query(value = "match (a:Agent {uid:{uid}})-[]->(pa:PortalAttack)-->(p:Portal) return p order by pa.attackTime desc limit 1")
Portal findLastAttackedPortalForAgent(@Param("uid") String uid);
@Query(value = "match (a:Agent{uid:{uid}})-[:TAKES_PART|ATTACKED_BY]-(pa:PortalAttack)-[:PORTAL]->(p:Portal) " +
" return pa.attackTime as time, p.portalName as portalName, p.imageURL as image, p.portalAddress as address" +
", p.uid as portalUID, p.wkt as portalWKT order by pa.attackTime desc skip {offset} limit {limit}")
List<Map<String, Object>> findSortedAttackForAgent(@Param("uid") String uid, @Param("offset") long offset, @Param("limit") long limit);
@Query(value = "match (a:Agent{uid:{uid}})-[:TAKES_PART|ATTACKED_BY]-(pa:PortalAttack)-[:PORTAL]->(p:Portal) where pa.attackTime < {before}" +
" return pa.attackTime as time, p.portalName as portalName, p.imageURL as image, p.portalAddress as address" +
", p.uid as portalUID, p.wkt as portalWKT order by pa.attackTime desc skip {offset} limit {limit}")
List<Map<String, Object>> findSortedAttackForAgentBefore(@Param("uid") String uid
, @Param("offset") long offset
, @Param("limit") long limit
, @Param("before") long before);
@Query(value = "match (a:Agent{uid:{uid}})--(pa:PortalAttack) where pa.attackTime < {before} return count(*)")
Integer findSortedAttackForAgentBeforeSize(@Param("uid") String uid, @Param("before") long before);
//match (a:Agent) where a.agentName =~ "Ben.*"
//optional match (a:Agent)-[:TAKES_PART]->(pa:PortalAttack)
//optional match (a:Agent)-[:MAIN_ACC]->(u:User)
// return a.agentName, a.currentLevel, u.gplusName, a.firstSeen, a.uid, a.lastUpdate, count(pa) order by a.agentName limit 25;
@Query(value = "match (a:Agent {uid:{uid}})-[:TAKES_PART|ATTACKED_BY]-(pa:PortalAttack)--(p:Portal) " +
" where NOT (a)-->(p)" +
" return p.uid as portalUID, p.portalName as portalName, p.imageURL as portalImageURL" +
", p.wkt as portalWKT, p.portalAddress as portalAddress, count(pa) as count " +
" order by count(pa) desc limit 10")
List<Map<String, Object>> getMostAttackedPortalsForAgent(@Param("uid") String uid);
@Query(value = "match (o:Agent)-[:TAKES_PART]->(pa:PortalAttack)<-[:TAKES_PART]-(a:Agent {uid:{uid}}) " +
" return o.uid as uid, o.agentName as agentName, count(pa) as count order by count(pa) DESC limit 20")
List<Map<String, Object>> findAttackPartners(@Param("uid") String uid);
@Query(value = "match (o:Agent)-[:ATTACKED_BY]->(pa:PortalAttack)<-[:ATTACKED_BY]-(a:Agent {uid:{uid}}) " +
" return o.uid as uid, o.agentName as agentName, count(pa) as count order by count(pa) DESC limit 20")
List<Map<String, Object>> findDefendPartners(@Param("uid") String uid);
}
package org.faboo.ingressdb.repository;
import org.faboo.ingressdb.domain.database.PortalAttack;
import org.springframework.data.neo4j.annotation.Query;
import org.springframework.data.neo4j.repository.GraphRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
/**
* Created by bert
*/
@Repository
public interface PortalAttackRepository extends GraphRepository<PortalAttack> {
@Query(value = "match (p:Portal)<-[r:PORTAL]-(pa:PortalAttack) " +
" where ID(p) = {0} and pa.lowerBound <= {1} and pa.upperBound >= {1} return pa, p, r")
PortalAttack loadAttackByPortalAndTimeWindow(Long portalID, Long time);
@Query(value = "match (a:Agent {uid:{uid}})-[:TAKES_PART]->(pa:PortalAttack) " +
" where pa.dayOfWeek={weekday}" +
" return pa.quarterHourBucket as bucket, count(pa.quarterHourBucket) as count")
List<Map<String, Object>> getBucketSumsForAgent(@Param("uid") String agentUID, @Param("weekday") int weekday);
@Query("MATCH (p:Portal)<-[:PORTAL]-(pa:PortalAttack)<--(a:Agent) WHERE a.uid= {agentUid} " +
" AND p.uid = {portalUid} RETURN pa.attackTime ORDER BY pa.attackTime ASC")
List<Long> findAttackTimesForAgentAndPortal(@Param("agentUid") String agentUid, @Param("portalUid") String portalUid);
@Query("MATCH path=(p:Portal {uid:{portalUid}})<-[:PORTAL]-(pa:PortalAttack)<-[:OWNER]-(a:Agent{uid:{agentUid}}) " +
" RETURN count(path)")
Long hasAgentBeenOwnerOfPortal(@Param("agentUid") String agentUid, @Param("portalUid") String portalUid);
}
package org.faboo.ingressdb.repository;
import java.util.List;
import java.util.Map;
import org.faboo.ingressdb.domain.database.Portal;
import org.springframework.data.neo4j.annotation.Query;
import org.springframework.data.neo4j.repository.GraphRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
/**
* Created by bert
*/
@Repository
public interface PortalRepository extends GraphRepository<Portal> {
@Query("match (p:Portal) where p.location = {location} return p")
Portal findByLocation(@Param("location") String location);
@Query(value = "match (p:Portal) return p")
List<Portal> getAllPortals();
@Query(value = "match (p:Portal) where lower(p.portalName) =~{sub} return p "
+ " order by length(p.portalName), p.portalName limit {limit}")
List<Portal> findBySubString(@Param("sub") String sub, @Param("limit") Long limit);
@Query(value = "match (p:Portal {uid:{uid}}) <--(pa:PortalAttack) return count(pa)")
Long getAttackCount(@Param("uid") String uid);
@Query(value = "match (p:Portal {uid:{uid}})-[r {__type__:'AgentPortalRelation'}]-(pl) return count(r)")
Long getUsageCount(@Param("uid") String uid);
@Query(value = "call spatial.bbox('layerPortal', {smin}, {smax}) yield node as p " +
"match (a:Agent)-[r:USES]->(p:Portal) " +
" return p.uid as portalUID, p.portalName as portalName, p.imageURL as imageURL, p.portalAddress as portalAddress" +
", p.location as portalLocation" +
", a.uid as agentUID, a.currentLevel as agentLevel, a.agentName as agentName, a.fraction as agentFraction" +
", r.type as portalType")
List<Map<String, Object>> findHomeWorkWithinBox(@Param("smin") Map<String, Double> smin, @Param("smax") Map<String, Double> smax);
@Query(value = "call spatial.bbox('layerPortal', {smin}, {smax}) yield node as portal "
+ " optional match (portal)<-[:PORTAL]-(pa:PortalAttack)<-[:OWNER]-(a:Agent {uid:{uid}}) with portal, count(pa) as cpao "
+ " optional match (portal)<-[:PORTAL]-(pa:PortalAttack)<-[:ATTACKED_BY]-(a:Agent {uid:{uid}}) with portal, count(pa) as cpad, cpao "
+ " where cpad > 0 or cpao > 0 "
+ " return portal.uid as uid, portal.portalName as name, portal.imageURL as image, portal.portalAddress as address, portal.location as location ,cpao as owner, cpad as defender")
List<Map<String, Object>> findAgentPortalsWithinBox(@Param("smin") Map<String, Double> smin, @Param("smax") Map<String, Double> smax, @Param("uid") String agentUID);
@Query(value = "call spatial.bbox('layerPortal', {smin}, {smax}) yield node as portal "
+ " optional match (portal)<-[:PORTAL]-(pa:PortalAttack)<-[:OWNER]-(a:Agent {uid:{uid}}) with portal, count(pa) as cpao "
+ " optional match (portal)<-[:PORTAL]-(pa:PortalAttack)<-[:ATTACKED_BY]-(a:Agent {uid:{uid}}) with portal, count(pa) as cpad, cpao "
+ " return portal.uid as uid, portal.portalName as name, portal.imageURL as image, portal.portalAddress as address, portal.location as location ,cpao as owner, cpad as defender")
List<Map<String, Object>> findAllAndAgentPortalsWithinBox(@Param("smin") Map<String, Double> smin, @Param("smax") Map<String, Double> smax,
@Param("uid") String agentUID);
@Query(value = "call spatial.bbox('layerPortal', {smin}, {smax}) yield node as portal "
+ " return portal.uid as uid, portal.portalName as name, portal.imageURL as image, portal.portalAddress as address, portal.location as location ,0 as owner, 0 as defender")
List<Map<String, Object>> findAllPortalsWithinBox(@Param("smin") Map<String, Double> smin, @Param("smax") Map<String, Double> smax);
@Query(value = "match (p:Portal) where ID(p) = {portalId} with collect(p) as portals call spatial.addNodes('layerPortal', portals) yield node return node")
void addToSpatialIndex(@Param("portalId") Long portalId);
@Query("MATCH (p:Portal)<-[:PORTAL]-(pa:PortalAttack)<--(a:Agent) WHERE a.uid={uid} RETURN p ORDER BY pa.attackTime ASC")
List<Portal> findPortalsDefendedByAgent(@Param("uid") String agentUid);
}
package org.faboo.ingressdb.repository;
import org.faboo.ingressdb.domain.UserRole;
import org.faboo.ingressdb.domain.database.User;
import org.springframework.data.neo4j.annotation.Query;
import org.springframework.data.neo4j.repository.GraphRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
/**
* Created by bert
*/
@Repository
public interface UserRepository extends GraphRepository<User> {
@Query("match (u:User) where id(u) = {id} with u optional match (u)-[r:MAIN_ACC]-(a:Agent) return u, r, a")
User findOne(@Param("id") Long id);
@Query("match (u:User) where u.gplusID = {gplus} with u optional match (u)-[r:MAIN_ACC]-(a:Agent) return u, r, a")
User findByGplusID(@Param("gplus") String id);
@Query("match (u:User) where u.role = {role} return u")
List<User> findByRole(@Param("role") UserRole role);
@Query("match (u:User) where u.email = {email} with u optional match (u)-[r:MAIN_ACC]-(a:Agent) return u, r, a")
User findByEmail(@Param("email") String key);
@Query(value = "match (u:User) where u.email is not null and u.forwardedEmailsCount > 0 "
+ " return u.email as email, u.forwardedEmailsCount as count order by u.forwardedEmailsCount desc")
List<Map<String, Object>> getEmailCounts();
@Query(value = "match (u:User) where lower(u.gplusName) =~ {sub} return u order by u.gplusName limit {limit}")
List<User> findBySubName(@Param("sub") String sub, @Param("limit") Long limit);
@Query(value = "match (u:User) where not (u)-[:MAIN_ACC]-() and "
+ " lower(u.gplusName) =~ {sub} return u order by u.gplusName limit {limit}")
List<User> findBySubNameNoAgent(@Param("sub") String sub, @Param("limit") Long limit);
@Query(value = "match (u:User) return u")
List<User> getAllUsers();
@Query(value = "match (u:User) where toLower(u.email) = {email} " +
" set u.forwardedEmailsCount = u.forwardedEmailsCount + {emailCount}" +
", u.lastEmail = case when u.lastEmail > {lastEmail} then u.lastEmail else {lastEmail} end ")
void updateEmailCount(@Param("email") String email, @Param("emailCount") Long count, @Param("lastEmail") Long lastEmail);
@Query("match (u:User)<-[r:MODERATOR]-(m:User) where id(m) = {modID} return u")
List<User> findByModeratedBy(@Param("modID") Long moderatorID);
// emails of users with role UserRole.MEMBER UserRole.MODERATOR and UserRole.ADMIN
@Query("MATCH (u:User) where toLower(u.email) = {0} and u.role in ['MEMBER','MODERATOR','ADMIN'] RETURN count(u) > 0")
boolean isEMailAddressesAllowed(String emailAddressesToCheck);
// emails of users with role UserRole.BANNED
@Query("MATCH (u:User) where toLower(u.email) = {0} and u.role in ['BANNED'] RETURN count(u) > 0")
boolean isEMailAddressesBanned(String emailAddressesToCheck);
@Query("match (u:User)-[r:MAIN_ACC]-(a:Agent) where u.forwardedEmailsCount > 0 and u.processed = false return distinct u, r,a")
List<User> findContributingUsersWithAgent();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.