Virtual fields and preloading

[tverra]

I couldn't find any equivalent of .Net Entity Framework's virtual fields in this package, which is fields declared in the model that only generates fields in the model class and not in the database table. They are used to store lists of preloaded rows from other tables.

In my example, I have a list of contests which all have an owner. Each contest has on owner id. When I'm loading the list of contests, I also want to load their owners, since i'll be displaying both the contest's and the owner's name the next time I'm updating the app's state. I then find myself either putting both the contest and the owners into a list of keyword maps, or creating two separate lists where I have to continously sort out the right owner I need for every contest displayed. It would be more convenient and effecient in my case, both for code readability and efficiency, to just have a owner field in my contest class where I could put the preloaded owner, and then just get the name by calling Contest.owner.name.

A workaround is to use the custom code feature to add a field directly to the contest class which can hold the preloaded element, and it seems to work just fine. It would just feel and look better to be able to do something along the lines of SqfEntityField('owner', DbType.virtual, table: tableOwner) which could generate the field Owner owner in the contest class, but not create a row in the database table. It would also have been nice to be able to preload by just writing Contest().select().preload(['owner']), instead of having to do multiple queries, also inspired by the .Net Entity Framework.

[hhtokpinar]

OK, you want to create a virtual field in your models, right?

[tverra]

In short, yes.

[hhtokpinar]

Hi @tverra I added SqfEntityFieldVirtual field type for virtual property

Example: Step 1- When creating a model:

    const tablePerson = SqfEntityTable(
        tableName: 'person',
        primaryKeyName: 'id',
        primaryKeyType: PrimaryKeyType.integer_auto_incremental,
        fields: [
            SqfEntityField('firstName', DbType.text),
            SqfEntityField('lastName', DbType.text),
            SqfEntityFieldVirtual('fullName', DbType.text),
        ],
        customCode: '''
            init()
            { 
            fullName = '\$firstName \$lastName';
            }'''
        );

Step 2- When using:

    final personList = await Person().select().toList();

    for (final person in personList) {
        person.init();
        print('fullName: ${person.virtualName}');
    }

I didn't prefer initialization virtual fields automatically because it can be overloaded when working with big data. You must call when you need it

[tverra]

Thank you for your time! This is a start, but the type of the field would in almost every case not be any of the DbTypes, but rather the type of the object which is referenced by a foreign key. Say I have the tables:

const tableOwners = SqfEntityTable(
    tableName: 'owners',
    primaryKeyName: 'id',
    primaryKeyType: PrimaryKeyType.integer_unique,
    fields: [
      SqfEntityFieldRelationship(
          fieldName: 'contestOwnerId',
          parentTable: tableContests,
          relationType: RelationType.ONE_TO_MANY),
      SqfEntityFieldVirtual(tableContests),  // the type of field that I want to be able to define
    ],
);

const tableContests = SqfEntityTable(
    tableName: 'contests',
    primaryKeyName: 'id',
    primaryKeyType: PrimaryKeyType.integer_unique,
    fields: [
      SqfEntityFieldVirtual(tableOwners),  // the type of field that I want to be able to define
    ],
);

I would then expect the Owner class to have the field List<Contest> contests, and the Contest class to have the field Owner owner. I would then be able to do this:

Owner owner = await Owner().select().toSingle();
owner.contests = await owner.getContests().toList();

List<Contest> contests = await Contest().select().toList();
for (Contest contest in contests) {
    contest.owner = await contest.getOwner().toSingle();
}

or even better:

Owner owner = await Owner().select().contests.preload().toSingle();

List<Contest> contests = await Contest().select().owner.preload().toSingle(); 

This would be a very useful feature for my kind of project, where I have the data spread across multiple tables, and most of the time receive an array of objects from different tables in each request from the server.

[hhtokpinar]

I'm also working on creating a view model in addition to the table model. Does this feature work?

[tverra]

I don't know, I still haven't tried the views feature. Might be able to do it later today.

[tverra]

Maybe I misunderstood your question; if you were asking if the implemented virtual fields were working, then I'd have to say that they don't work in my case. I need to be able to use them for objects of the types that are generated from the model, not just simple data types.

[hhtokpinar]

I think the dynamic type will help you, right?

[tverra]

Well technically yes, but I would still prefer to be able to use a model name as the type for the field.

[hhtokpinar]

How about that virtual property created automatically such as plContests field under the Owner model? maybe it can also be loaded by the help of boolean parameter named preload in toList() for example

final owner = await Owner().select().toSingle(preload:true);
// or
final contests = await Contest().select().toList(preload:true);

just you'll type when using:

owner.plContest...
or
contests[0].plOwner...

[tverra]

Yes, this sounds more like what I wanted.

[hhtokpinar]

OK. in the latest version added new feature for relationships preload parent or child fields to virtual fields that created automatically starts with 'pl'

