Getting data from queryparser-CodePudding

I am attempting to use queryparser to extract table relationships from an SQL query. I can get most of what I need, I'm just having issues unpacking the lists.

library(queryparser)

file <- "select 
  p.name,
  p.age,
  p.hometown,
  c.state,
  c.country,
  n.capitol,
  n.leader
 from person p
   inner join city c
     on  p.hometown = c.name
   inner join nation n
     on c.county = n.name
 where C.country != 'Antarctica' "

query <- parse_query(file, tidyverse = TRUE)
query$from

yields the following lists:

> query$from
$p
person

$c
city

$n
nation

attr(,"join_types")
[1] "inner join" "inner join"
attr(,"join_conditions")
attr(,"join_conditions")[[1]]
p.hometown == c.name

attr(,"join_conditions")[[2]]
c.county == n.name

I would like to have a datafame that has each table name and it's alias, and a second table with the join criteria. What is the easiest way to do this dynamically so that I don't have to adjust code between scanning different scripts?

CodePudding user response：

Convert to character and then use stack. For the info in the attributes remove the names and simplify giving the character matrix shown.

stack(sapply(query$from, as.character))
##   values ind
## 1 person   p
## 2   city   c
## 3 nation   n

simplify2array(attributes(query$from)[-1])
##      join_types   join_conditions       
## [1,] "inner join" "p.hometown == c.name"
## [2,] "inner join" "c.county == n.name"