Skip to content

Instantly share code, notes, and snippets.

@taseroth
Created December 11, 2018 20:14
Show Gist options
  • Save taseroth/f4fccb306a275a4bf04907888305df9e to your computer and use it in GitHub Desktop.
Save taseroth/f4fccb306a275a4bf04907888305df9e to your computer and use it in GitHub Desktop.
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