this field will be created for Product table

Category plCategory;

this field will be created for Category table

List<Product> plProducts;

Using (from parent to child):

    final category = await Category().select()..(your optional filter)...toSingle(preload:true);

    // you can see pre-loaded products of this category
    category.plProducts[0].name

or Using (from child to parent):

    final products = await Product().select().toList(preload:true);

    // you see pre-loaded categories of all products
    products[0].plCategory.name

[tverra]

Excellent, I'll try it out once it is released. By the way, is it possible to preload only certain fields? Else it would result in me having to keep a lot of unused data in memory if i wanted to use the preloading, since there is a lot of connections in my database.

[hhtokpinar]

Excellent, I'll try it out once it is released. By the way, is it possible to preload only certain fields? Else it would result in me having to keep a lot of unused data in memory if i wanted to use the preloading, since there is a lot of connections in my database.

I usually write the answer after it's released, just check it :)

by the way, I'm working on how do I set that certain fields

[tverra]

There probably is a delay between when you publish a release and when I receive it, so I couldn't get the release from pub.gen at the time of your answer. It's updated now though.

[hhtokpinar]

Now you can send certain field names with preloadFields parameter for preloading. For example: toSingle or toList(preload:true, preloadFields:['plField1','plField2'... etc])

just update and re-generate the models pls... sqfentity_gen: ^1.2.1+19

[hhtokpinar]

Yeah, I know that updating package is always late anyway, we are sending fields as string temporarily, I'll take preloaded fields into a class container

Best luck...

[tverra]

In sqfentity_gen: ^1.2.1+18, when preloading products into categories, all categories in the list has their parent category preloaded as well. Is this the intended behaviour?

Category category = await Category().select().id.equals(3).toSingle(preload: true);
List<Product> products = category.plProducts;

for (Product product in products) {
  if (product.plCategory != null) print(product.plCategory.name);
}

Output: I/flutter ( 6206): Category 3 I/flutter ( 6206): Category 3 I/flutter ( 6206): Category 3 I/flutter ( 6206): Category 3 I/flutter ( 6206): Category 3

[hhtokpinar]

No it’s not an intended behaviour. I’ll fix it...

[hhtokpinar]

In fact, in version +18 was being loaded without looking at the preload parameter and should be fixed after released +19

[tverra]

Great, the preloading does seem like it's working so far.

[tverra]

It would also be really helpful to be able to generate a method to parse a preloaded map. To reduce the number of requests in my app, I'm preloading objects on the server before sending them. I then have to manually make new fromMap-methods, like this:

Product.fromPreloadedMap(Map<String, dynamic> o) {
    id = o['id'] as int;
    name = o['name'] as String;

    plCategory = o['category'] != null
        ? ContestOrganizer.fromMap(o['category'])
        : null;
    categoryId = plCategory != null
        ? plCategory.id
        : o['categoryId'] as int;

    isSaved = true;
  }

[hhtokpinar]

so If I want to add that to my sample class, Product.fromMap should be like this?

Product.fromMap(Map<String, dynamic> o) {
productId = o['productId'] as int;
name = o['name'] as String;
....
plCategory = o['plcategory'] != null
        ? Category.fromMap(o['plcategory'] as Map<String, dynamic>)
        : null;
...
}

[tverra]

I was thinking something like this

Product.fromMap(Map<String, dynamic> o) {
    id = o['id'] as int;
    name = o['name'] as String;
    ....
    plCategory = o['category'] != null
        ? Category.fromMap(o['category'] as Map<String, dynamic>)
        : null;
    categoryId = plCategory != null ? plCategory.id : o['category'] as int;
    ....
  }

since our server doesn't put "pl" into the name of the preloaded objects, and also doesn't return the foreign id field when the object is preloaded.

[hhtokpinar]

My worry is that some developers uses the same name with the parent table’s name when naming to relationship field so it will cause a conflict between them. Maybe I can check all fields name before naming it. if the name is available, I name it with the parent table name

[hhtokpinar]

now released sqfentity_gen: ^1.2.3+1

it's generating code for me like below

    // RELATIONSHIPS FromMAP
    plCategory = o['category'] != null
        ? Category.fromMap(o['category'] as Map<String, dynamic>)
        : null;
    // END RELATIONSHIPS FromMAP

[tverra]

I think it's worse to prevent developers from following conventions because you want people to be able to break them. In that case, you should instead let developers override the field names.

[tverra]

I updated the package and re-generated the models, but I can't see the changes you mentioned.

[hhtokpinar]

It was generated according to my relation field named categoryId in tableProduct model. How is your model? It has any relationship field?

[tverra]

It's a copy of the models from the package's readme:

