Filters, ranges, sort-by, sort-by-order in products

Help
, ,
1

These are all trivial in SQL but doing these in FQL have become a challenge for me, I’ve spent days just staring at code and getting nowhere other than solving basic queries.

Maybe someone can help explain how to accomplish this and break it down into parts to explain what actually happens to the data and how it relates to the results?

What I’m trying to accomplish:

  1. Find all products with specific colours (One or multiple)
  2. Find all products with specific size and colours (One or multiple)
  3. Find all products with specific price or price range
  4. List all reviews for a specific product
  5. List all customer reviews for a specific product
  6. Filter on specifications (Products with specific specs)
  7. Search in specification (in combination with filter)
  8. Sort by: price, name, creation_date, weighting

This is a sample schema and sample data:

type Products {
  sku: String! @unique
  name: String! @unique
  price: Float
  colours: [Colours]! @relation
  reviews: [Reviews] @relation
  specifications: [Specifications] @relation
  creation_date: String
}

type Colours {
  name: String
  products: [Products!] @relation
}

type Reviews {
  published: Boolean
  author: String
  products: [Products!] @relation
  content: String
}

type Specifications {
  type: String
  description: String
  image_path: String
  products: [Products!] @relation
}

Solutions

8 - Sorting

By name/price/date/weighting (Replace placeholders with field-name):

/* Create the index */
CreateIndex({
  name: "products_sort_by_fieldname_asc",
  source: Collection("Products"),
  values: [
    { field: ["data", "fieldname"] },
    { field: ["ref"] }
  ]
})
/* Query */
Paginate(Match(Index("products_sort_by_fieldname_asc")))

For reverse order, add reverse: true to the index field, like so:
{ field: ["data", "fieldname"], reverse: true }

8. Bonus > range search

/* Create index */
CreateIndex({
  name: "products_by_date",
  source: Collection("Products"),
  values: [{field: ["data", "creation_date"]}, {field: ["ref"]}]
})
/* Query */
Map(
  Paginate(
    Range(
      Match(Index("products_by_date")), 
      Date("2020-06-01"), 
      Date("2020-06-15")
    ),
  ), 
  Lambda((ts, ref) => Get(ref))
)
3 Likes
2

Have you found the solutions yourself?

3

No, I found a solution to #8 and then gave up and went back to SQL where these issues don’t exist.

I’ll return when there’s solutions to the problems that don’t take days to find or weeks to learn.

1 Like
4

@ThomasD I’m not an expert at all, but I guess better me than no-one. It would also be great if you could specify where you got, where you’re stuck etc.

Anyway, I don’t think getting “one or multiple” with an index is something fauna supports. What I’d think about doing is something like this from the Union docs:

client.query(
  q.Paginate(
    q.Union(
      q.Match(q.Index('products_by_color'), 'red'),
      q.Match(q.Index('products_by_color'), 'blue'),
    )
  )
)
.then((ret) => console.log(ret))

For 3. to get a price range I would think about using the Range function.

client.query(
  q.Paginate(
    q.Range(q.Match(q.Index('products')), 10.00, 20.00)
  )
)

You’d probably need and index with values set to the price field to have it sorted.

I don’t get the difference between 4 and 5 (maybe you meant customer's reviews?), but you’d need to have a reference to the User who created the review, then you could index the Reviews by the User, or Product.

I think 6 is just getting lots of indexes for everything, and then doing Intersection.

For number 7, my understanding is you want to search titles and descriptions, i.e. case insenstive, substrings, fuzzy matching etc. I don’t think it’s a good idea to do fuzzy searching by using a database. I’d love to know how to do this either in FaunaDB or something like PostgreSQL, but what I would do is probably research Algolia, or run your own search engine like Tantivy or Apache’s Lucene, and run it on a server. Other option is to make a binding that will lowercase the title of the product, and you would lowercase the user search, making the search case insensitive. That way you could make an index, but it would only match whole titles. Or you could just on each request go through each product and do a Contains call. Probably not a good idea. You could also save the titles in RAM, like Redis or Cloudflare Workers KV and search through that pretty cheaply.

I probably said a lot of basic stuff for you, but that could happen if you don’t say how far you got by yourself. Sorry if this didn’t help.

EDIT:
Retraction: Linear time (searching through lots of keys in Workers KV) would probably not work because of the 10/50ms limit.

2 Likes
5

I’ll break these down section by section when I have more time, probably in September.

CF Workers, i can do a prefix search, but that type of search is better suited for full-text search engines, so I’ll use that for that point.

Thanks for giving it a go though, I really appreciate it!

Sidenote: I actually tried full-text-search inside a worker and it can handle about 400kb of (indexed) text within the CPU-limit :wink:

1 Like
6

That’s awesome. Are you refering to the paid or free plan though?

7

Definitely paid plan (50ms), free plan is too limited.

1 Like
8

@ThomasD this is off-topic and quite possibly against the ToS of Cloudflare but since I have someone who’s using Workers, my mind keeps coming back to cheating the time limit a bit. Do you think it would be possible to detect, e.g. when you hit 49ms and then call the worker again from inside the worker to keep going where the first one stopped, recursively…

9

I’ve already setup worker to initiate worker, you can do that by using different domains. This way you can split up a workload an run it async on multiple worker at the same time. It’s really powerful, but just beware that it’s easy to create a DDOS using this. With great power… :wink:

You can ask stuff on the CF worker forum, people are quick to answer and if it’s too advanced, I usually answer (thomas4).

1 Like