Skip to content

Relationships

Jump to bottom
Hüseyin Tokpınar edited this page Jun 19, 2020 · 8 revisions

image

What's Relationships?

When creating a database, common sense dictates that we use separate tables for different types of entities. Some examples are customers, orders, items, messages, etc... But we also need to have relationships between these tables. For instance, customers make orders, and orders contain items. These relationships need to be represented in the database. Also, when fetching data with SQL, we need to use certain types of JOIN queries to get what we need. SqfEntity ORM shows its ability here, allowing you to easily access the related parent or child entities of any entity, without JOIN with INNER, OUTER, LEFT, or RIGHT, etc..

How to add relationship columns?

Define these tables to add relationship fields in the next steps

// Define the 'tableCategory' constant as SqfEntityTable for the category table
const tableCategory = SqfEntityTable(
  tableName: 'category',
  primaryKeyName: 'categoryId',
  primaryKeyType: PrimaryKeyType.integer_auto_incremental,
  fields: [
    SqfEntityField('name', DbType.text, formIsRequired: true),
    SqfEntityField('isActive', DbType.bool, defaultValue: true)
   ]);

// Define the 'tableProduct' constant as SqfEntityTable for the product table
const tableProduct = SqfEntityTable(
    tableName: 'product',
    primaryKeyName: 'productId',
    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),
    ],
    );

const tableProductProperties = SqfEntityTable(
    tableName: 'product_properties',
    // now you do not need to set primaryKeyName If you set RelationshipField as primarykeyfield
    // declare fields
    fields: [
      SqfEntityField('title', DbType.text),
    ]);

You can add a relationship column in your table model with the following 3 types

1- How to define a one-to-one relationship?

add a SqfEntityFieldRelationship column in tableProductProperties model with RelationType.ONE_TO_ONE and its parentTable must be tableProduct like below:

SqfEntityFieldRelationship(parentTable: tableProduct, deleteRule: DeleteRule.CASCADE, isPrimaryKeyField: true, relationType: RelationType.ONE_TO_ONE)

finally, your tableProductProperties should be as follows:

const tableProductProperties = SqfEntityTable(
    tableName: 'product_properties',
    fields: [
      SqfEntityField('title', DbType.text),
      SqfEntityFieldRelationship(parentTable: tableProduct, deleteRule: DeleteRule.CASCADE, 
                                 isPrimaryKeyField: true, relationType: RelationType.ONE_TO_ONE),
    ]);

Now you can get or set the value of the property as follows:

final product = await Product().getById(1);
product.product_property.title = 'Test title 1';
product.save();

2- How to define a one-to-many relationship?

add a SqfEntityFieldRelationship column in tableProduct model with RelationType.ONE_TO_MANY and its parentTable must be tableCategory like below:

SqfEntityFieldRelationship(parentTable: tableCategory, deleteRule: DeleteRule.CASCADE, relationType: RelationType.ONE_TO_MANY)

Finally, your tableProduct should be as follows:


const tableProduct = SqfEntityTable(
    tableName: 'product',
    primaryKeyName: 'productId',
    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),

      /// Add Relationship column for CategoryId of Product
      SqfEntityFieldRelationship(
          parentTable: tableCategory,
          fieldName: 'categoryId',
          deleteRule: DeleteRule.CASCADE,
          ),
    ],
    );

Now you can access easily the related parent or child entities of Product or Category table like this:

OPTION 1: LAZY LOADING

// get any product
final product = await Product().getById(3);

// get category of the product above:
final category = await product.getCategory();
    
// get all products of the category above:
final products = await category.getProducts().toList();

// get top 5 products of the category above:
final products = await category.getProducts().top(5).toList();

// get top 5 products of the category above and order by name:
final products = await category.getProducts().top(5).orderBy('name').toList();

Note: Please notice that a method name that gets sub-records has named plural (ends with "s")

It is possible to reproduce samples as seen

OPTION 2: PRELOADING

// get any product
final product = await Product().getById(3, preload: true, loadParents: true);

// get category of the product above:
final category = product.plCategory;
    
// get any category:
final category = Category().getById(1, preload: true);

// get products of the category above:
final products = category.plProducts;

3- How to define a many-to-many relationship?

In Chinook.db there are two tables named Playlist and Track which are related to many-to-many relations as you can see the figure above.

And there is a table called PlaylistTrack which is saved ids of those related records here. Now, you can define SqfEntityFieldRelationship column in one of these tables, like this:

Define this SqfEntityFieldRelationship column in Track table model with the relationType is MANY_TO_MANY

SqfEntityFieldRelationship(
      parentTable: tablePlaylist,
      deleteRule: DeleteRule.NO_ACTION,
      relationType: RelationType.MANY_TO_MANY,
      manyToManyTableName: 'PlaylistTrack'),

Notes:

  1. manyToManyTableName parameter is optional. It's the name of the table in which interrelated records will be saved. SqfEntity will name it if you don't set this parameter.
  2. You can define that column in any table referenced to each other.

After generate the models you can list related records like this:

(lazy-load) to list Tracks from a Playlist:

final playlist = await Playlist().getById(1);
final tracks = await playlist.getTracks().toList();

(lazy-load) to list Playlists from a Track:

      final track = await Track().getById(1);
      final playlists = await track.getPlaylists().toList();

(pre-load) to list Tracks from a Playlist:

final playlist = await Playlist().getById(1, preload:true);
final tracks = playlist.plTracks;

(pre-load) to list Playlists from a Track:

      final track = await Track().getById(1, preload:true);
      final playlists = track.plPlaylists;

4- How to define a one-to-many-viceversa relationship?

If you want to define one-to-many relationship columns from table A to table B and one-to-many relationships from table B to table A, Flutter does not allow you to define two tables that refer to each other. So, in these cases you can define one-to-many-viceversa columns like below:

const table1 = SqfEntityTable(
  tableName: 'table1',
  primaryKeyName: 'id',
  fields: [
      SqfEntityField('name', DbType.text),
    // ... OTHER FIELDS
  ]
);

const table2 = SqfEntityTable(
  tableName: 'table2',
  primaryKeyName: 'id',
  fields: [
      SqfEntityField('name', DbType.text),
      SqfEntityFieldRelationship(parentTable: table1, deleteRule: DeleteRule.NO_ACTION, relationType: RelationType.ONE_TO_MANY_VICEVERSA)
    // ... OTHER FIELDS
  ]
);

Note: you can define that column in any table referenced to each other.

After generating models, you can access two methods that get SubItems and ParentItems like below:

Sample for table1:

final table1 = Table1();
final subrecords = await table1.getTable2s();
final parentrecord = await table1.getTable2();

Please notice that a method name that gets sub-records has named plural (ends with "s")

Sample for table2:

final table2 = Table2();
final subrecords = await table2.getTable1s();
final parentrecord = await table2.getTable1();
Clone this wiki locally