const tableCategory = SqfEntityTable(
    tableName: 'category',
    primaryKeyName: 'id',
    primaryKeyType: PrimaryKeyType.integer_auto_incremental,
    useSoftDeleting: true,
    modelName: null,
    fields: [
      SqfEntityField('name', DbType.text),
      SqfEntityField('isActive', DbType.bool, defaultValue: true),
    ]);

const tableProduct = SqfEntityTable(
    tableName: 'product',
    primaryKeyName: 'id',
    primaryKeyType: PrimaryKeyType.integer_auto_incremental,
    useSoftDeleting: true,
    fields: [
      SqfEntityField('name', DbType.text),
      SqfEntityField('description', DbType.text),
      SqfEntityField('price', DbType.real, defaultValue: 0),
      SqfEntityField('isActive', DbType.bool, defaultValue: true),
      SqfEntityFieldRelationship(
          parentTable: tableCategory,
          deleteRule: DeleteRule.CASCADE,
          defaultValue: '0'),
      SqfEntityField('rownum', DbType.integer, sequencedBy: seqIdentity),
      SqfEntityField('imageUrl', DbType.text)
    ]);

Well, I was looking in Product.fromMap(). I see that you've added relationship preloading in fromMapList(), which I guess works if the parent object already exists in the local storage. When parsing data from the server, I wasn't exactly looking for preloading. I wanted to be able to parse objects which were already preloaded on the server, like this:

Map<String, dynamic> category = {"id": 1, "name": "Category"};
Map<String, dynamic> product = {
    "id": 1,
    "name": "Product",
    "description": "Description",
    "price": 100,
    "category": category
};

List<Product> parsedProductList = await Product.fromMapList([product], preload: true);
print("Parsed category: " + parsedProductList[0].plCategory.toString());

Output: I/flutter (16814): Parsed category: null

[hhtokpinar]

I see. If you call fromMapList([product]) without preload: true then you can load into parsedProductList what you sent

by the way, If we send preload = true, we load the plCategory object twice, first one is Product.fromMap() and then again in Product.fromMapList() as below code (see comments those start with FIRST LOADING and SECOND LOADING)

we'll have to give up one, but they don't do the same operation, one of which is parsing from the Json Map you sent and the other from gets from database.

  static Future<List<Product>> fromMapList(List<dynamic> data,
      {bool preload = false, List<String> preloadFields}) async {
    final List<Product> objList = <Product>[];
    for (final map in data) {

      // FIRST LOADING plCategory due to fromMap
      final obj = Product.fromMap(map as Map<String, dynamic>);

      // PRELOAD
      if (preload) {
        if (preloadFields == null || preloadFields.contains('plCategory')) {
         // SECOND LOADING plCategory due to preload parameter
          obj.plCategory = await obj.getCategory();
        }
      } // END PRELOAD

      objList.add(obj);
    }
    return objList;
  }

Product.fromMap(Map<String, dynamic> o) {
    _setDefaultValues();
    productId = o['productId'] as int;
    ...
    ...
    // RELATIONSHIPS FromMAP
    // THIS IS FIRST LOADING plCategory due to fromMap
    plCategory = o['Category'] != null
        ? Category.fromMap(o['Category'] as Map<String, dynamic>)
        : null;
    // END RELATIONSHIPS FromMAP
  }

[hhtokpinar]

Or, I can fix the line in fromMapList to do not change if the parent object has loaded before like this:

it would be like below

       if (preloadFields == null || preloadFields.contains('plCategory')) {
          obj.plCategory = obj.plCategory ?? await obj.getCategory();
        }

instead of

if (preloadFields == null || preloadFields.contains('plCategory')) {
          obj.plCategory =  await obj.getCategory();
        }

[tverra]

Currently it's not working, because of the casing of o['Category']. All fields that are returned from the server are snake_case, which I manually have to convert to camelCase since the generated models would otherwise get snake case fields. That means the "category"-field will stay "category", not "Category". I would like to not have to manually differentiate between which values are preloaded on the server and which ones are not.

[tverra]

Except from that, it seems to work great.

[hhtokpinar]

Oh Sorry, I completely forgot about it! you can update to new released 1.2.3+6

[tverra]

The keys still aren't camel case, they're just lower case. It doesn't work if i have, say, a product owner.

Map<String, dynamic> o = {'productOwner': {}}
ProductOwner productOwner = 
ProductOwner.fromMap(o['productowner'] as Map<String, dynamic>);

productOwner is then is null, since the key is wrong.

[hhtokpinar]

can you try again it on the latest version sqfentity_gen 1.2.3+8?

[tverra]

It seems like it's the same with version 1.2.3+8

[hhtokpinar]

Are you sure is it the same? It must generate like this

ProductOwner.fromMap(o['productOwner'] as Map<String, dynamic>);

instead ProductOwner.fromMap(o['productowner'] as Map<String, dynamic>);

[tverra]

Yeah, you're right. I just had to do a flutter clean ...