Implement `.find()` on `Entity`

[ra0x3]

Context

• This is one of several pieces of work that will be a part of https://github.com/FuelLabs/fuel-indexer/issues/1417
• The use case is as a user, I want to find a single instance of an Entity
• Looking at how Prisma does this, I think they have something we can replicate with minimal complexity
  • https://prisma.brendonovich.dev/reading-data/find

What we currently have

• We currently impl Entity in our fuel_indexer_macros::decoder module as follows:

#[derive(Debug, PartialEq, Eq, Hash, Serialize, Deserialize)]
pub struct #ident {
    #struct_fields
}

impl<'a> Entity<'a> for #ident {
    const TYPE_ID: i64 = #type_id;
    const JOIN_METADATA: Option<[Option<JoinMetadata<'a>>; MAX_FOREIGN_KEY_LIST_FIELDS]> = #join_metadata;

    fn from_row(mut vec: Vec<FtColumn>) -> Self {
        #field_extractors
        Self {
            #from_row
        }
    }

    fn to_row(&self) -> Vec<FtColumn> {
        vec![
            #to_row
        ]
    }

}

What we want

• We would update this ☝🏼 implementation to the following

#[derive(Debug, PartialEq, Eq, Hash, Serialize, Deserialize)]
pub struct #ident {
    #struct_fields
}

impl<'a> Entity<'a> for #ident {
    const TYPE_ID: i64 = #type_id;
    const JOIN_METADATA: Option<[Option<JoinMetadata<'a>>; MAX_FOREIGN_KEY_LIST_FIELDS]> = #join_metadata;

    #const_fields

    fn from_row(mut vec: Vec<FtColumn>) -> Self {
        #field_extractors
        Self {
            #from_row
        }
    }

    fn to_row(&self) -> Vec<FtColumn> {
        vec![
            #to_row
        ]
    }

}

• Explanation:
  • #const_fields would be a series of tokens for each field on the Entity as a const

So with the following schema

type Foo @entity {
  id: ID!
  account: Address!
  name: String!
}

You'd get the following const fields on the Entity

trait SQLFragment {
  fn equals<T: Sized>(val: T) -> String;
}

struct FooId;

impl FooId {
  const name: &str = "id";
}

impl SQLFragment for FooId {
  fn equals<T: Sized>(val: T) -> String {
      format!("{} = '{}'", Self::name, val);
  }
}

struct FooAccount;

impl FooAccount {
  const name: &str = "account";
}

struct FooName;

impl FooName {
  const name: &str = "name";
}

impl<'a> Entity<'a> for Foo {
   const id: String = FooId;
   const account: Address =  FooAddress;
   const name: &str = FooName;

   // .. all the other code ...
}

This ☝🏼 would allow us to build a .find() method as follows:

impl<'a> Entity<'a> for Foo {

   // .. all the other code ...

   fn find(fragments: Vec<impl SQLFragment>) -> Option<Self> {
      let where_clause = fragments.join(" AND ");
      let query = format!("SELECT * FROM {} WHERE {} LIMIT 1", self.name, where_clause);
      let buff = bincode::serialize(&query);
      let mut bufflen = (buff.len() as u32).to_le_bytes();
      let ptr = ff_arbitrary_single_select(Self::TYPE_ID, buff.as_ptr(), bufflen.as_mut_ptr());

       match ptr {
            Some(p) => {
                   let len = u32::from_le_bytes(bufflen) as usize;
                   let bytes = Vec::from_raw_parts(ptr, len, len).unwrap();
                   let data = deserialize(&bytes).unwrap();
                   Some(Self::from_row(data));
            }
            None => None,
       }
   }
}

• The idea is that Entity::field::equals(value) returns String fragments that can be aggregated into an arbitrary single SELECT query and easily passed to the DB via the FFI
• So the end result in your indexer handler would look like:

extern crate alloc;
use fuel_indexer_utils::prelude::*;

mod indexer_mod {
    fn handle_burn_receipt(order: OrderEntity) {
        // single find parameter
        let found_order = OrderEntity.find(vec![OrderEntity::amount::equals(5)])?; 

        // multiple find parameters
        let found_order = OrderEntity.find(vec![OrderEntity::amount::equals(5), OrderEntity::address::equals("0x00001")])?; 
    }
}

Future work

• This would also allow us to update the SQLFragment trait to support other things such as

trait SQLFragment {
  fn equals<T: Sized>(val: T) -> String;
  fn gt<T: Sized>(val: T) -> String;
  fn lt<T: Sized>(val: T) -> String;
  // .. so on and so forth ..
}

[ra0x3]

This ☝🏼 is all pseudo-code, but CC @Voxelot @deekerno @lostman for comment, as this (implementing .find()) is gonna block #886 because we need to be able to lookup the CoinOutput (for a given InputCoin) by the owner: Address field (which we currently don't support)

[lostman]

