Query data in a graph with a parameter

  void 
  
 QueryDataWithParameter 
 ( 
 google 
 :: 
 cloud 
 :: 
 spanner 
 :: 
 Client 
  
 client 
 ) 
  
 { 
  
 namespace 
  
 spanner 
  
 = 
  
 :: 
 google 
 :: 
 cloud 
 :: 
 spanner 
 ; 
  
 spanner 
 :: 
 SqlStatement 
  
 select 
 ( 
 R 
 " 
 ""( 
 Graph FinGraph 
 MATCH (a:Person)-[o:Owns]->()-[t:Transfers]->()<-[p:Owns]-(b:Person) 
 WHERE t.amount >= @min 
 RETURN a.name AS sender, 
 b.name AS receiver, 
 t.amount, 
 t.create_time AS transfer_at 
 )"" 
 " 
 , 
  
{{"min", spanner::Value(500)} }); 
  
 using 
  
 RowType 
  
 = 
  
 std 
 :: 
 tuple<std 
 :: 
 string 
 , 
  
 std 
 :: 
 string 
 , 
  
 double 
 , 
  
 spanner 
 :: 
 Timestamp 
> ; 
  
 auto 
  
 rows 
  
 = 
  
 client 
 . 
 ExecuteQuery 
 ( 
 std 
 :: 
 move 
 ( 
 select 
 )); 
  
 for 
  
 ( 
 auto 
&  
 row 
  
 : 
  
 spanner 
 :: 
 StreamOf<RowType> 
 ( 
 rows 
 )) 
  
 { 
  
 if 
  
 ( 
 ! 
 row 
 ) 
  
 throw 
  
 std 
 :: 
 move 
 ( 
 row 
 ). 
 status 
 (); 
  
 std 
 :: 
 cout 
 << 
 "sender: " 
 << 
 std 
 :: 
 get<0> 
 ( 
 * 
 row 
 ) 
 << 
 " 
 \t 
 " 
 ; 
  
 std 
 :: 
 cout 
 << 
 "receiver: " 
 << 
 std 
 :: 
 get<1> 
 ( 
 * 
 row 
 ) 
 << 
 " 
 \t 
 " 
 ; 
  
 std 
 :: 
 cout 
 << 
 "amount: " 
 << 
 std 
 :: 
 get<2> 
 ( 
 * 
 row 
 ) 
 << 
 " 
 \t 
 " 
 ; 
  
 std 
 :: 
 cout 
 << 
 "transfer_at: " 
 << 
 std 
 :: 
 get<3> 
 ( 
 * 
 row 
 ) 
 << 
 " 
 \n 
 " 
 ; 
  
 } 
  
 std 
 :: 
 cout 
 << 
 "Query completed for [spanner_query_graph_data_with_parameter] 
 \n 
 " 
 ; 
 } 
 

  import 
  
 ( 
  
 "context" 
  
 "fmt" 
  
 "io" 
  
 "time" 
  
 "cloud.google.com/go/spanner" 
  
 "google.golang.org/api/iterator" 
 ) 
 func 
  
 queryGraphDataWithParameter 
 ( 
 w 
  
 io 
 . 
 Writer 
 , 
  
 db 
  
 string 
 ) 
  
 error 
  
 { 
  
 ctx 
  
 := 
  
 context 
 . 
 Background 
 () 
  
 client 
 , 
  
 err 
  
 := 
  
 spanner 
 . 
 NewClient 
 ( 
 ctx 
 , 
  
 db 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 defer 
  
 client 
 . 
 Close 
 () 
  
 // Execute a Spanner query statement comprising a graph query. Graph queries 
  
 // are characterized by 'MATCH' statements describing node and edge 
  
 // patterns. 
  
 // 
  
 // This statement finds entities ('Account's) owned by all 'Person's 'b' to 
  
 // which transfers greater than a specified 'min' have been made by entities 
  
 // ('Account's) owned by any 'Person' 'a' in the graph called 'FinGraph'. 
  
 // It then returns the names of all such 'Person's 'a' and 'b', and the 
  
 // amount and time of the transfer. 
  
 // The parameter 'min' is distinguished by a leading '@', and the value 
  
 // of the parameter is specified using a map. 
  
 stmt 
  
 := 
  
 spanner 
 . 
  Statement 
 
 { 
  
 SQL 
 : 
  
 `Graph FinGraph 
 MATCH (a:Person)-[o:Owns]->()-[t:Transfers]->()<-[p:Owns]-(b:Person) 
 WHERE t.amount >= @min 
 RETURN a.name AS sender, b.name AS receiver, t.amount, t.create_time AS transfer_at` 
 , 
  
 Params 
 : 
  
 map 
 [ 
 string 
 ] 
 interface 
 {}{ 
  
 "min" 
 : 
  
 500 
 , 
  
 }, 
  
 } 
  
 iter 
  
 := 
  
 client 
 . 
  Single 
 
 (). 
 Query 
 ( 
 ctx 
 , 
  
 stmt 
 ) 
  
 defer 
  
 iter 
 . 
 Stop 
 () 
  
 // The results are returned in tabular form. Iterate over the 
  
 // result rows and print them. 
  
 for 
  
 { 
  
 row 
 , 
  
 err 
  
 := 
  
 iter 
 . 
 Next 
 () 
  
 if 
  
 err 
  
 == 
  
 iterator 
 . 
 Done 
  
 { 
  
 return 
  
 nil 
  
 } 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 var 
  
 sender 
 , 
  
 receiver 
  
 string 
  
 var 
  
 amount 
  
 float64 
  
 var 
  
 transfer_at 
  
 time 
 . 
 Time 
  
 if 
  
 err 
  
 := 
  
 row 
 . 
  Columns 
 
 ( 
& sender 
 , 
  
& receiver 
 , 
  
& amount 
 , 
  
& transfer_at 
 ); 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 fmt 
 . 
 Fprintf 
 ( 
 w 
 , 
  
 "%s %s %f %s\n" 
 , 
  
 sender 
 , 
  
 receiver 
 , 
  
 amount 
 , 
  
 transfer_at 
 . 
 Format 
 ( 
 time 
 . 
 RFC3339 
 )) 
  
 } 
 } 
 

  static 
  
 void 
  
 queryWithParameter 
 ( 
 DatabaseClient 
  
 dbClient 
 ) 
  
 { 
  
 Statement 
  
 statement 
  
 = 
  
 Statement 
 . 
 newBuilder 
 ( 
  
 "Graph FinGraph MATCH" 
  
 + 
  
 " (a:Person)-[o:Owns]->()-[t:Transfers]->()<-[p:Owns]-(b:Person) WHERE" 
  
 + 
  
 " t.amount >= @min RETURN a.name AS sender, b.name AS receiver, t.amount," 
  
 + 
  
 " t.create_time AS transfer_at" 
 ) 
  
 . 
 bind 
 ( 
 "min" 
 ) 
  
 . 
 to 
 ( 
 500 
 ) 
  
 . 
 build 
 (); 
  
 try 
  
 ( 
 ResultSet 
  
 resultSet 
  
 = 
  
 dbClient 
 . 
 singleUse 
 (). 
 executeQuery 
 ( 
 statement 
 )) 
  
 { 
  
 while 
  
 ( 
 resultSet 
 . 
 next 
 ()) 
  
 { 
  
 System 
 . 
 out 
 . 
 printf 
 ( 
  
 "%s %s %f %s\n" 
 , 
  
 resultSet 
 . 
 getString 
 ( 
 "sender" 
 ), 
  
 resultSet 
 . 
 getString 
 ( 
 "receiver" 
 ), 
  
 resultSet 
 . 
 getDouble 
 ( 
 "amount" 
 ), 
  
 resultSet 
 . 
 getTimestamp 
 ( 
 "transfer_at" 
 )); 
  
 } 
  
 } 
 } 
 

  def 
  
 query_data_with_parameter 
 ( 
 instance_id 
 , 
 database_id 
 ): 
  
 """Queries sample data from the database using SQL with a parameter.""" 
 spanner_client 
 = 
 spanner 
 . 
 Client 
 () 
 instance 
 = 
 spanner_client 
 . 
 instance 
 ( 
 instance_id 
 ) 
 database 
 = 
 instance 
 . 
 database 
 ( 
 database_id 
 ) 
 with 
 database 
 . 
 snapshot 
 () 
 as 
 snapshot 
 : 
 results 
 = 
 snapshot 
 . 
 execute_sql 
 ( 
  
 """Graph FinGraph 
 MATCH (a:Person)-[o:Owns]->()-[t:Transfers]->()<-[p:Owns]-(b:Person) 
 WHERE t.amount >= @min 
 RETURN a.name AS sender, b.name AS receiver, t.amount, t.create_time AS transfer_at""" 
 , 
 params 
 = 
 { 
 "min" 
 : 
 500 
 }, 
 param_types 
 = 
 { 
 "min" 
 : 
 spanner 
 . 
 param_types 
 . 
 INT64 
 }, 
 ) 
 for 
 row 
 in 
 results 
 : 
 print 
 ( 
 "sender: 
 {} 
 , receiver: 
 {} 
 , amount: 
 {} 
 , transfer_at: 
 {} 
 " 
 . 
 format 
 ( 
 * 
 row 
 ))