Render and filter query

Low-code
#1

I want to render people you may know, so I want to filter friend object and get only those who are not linked with myFriend field, and friend Request field.

#2

There’s currently no good way to express such a query on the platform.

I will tag this as a feature request.

#3

We’re considering this with an ArcQL extension to add an IN clause. So given:

type User {
  age: Int
  name: String
  friendRequests: [User!]
  friends: [User!]
  following: [User!]
}

And you want to filter the friends field to exclude those in the friendRequest and following fields you could write this ArcQL:

query {
    find(type: User, arcql: "*"){
        friends(arcql:"hypi.id NOT(hypi.id IN FIELD friendRequests.hypi.id WHERE name = 'Courtney' OR hypi.id IN FIELD following.hypi.id WHERE age > 17)"){
            ...
        }     
    }
}

There would be two new syntax additions, the NOT group and the IN <FIELD|TYPE> expression.

  1. <fieldExpr> NOT(<expr>) - this addition would exclude any results returned by <expr>
  2. <fieldExpr> IN <FIELD <fieldName>|TYPE <Typename> WHERE <expr> - lets you filter by an arbitrary sub-query. If FIELD follows the IN keyword then it filters rows in the current type. Otherwise, the filter is applied to the type given by TYPE. The WHERE clause will be used to filter on the field or type.

For example :

type  AppInstance {
    domain: String
    release: AppRelease
}

type App {
    name: String
    releases: [AppRelease!]
}

type AppRelease {
    name: String
}

In this model, I want to find AppInstance objects by app name. The only relationship between App and AppInstance is that both AppInstance and App has a field of type AppRelease. With the proposed extensions we can find AppInstances by App name using the following query:

query {
    find(type: AppInstance, arcql: "release.hypi.id IN TYPE App.release.hypi.id WHERE name = 'app1' AND releases.hypi.id = this.hypi.id)"){
        ...
    }
}

What do you think of this proposal deepakKashyap?

#4

This would be similar to SQL IN operator and would it be translate to the following?
ARCQL
find(type: AppInstance, arcql: "release.hypi.id IN TYPE App.release.hypi.id WHERE name = 'app1')
SQL
Select * FROM AppInstance WHERE release.hypi.id IN App.release.hypi.id = "this.hypi.id"

#5

Roughly yes but the actually SQL would be a little more complex.
Every . in an ArcQL query leads to a JOIN on the type being implicitly referenced. I imagine the SQL
For the query

release.hypi.id IN TYPE App.release.hypi.id WHERE name = ‘app1’ AND releases.hypi.id = this.hypi.id)

it’ll generate will be similar to:

SELECT field1,...fieldN FROM AppInstance i
INNER JOIN AppRelease r ON i.hypi.id = r.hypi.id ...
WHERE r.hypi.id IN (  
    SELECT r2.hypi.id FROM App a
    INNER JOIN AppRelease r2 ON r2.hypi.id = a.hypi.id AND r2.hypi.id = r.hypi.id
    WHERE a.name = 'app1' 
)

That’s more what I expect this ArcQL query will have to generate (the syntax isn’t 100% valid SQL, just demonstrating the main form of the query).

#6

Okay, that makes sense!

#7

Both syntax are now possible. The two are shown and explained a bit more below

release.hypi.id TYPE IN App:release.hypi.id WHERE name = ‘app1’ AND releases.hypi.id = this.hypi.id

hypi.id FIELD IN friendRequests.hypi.id WHERE name = ‘Courtney’ OR hypi.id FIELD IN following.hypi.id WHERE age > 17

Both are now possible queries. Given these types in your schema:

type User {
  user: Account
  followedBy: [User!]
  friendRequest: [User!]
  friends: [User!]
}

type Post {
  createdBy: User
  title: String!
}

Two possible queries that couldn’t be expressed before are:

  1. What are the posts from my friends.

createdBy.hypi.id TYPE IN User:friends.hypi.id WHERE hypi.id = ‘’

  1. Who are people I may know? Essentially, Who are my friends and who are their friends that are not my friends

hypi.id != ‘’ AND friends.hypi.id FIELD IN friends.hypi.id WHERE hypi.id = ’

This query says

  1. “find the ids of my friends” (friends.hypi.id WHERE hypi.id = '’)
  2. “find the ids of any user who are friends with those friends of mine” (friends.hypi.id FIELD IN)
  3. “Return all matching users, except my user” (hypi.id != ‘’)

What are these doing? Both forms of queries
<fieldExr> TYPE IN and <fieldExpr> FIELD IN are translated to SQL sub-queries with the IN clause.

Notice, the fields that are selected are all ID fields. It MUST be a scalar field (doesn’t have to be ID but must be scalar).
Two things happen in the queries above, a JOINs friends.hypi.id does a self-JOIN on the User table and then <FIELD|TYPE> IN... does a SQL IN on the results of the query on the right hand side of the FIELD IN.

The WHERE clause can be arbitrarily complex supporting all possibilities in ArcQL.

To better exemplify, the what are the posts from my friends query above roughly translates to this SQL (unnecessary parts omitted):

SELECT 
  h0.`title` AS h0_t00, 
  h0.`hypi_id` AS h0_h00, 
  h0.`hypi_created` AS h0_h01 
FROM 
  Post h0 
  INNER JOIN User h2 ON h0.`createdBy.id` = h2.`hypi.id` 
WHERE 
  h2.`hypi.id` IN (
    SELECT 
      h3.`hypi.id` AS h3_h21 
    FROM 
      User h1 
      INNER JOIN Junctions ON ...
      INNER JOIN User h3 ON j0.`toId` = h3.`hypi.id` 
    WHERE 
      h1.`hypi.id` = ? 
      AND j0.<...>...
    ORDER BY 
      h1.hypi_id 
  )

The who people you may know query is roughly:

SELECT 
  h0.`hypi_id` AS h0_h00, 
  h0.`hypi_created` AS h0_h01, 
  h1.`username` AS h1_u00, 
  h1.`hypi_id` AS h1_h00, 
  h1.`hypi_created` AS h1_h01 
FROM 
  User h0 
  LEFT JOIN Account h1 ON h0.`user_id` = h1.`hypi.id` 
WHERE 
  (
    h0.`hypi.id` != ? 
    AND h0.`hypi.id` IN (
      SELECT 
        h2.`hypi.id` AS h2_h12 
      FROM 
        User h0 
        INNER JOIN Junctions j0 ON h0.`hypi.id` = j0.`fromId` 
        INNER JOIN User h2 ON j0.`toId` = h2.`hypi.id` 
      WHERE 
        h0.`hypi.id` = ? 
        AND j0.<...>...
    )
  )