@ra0x3,

We would have to generate these functions ::field_name::equals() for all types in the schema, correct?

vec![OrderEntity::amount::equals(5), OrderEntity::address::equals("0x00001")]

And they would produce SQLFragments directly?

[ra0x3]

@lostman

• Every field on every Object and Union with @entity would get one of these field structs (e.g., FooId) and each of these field structs would supports SQLFragment for operations such as eq, gt, gte, lt, etc.

[deekerno]

I have to say that I'm not the greatest fan of using a vector for multiple parameters. I'd much rather a cleaner "object-based" syntax similar to how selection parameters are done in Prisma. However, I don't think that we can easily do that right now given the fact that Rust doesn't allow for anonymous structs; additionally, we'd want type safety wherever possible so doing something with JSON would probably not be worth the headache. In any case, I think this style is probably the best type-safe way and will work for now in order to not block the predicate support work.

Perhaps in the future, we could leverage the builder pattern to do something in a more functional style:

let found_order = OrderEntity
  .find()
  .filter(OrderEntity::amount::gt(5))
  .filter(OrderEntity::address::equals("0x00001")
  .select()?

...or something to that effect.

[ra0x3]

@deekerno

• Big +1 from me on avoiding that vec syntax if we can.
• just copied it cause primsa did it.
• Maybe we can start at the ideal case and work our way back?
• I like the builder pattern-ish idea as that is what most ORMs do
• Ideally would like to keep the methods on the actual Entity limited to the basics (e.g., find in this case)
  • So that means any additional builder logic would have to happen within the fields themselves

let found_order = OrderEntity.find(OrderEntity::amount::gt(5).and(OrderEntity::address::eq("0x00001"));

• This ☝🏼 would mean that the structs for each const field (e.g., FooId) would have to themselves implement the builder-ish pattern for things like eq, gt, etc.

@deekerno @lostman thoughts? ☝🏼

[deekerno]

I like that idea even better and we should aim for that if possible. The only hesitation I have is how we would support operator combination, if at all.

From the Prisma docs:

const result = await prisma.user.findMany({
  where: {
    OR: [
      {
        email: {
          endsWith: 'prisma.io',
        },
      },
      { email: { endsWith: 'gmail.com' } },
    ],
    NOT: {
      email: {
        endsWith: 'hotmail.com',
      },
    },
  },
  select: {
    email: true,
  },
})

I'd imagine that the vector style would have to make a reappearance here, but we can cross the proverbial bridge when we get to it.

[ra0x3]

@deekerno The above ☝🏼 would be written as

User.find(
  User::email::ends_with("prisma.io")
  .or(User::email::ends_with("gmail.com")
  .and(User::email::not_ends_with("hotmail.com")
);

• Obviously we'd have to add as many of these operators (e.g., not_ends_with, or ends_with) as we want.
• As far as the select, we'd have to return the full Entity or not (given that we have to know how much space to allocate for the returned entity)
• Thoughts?

[ra0x3]

Again, I'm not married to my method so feel free to push back

[lostman]

I don't think this is valid Rust:

OrderEntity::amount::gt(5)
^^^^^^^^^^^
type          ^^^^^^
              ?      ^^
                     associated function

I was thinking about something like this:

pub struct Constraint<T> {
    constraint: String,
    phantom: std::marker::PhantomData<T>,
}

pub trait Field<T> {
    // TODO: Type needs to be convertible SQL fragment
    type Type: Sized + std::fmt::Debug;
    const NAME: &'static str;

    fn equals(val: Self::Type) -> Constraint<T> {
        Constraint {
            constraint: format!("{} = '{:?}'", Self::NAME, val),
            phantom: std::marker::PhantomData,
        }
    }
}

Then:

struct Order {
  id: usize,
  amount: i32,
}

struct OrderIdField;

impl Field<Order> for OrderIdField {
    type Type = usize;
    const NAME: &'static str = "id";
}

struct OrderAmountField;

impl Field<Order> for OrderAmountField {
    type Type = i32;
    const NAME: &'static str = "amount";
}

And using the vec! syntax (for now):

pub trait Entity<'a>: Sized + PartialEq + Eq + std::fmt::Debug {
    fn find(constraints: Vec<Constraint<Self>>) -> String {
        let mut buf = String::new();
        for c in constraints {
            if !buf.is_empty() {
                buf += " AND ";
            }
            buf += &c.constraint
        }
        buf
    }
}

So, we'd have something like:

find(vec![OrderIdField::eq(1usize), OrderAmountField::lt(123i32)])

Of course, we can instead have a simple AST:

pub struct Constraint<T> {
    constraint: String,
    phantom: std::marker::PhantomData<T>,
}

impl<T> Constraint<T> {
    pub fn and(self, other: impl Into<Expr<T>>) -> Expr<T> {
        Expr::And(Box::new(Expr::Leaf(self)), Box::new(other.into()))
    }
}

impl<T> From<Constraint<T>> for Expr<T> {
    fn from(c: Constraint<T>) -> Expr<T> {
        Expr::Leaf(c)
    }
}
pub enum Expr<T> {
    And(Box<Expr<T>>, Box<Expr<T>>),
    Leaf(Constraint<T>)
}

impl<T> Expr<T> {
    pub fn and(self, other: impl Into<Expr<T>>) -> Expr<T> {
        Expr::And(Box::new(self), Box::new(other.into()))
    }
}

An example:

        let x: Expr<Block> = {
            let e1: Constraint<Block> = BlockIdField::equals(block_frag.id.clone());
            let e2: Constraint<Block> = BlockIdField::equals(block_frag.id.clone());
            // constraint and constraint = expr
            e1.and(e2)
        };

        let y: Expr<Block> = {
            let e = BlockIdField::equals(block_frag.id.clone());
            // constraint and expr = expr
            // e.and(x)
            // or expr and constraint = expr
            x.and(e)
        };

        let z: Expr<Block> = {
            let e1: Constraint<Block> = BlockIdField::equals(block_frag.id.clone());
            let e2: Constraint<Block> = BlockIdField::equals(block_frag.id.clone());
            e1.and(e2)
        };

        // expr and expr = expr
        let v = z.and(y);

It may look a little convoluted, but it shows that these can be easily mixed and matched.

Another:

        let complex = BlockIdField::equals(block_frag.id.clone())
            .and(BlockConsensusField::equals(consensus.id.clone()))
            .and(BlockHeaderField::equals(header.id.clone()));

[ra0x3]

@lostman

• Thanks for the examples they definitely help
• I think the AST implementation is definitely a bit too complex for this use case
• I like your first approach
• I think the question I have now is could we extend your first approach to remove the need for Vec as @deekerno mentioned?
  • Ideally we want to be able to chain these operator functions in order to build the query (e.g., Order.find(where: Order::id::eq(5).and(Order::amount::gt(6).or(Order::amount::lt(7));) or something like that
  • Note the nesting here of .and() within an .and()
  • Slightly increases the scope but I think we should support that as well (if it makes sense)

[lostman]

@ra0x3, the AST is an internal implementation detail and pretty simple. Avoiding it wouldn't make anything simpler.

Some questions:

1. What to do about nullable fields? If we have Option<U32> then SomeField::eq(None), or SomeField::lt(Some(5)) should be supported, correct?

The first would translate to WHERE some_field = null and the second to WHERE some_field < 5, correct?

2. What about Array fields? What operations should we support if we have Array<U32>?

Some form of any and all come to mind.

        let arr2 = TransactionInputsField::any(
            TransactionInputsField::equals(
                SizedAsciiString::new("".to_string()).unwrap(),
            )
            .and(TransactionInputsField::equals(
                SizedAsciiString::new("xyz".to_string()).unwrap(),
            )),
        );

This is a little verbose but can be shortened to:

        let arr2: ArrayExpr<Transaction> = {
            type T = TransactionInputsField;
            T::any(
                T::equals(SizedAsciiString::new("".to_string()).unwrap())
                    .and(T::equals(SizedAsciiString::new("xyz".to_string()).unwrap())),
            )
        };

[lostman]

Some options for structuring the DSL for writing constraint expressions:

Using modules:

        let c: Constraint<ScriptTransaction> = {
            use script_transaction::*;
            // ((gas_price < '100' OR gas_price > '200') AND gas_limit = '1000')
            gas_price()
                .lt(100i32)
                .or(gas_price().gt(200))
                .and(gas_limit().eq(1000))
        };

Using associated functions:

        let z = ScriptTransaction::gas_price()
            .lt(100i32)
            .or(ScriptTransaction::gas_price().gt(200))
            .and(ScriptTransaction::gas_limit().eq(1000));

Which could be shortened with:

use ScriptTransactionResult as T;
T::gas_price()...

Having a module with these functions seems cleaner.

I still need to think about how array fields would fit into this.

[ra0x3]

• Hm @lostman if you think you can do the tokens just as fast as you could do raw Strings (we'd use https://docs.rs/sqlparser/0.39.0/sqlparser/ since that's what we're using elsewhere), I do think the token approach would be the more "proper" way to do this.
• I also think the "Using associated functions:" method looks a bit more ORM-y
  • Would this (the example you list for associated functions) be wrapped in ScriptTransaction.find() ?
  • Example:

let result = ScriptTransaction.find(ScriptTransaction::gas_price()
            .lt(100i32)
            .or(ScriptTransaction::gas_price().gt(200))
            .and(ScriptTransaction::gas_limit().eq(1000)));

[ra0x3]

@lostman

• Answering your question about Option
  • Some(x) would be the same as just x
  • None would be is NULL
  • We would not support passing a Vec<T> to .find()
  • Just only basic non-generic primitives for now