2

Can any one help me with the following question? In the Gosu code below, how can I include the city information in Address table in the result from the query (without turning the tables around - starting from Address table)? Address table has a foreign key point to user table, but no foreign key the other way. Can row query do that? Thanks a lot

uses gw.api.database.Query

// -- query the User entity --
var queryUser = Query.make(User)

// -- select only User instances who last updated addresses in the city of Chicago --
var tableAddress = queryUser.join(Address, "UpdateUser")
tableAddress.compare("City", Equals, "Chicago") 

// -- fetch the User instances with a for loop and print them --
var result = queryUser.select()

for (user in result) {
    print (user.DisplayName)
}
kev
  • 21
  • 2

1 Answers1

0

Since Address has the user FK why not to turn the tables?

uses gw.api.database.Query

// -- query the Address entity --
var queryAddress = Query.make(Address)
queryAddress.compare("City", Equals, "Chicago") 

// -- select only User instances who last updated addresses in the city of Chicago --
var tableUser = queryAddress.join(Address#UpdateUser)

// -- fetch the Addresses instances with a for loop and print them --
var result = tableUser.select()

for (address in result) {
    print(address.City?.DisplayName)
    print(address.UpdateUser?.DisplayName)
}
hakamairi
  • 4,464
  • 4
  • 30
  • 53
  • Thanks hakamairi for the answer. But what I really would like to know is that: does gosu (Guidewire) provide a way to accessing Properties of the Secondary Entity when the foreign key is only available on the right side. I would edit the original questions to make it more clear. – kev Feb 12 '19 at 22:40
  • @kev, I don't think it maps well back to objects. Since you User knows nothing about UpdateUser on Address there's no way to access those. Also the reverse join is used when you have an Array on the other type - so it would be accessible if you would add an array of Addresses on the User. Other way around this is just to use JDBC Resultset directly, there you can get all the columns you need. – hakamairi Feb 13 '19 at 09:41
  • Thanks @hakamairi,Based on the statements in the documentation quoted in the comment below, it looks like the "Row Queries" is the way to go, but I just can not figure out how. Maybe the documentation itself is not so accurate. By the way, could you give me a sample in Gosu to use JDBC Resultset directly to get all the columns as you suggested? Thanks – kev Feb 13 '19 at 21:42
  • Here @hakamairi, is the statements from documentation in term of the sample code in original question: "In the preceding Gosu example query, you cannot determine which addresses specific users in the result last updated. In some cases this is not a problem. Often, you use the results of inner queries with foreign keys on the right for further processing without accessing any information from the related instances that led to the result. If you do want to access the information, for example to display on a screen or in a report, see “Working with Row Queries”. " – kev Feb 13 '19 at 21:43
  • Since the select({QuerySelectColumns..} does start with your base entity, it's still impossible to be done if you start from User. – hakamairi Feb 14 '19 at 08:45