'Is it possible to UNION select from 2 tables?

Is it possible to do something like a sql UNION select with pact or select a common row from 2 separate tables returning a result?

For example we have 2 tables, and we want to select row ("Bob") from both tables (ages, favorite-food), and return a single object composed of data (age, food) from both tables .

;Table 1 schema
(defschema table-schema
    age:decimal)

;Table 1
(deftable ages:{supply})

;Table 2 schema
(defschema table-schema
    food:string)

;Table 2
(deftable favorite-food:{supply})

In such a case, a single function which would return an object containing Bob's age and his favorite food.

Is such a query possible in pact that serves this purpose?

pact-lang


Solution 1:[1]

Thank you kitty_kad I originally did mean to ask about a use case scenario which takes a list as input. Here is what I came up with just in case anyone else needs a use case with a list instead of just a single key:

(defschema user-schema
    account:string
    petId:string
    balance:decimal
)
 
(deftable users:{user-schema})

(defschema pets-schema
    petId:string
    owner:string
    petType:string
)
 
(deftable pets:{pets-schema})

(defun get-user-pet-details
  ( account:string )
  (let ((x (select users['petId]
      (and? (where 'account (= account))
        (where 'balance (< 0.0))))))
        (map (get-pet-details) x))

)

(defun get-pet-details
  ( x:object{pets-schema} )

  (bind x { "petId" := id }

  (with-read pets id
    { "petId" := pid
    , "owner" := powner
    , "petType" := ptype
    }
    {
     "ID": pid
    , "OWNER": powner
    , "TYPE": ptype
    } )
  )
)

Solution 2:[2]

You can use let and read together

(let (
       (age  (at "age" (read ages "Bob" ["age"])) )
       (food (at "food" (read favourite-food "Bob" ["food"])) )
     )
    {"age":age, "food" food}
)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 squiegee
Solution 2 Kitty Kad

Related Questions