Querying a One to One relations using fql

Help
1

I have two collections which have the data in the following format

{
  "ref": Ref(Collection("Leads"), "267824207030650373"),
  "ts": 1591675917565000,
  "data": {
    "notes": "voicemail ",
    "source": "key-name",
    "name": "Glenn"
  }
}

{
  "ref": Ref(Collection("Sources"), "266777079541924357"),
  "ts": 1590677298970000,
  "data": {
    "key": "key-name",
    "value": "Google Ads"
  }
}

I want to be able to query the Leads collection and be able to retrieve the corresponding Sources document in a single query

I came up with the following query to try and use an index but I am stuck on how to retrieve the item from the Sources collection

Let(
      {
        data: Get(Ref(Collection('Leads'), '267824207030650373'))
      },
      {
        data: Select(['data'],Var('data')),
        source: q.Lambda('data',
              Match(Index('LeadSourceByKey'), Get(Select(['source'], Var('data') )) )
            )
      }
)

This is my Index for the Source

{
  name: "LeadSourceByKey",
  unique: true,
  serialized: true,
  source: "Sources",
  terms: [
    {
      field: ["data", "key"]
    }
  ],
  values: [
    {
      field: ["data", "key"]
    },
    {
      field: ["data", "value"]
    }
  ]
}

Do I need to organize my schema in a different manner to pull this or can I acheive this using a fql query

2

You need to keep references on both collections, that reference is what you will use to pull the other out.

I.e.:

leadsCollection:

{
  googleAdsKeyRef,
  ...etc
}

googleAdsKeysCollection

{
  leadRef,
  ...etc
}

So you can identify which googleAdsKey belong to which user by using the leadRef as the identifier and then which lead has which googleAdsKey by using the googleAdsKeyRef as the identifier.

3

Hi April, thanks for the reply on this. Could you tell me how a query would like if I were to use the googleAdsKeyRef to fetch the item from the keysCollection ?

4

You can definitely accomplish what you want via FQL:

Let(
  {
    lead_doc: Get(Ref(Collection("Leads"), "267824207030650373"))
  },
  {
  from_source_coll: Paginate(Match(
    Index("LeadSourceByKey"),
    Select(
      ["data", "source"],
      Var("lead_doc")
    )
  )),
  from_lead_coll: Var("lead_doc")
  }
)

However, you might choose to replace “key-name” with a reference so that you aren’t duplicating the data in both collections, unless you prefer to work with the key as a primary key instead of database references.

1 Like
5

Hey @aprilmintacpineda , thanks for the answer and my apologies for correcting you. That’s indeed a possibility but probably not what @maisnamraju is looking for since you can do that without storing the references.

@maisnamraju, your problem is the structure of the query. So we’ll go step by step in a Let. Each time we’ll return all values so we can see exactly how our query progresses when we add elements. To just get the Leads is simple, you got that right.

Let({
  lead: Get(Ref(Collection('Leads'), '269038063157510661'))
}, 
{
  lead: Var('lead')
}
)

We can get the ‘source key’ out of the lead (you had that part as well iirc)

Let({
  lead: Get(Ref(Collection('Leads'), '269038063157510661')),
  sourceKey: Select(['data', 'source'], Var('lead'))
}, 
{
  lead: Var('lead'),
  sourceKey: Var('sourceKey')
}
)

Approach 1 (more flexible, a bit more expensive) Index that Returns references + Map/Get

Then we can get all sourceReferences related to this sourceKey by using an index. Your index is slightly different from mine. I’ll write another example with your index later. Mine looks like:

{
  name: "LeadSourceByKey",
  unique: false,
  serialized: true,
  source: "Sources",
  terms: [
    {
      field: ["data", "key"]
    }
  ]
}

I do not specify values, in which case the reference of the source will be returned.

We can then get all references as follows:

Let(
  {
    lead: Get(Ref(Collection("Leads"), "269038063157510661")),
    sourceKey: Select(["data", "source"], Var("lead")),
    sourceReferences: Match(Index("LeadSourceByKey"), Var("sourceKey"))
    )
  },
  {
    lead: Var("lead"),
    sourceKey: Var("sourceKey"),
    sourceReferences: Var("sourceReferences")
  }
)

And from these references, you can get the actual source documents by using a Map + Get again.

Let(
  {
    lead: Get(Ref(Collection("Leads"), "269038063157510661")),
    sourceKey: Select(["data", "source"], Var("lead")),
    sourceReferences: Match(Index("LeadSourceByKey"), Var("sourceKey")),
    sources: Map(
      Paginate(Var("sourceReferences")),
      Lambda(["ref"], Get(Var("ref")))
    )
  },
  {
    lead: Var("lead"),
    sourceKey: Var("sourceKey"),
    sourceReferences: Var("sourceReferences"),
    sources: Var("sources")
  }

Approach 2 (yours) Index that Returns values

You made an index that returns some values which could be an approach (put all values in the index). In that case, since you did not return the reference we can’t Get the document. However, it’s cheaper since it doesn’t do a get for each source document (you can optimise like this, the tradeoff is that it’s less flexible, if your documents change, your index has to be updated)
Imagine we use your index:

{
  name: "LeadSourceValuesByKey",
  unique: false,
  serialized: true,
  source: "Sources",
  terms: [
    {
      field: ["data", "key"]
    }
  ],
  values: [
    {
      field: ["data", "key"]
    },
    {
      field: ["data", "value"]
    }
  ]
}

Then we specified that this index returns [key, value] instead of only the reference. So we can write it differently:

Let(
  {
    lead: Get(Ref(Collection("Leads"), "269038063157510661")),
    sourceKey: Select(["data", "source"], Var("lead")),
    sourceValues: Paginate(Match(Index("LeadSourceValuesByKey"), Var("sourceKey")))
  },
  {
    lead: Var("lead"),
    sourceKey: Var("sourceKey"),
    sourceValues: Var("sourceValues")
  }
)

Would return:

{
  lead: {
    ref: Ref(Collection("Leads"), "269038063157510661"),
    ts: 1592833540970000,
    data: {
      notes: "voicemail ",
      source: "key-name",
      name: "Glenn"
    }
  },
  sourceKey: "key-name",
  sourceValues: {
    data: [["key-name", "Google Ads"]]
  }
}

Note that the sourceValues is an array since the values of an index are always an array. Since Paginate returns an array as well, you now have an array of arrays. (you might have multiple source values that were returned from the index). the result is:

4 Likes
6

Thanks guys for the help, this really clears a lot of air reqarding fql queries. I liked the approach with the ref and am using that approach now.

7

I stumbled upon this too when I started using FQL.

Coming from SQL I was storing ids instead of refs :slight_smile:

1 Like