Simple many-to-many joins?

[schvenk]

I'm building a to-do list app backed by Supabase. Each to-do can have zero or more tags. So, in the DB I have a tasks table, a tags table, and a tasks_tags table so a user can have multiple tags, associate a task with as many of them as she likes, and it's easy to look up a task by its tags (e.g., to display all the tasks for a given tag). I'm not married to this particular schema, but it seemed like the most standard/straightforward.

When fetching a task, Brick seems to do a great job of pulling down the tags as an array! But when updating a task, I run into errors like:

flutter: OfflineFirstRepository: WARNING: #upsert supabase failure: PostgrestException(message: {"code":"PGRST204","details":null,"hint":null,"message":"Could not find the 'tags' column of 'tasks' in the schema cache"}, code: 400, details: Bad Request, hint: null)

In other words, on upsert it's not recognizing that tags is synthesized from the relation. Is there a way Brick cane handle this for me?

If not, maybe that's OK. Before starting to integrate Brick, I was talking to Supabase directly. I simply omitted tags from the JSON, then updated the tags separately. But I'm not sure how to do that either since the task model has a tags field and even if I set it to null, it's included in Brick's query to Supabase.

Thanks!

[tshedor]

@schvenk are you defining Tag as an association model or as JSON/Serdes? If it's an association, I don't see why this shouldn't work. I think in order for Brick to work with your schema, you'll need a TaskTag join model

[schvenk]

Sorry for all the questions! Feel free to point me to the right spots in the docs...I just haven't been able to find what I'm looking for there but maybe I'm missing something.

Anyway.

Question 1: What do I need to do to define Tag as an association model? Here's Tag today:

...
part 'tag.model.g.dart';

@ConnectOfflineFirstWithSupabase(
  supabaseConfig: SupabaseSerializable(tableName: 'tags'),
)
@CopyWith()
class Tag extends OfflineFirstWithSupabaseModel {
  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String id;

  final String name;
  final String? type;
  final DateTime? lastUsedAt;
  final int? sortScore;

  Tag({
    String? id,
    required this.name,
    this.type,
    this.lastUsedAt,
    this.sortScore,
  }) : id = id ?? const Uuid().v4();
}

Question 2: How would that TaskTag join model work? Would I set Task's field type to List<TaskTag> instead of List<Tag>? Is there an annotation I'd need to use to say "this is a join model"?

[tshedor]

@schvenk So Tag as its written will be an association model when you use it as dependency injection. For example:

part 'tag.model.g.dart';

@ConnectOfflineFirstWithSupabase(
  supabaseConfig: SupabaseSerializable(tableName: 'tasks_tags'),
)
@CopyWith()
class TaskTag extends OfflineFirstWithSupabaseModel {
  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String id;

  final Task task;

  final Tag tag;

  TaskTag({
    required this.task,
    required this.tag,
  }) : id = id ?? const Uuid().v4();
}

However, looking at this now, you can't have a List<TaskTag> on a tag because you'd have a recursive dependency. Which Brick can handle, but it's not officially supported unless you're confident in what you're doing.

So what you'll likely need is something closer to this:

part 'tag.model.g.dart';

@ConnectOfflineFirstWithSupabase(
  supabaseConfig: SupabaseSerializable(tableName: 'tasks_tags'),
)
@CopyWith()
class TaskTag extends OfflineFirstWithSupabaseModel {
  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String id;

  @Supabase(ignore: true)
  final Task task;

  @Sqlite(ignore: true)
  String get taskId => task.id;

  @Supabase(ignore: true)
  final Tag tag;
  
  @Sqlite(ignore: true)
  String get tagId => tag.id;

  TaskTag({
    required this.task,
    required this.tag,
  }) : id = id ?? const Uuid().v4();
}

And then your tag model can be

@ConnectOfflineFirstWithSupabase(
  supabaseConfig: SupabaseSerializable(tableName: 'tags'),
)
@CopyWith()
class Tag extends OfflineFirstWithSupabaseModel {
  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String id;

  final String name;
  final String? type;
  final DateTime? lastUsedAt;
  final int? sortScore;
  final List<TaskTag> tasksTags;

  Tag({
    String? id,
    required this.name,
    this.type,
    this.lastUsedAt,
    this.sortScore,
   required this.taskTags,
  }) : id = id ?? const Uuid().v4();
}

This is all untested, so I'd encourage you to play with it and see if it fits your schema / use case. To answer your second question, Brick determines if a field is an association using Dart's analyzer as part of the build_runner phase. So no extra annotations necessary.

[schvenk]

Thanks! But...as you surmised I'm getting errors, not in the model files themselves but at code-generation time. Is there a good spot in the documentation for me to read to understand the details e.g., the various types of ignore, when to use the object vs. the ID vs. both as the relation field, and so on?

[richard457]

should the final List<TaskTag> tasksTags; column be available in tags in supabase? if yes then with which types?

[tshedor]

Thanks! But...as you surmised I'm getting errors, not in the model files themselves but at code-generation time

I wouldn't have expected this. What errors?

Is there a good spot in the documentation for me to read to understand the details e.g., the various types of ignore, when to use the object vs. the ID vs. both as the relation field, and so on?

You can try looking at the KitchenSink model to see how code is generated. There's a note on recursive association generation for Supabase specifically and generic annotation documentation. As a generally single maintainer, I've found it difficult to keep the documentation and code in sync, so the most up-to-date information will be in the Dart doc of what you're trying to use.

[tshedor]

should the final List tasksTags; column be available in tags in supabase? if yes then with which types?

This would theoretically be the join table described in the initial comment. There'd be a PK column, a column for tag_id that references the tags table, and a column for task_id that references the tasks table.

[richard457]

Commenting on this to see if I can understand this concept or get support:
Having model

@ConnectOfflineFirstWithSupabase(
  supabaseConfig: SupabaseSerializable(tableName: 'purchases'),
)
class Purchase extends OfflineFirstWithSupabaseModel {
  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String id;

  @Supabase(ignore: true)
  // @Sqlite(ignore: true)
  List<Variant>? variants;
}

and

@ConnectOfflineFirstWithSupabase(
  supabaseConfig: SupabaseSerializable(tableName: 'variants'),
)
class Variant extends OfflineFirstWithSupabaseModel {
  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String id;
   @Sqlite(index: true)
  final String? purchaseId;
}

I have

Purchase purchase = Purchase(
        spplrTin: "11",
        spplrNm: id,
        spplrBhfId: "01",
        spplrInvcNo: 1,
        rcptTyCd: "N",
        pmtTyCd: "N",
        cfmDt: "cfmDt",
        salesDt: "salesDt",
        totItemCnt: 1,
        taxblAmtA: 1.0,
        taxblAmtB: 1.0,
        taxblAmtC: 1.0,
        taxblAmtD: 1.0,
        taxRtA: 1.0,
        taxRtB: 1.0,
        taxRtC: 1.0,
        taxRtD: 1.0,
        taxAmtA: 1.0,
        taxAmtB: 1.0,
        taxAmtC: 1.0,
        taxAmtD: 1.0,
        totTaxblAmt: 1.0,
        totTaxAmt: 1.0,
        totAmt: 1.0,
      );
repository.upsert<Purchase>(purchase);

Variant variant = Variant(name: "name", purchaseId:purchase.id);

repository.upsert<Variant>(purchase);

On supabase I have made purchase_id part of the variant to be the foreign key of purchase and I can see data in supabase
the question is when I do:
final responses = await repository.get<Purchase>get();
responses.first.variants --> This will have 0 entries. where am I wrong?

[richard457]

I even wrote a test but it still fail:

import 'package:supabase_models/brick/brick.g.dart';
import 'package:brick_supabase/src/supabase_provider.dart';
import 'package:test/test.dart';
import 'package:brick_supabase/testing.dart';
import 'package:uuid/uuid.dart';

void main() {
  final mock = SupabaseMockServer(modelDictionary: supabaseModelDictionary);
  group('Purchase with Variants', () {
    setUp(mock.setUp);
    tearDown(mock.tearDown);

    test('#getPurchaseWithVariants', () async {
      // Create mock Variant data
      final variant1 = Variant(
        id: const Uuid().v4(),
        name: 'Variant 1',
        purchaseId: '1', // Link to the Purchase
        taxPercentage: 18.0,
      );
      final variant2 = Variant(
        id: const Uuid().v4(),
        name: 'Variant 2',
        purchaseId: '1', // Link to the Purchase
        taxPercentage: 18.0,
      );

      // Create mock Purchase data with Variants
      final purchase = Purchase(
        id: '1',
        spplrTin: '123456789',
        spplrNm: 'Supplier Name',
        spplrBhfId: 'BH123',
        spplrInvcNo: 1001,
        rcptTyCd: 'RCPT001',
        pmtTyCd: 'PMT001',
        cfmDt: '2023-10-01',
        salesDt: '2023-10-01',
        totItemCnt: 2,
        taxblAmtA: 100.0,
        taxblAmtB: 200.0,
        taxblAmtC: 300.0,
        taxblAmtD: 400.0,
        taxRtA: 10.0,
        taxRtB: 20.0,
        taxRtC: 30.0,
        taxRtD: 40.0,
        taxAmtA: 10.0,
        taxAmtB: 40.0,
        taxAmtC: 90.0,
        taxAmtD: 160.0,
        totTaxblAmt: 1000.0,
        totTaxAmt: 300.0,
        totAmt: 1300.0,
        variants: [variant1, variant2], // Include Variants in the Purchase
      );

      // Mock Supabase request and response
      final req = SupabaseRequest<Purchase>();
      final resp = SupabaseResponse([
        await mock.serialize(purchase),
      ]);

      // Stub the server with the mock data
      mock.handle({req: resp});

      // Initialize the provider
      final provider = SupabaseProvider(mock.client,
          modelDictionary: supabaseModelDictionary);

      // Retrieve the Purchase
      final retrieved = await provider.get<Purchase>();

      // Assertions
      expect(retrieved, hasLength(1)); // Ensure one Purchase is returned
      expect(
          retrieved.first.variants, isNotNull); // Ensure Variants are not null
      expect(retrieved.first.variants,
          hasLength(2)); // Ensure there are 2 Variants
      expect(retrieved.first.variants?.first.name,
          'Variant 1'); // Check Variant data
      expect(retrieved.first.variants?.last.name,
          'Variant 2'); // Check Variant data
    });
  });

[tshedor]

@richard457 I think you may have it backwards. I think you need a variant_id column on the purchases table. However, there's not a lot of information on the Variant model so I'm not sure if this is the right suggestion.

@ConnectOfflineFirstWithSupabase(
  supabaseConfig: SupabaseSerializable(tableName: 'purchases'),
)
class Purchase extends OfflineFirstWithSupabaseModel {
  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String id;

  final List<Variant> variants;
}

You would then access variants by

purchase.variants

This will have 0 entries. where am I wrong?

In your model definition, you have @Supabase(ignore: true) on the Variant field. That tells Brick to not request the data from Supabase.

[richard457]

@tshedor Thank you very much and sorry to trouble you, a small clarification to my model:

I removed @supabase(ignore: true) from the Purchase model but then on upsert supabase complained that model variants do not exist on the table, I thought of adding to the table with JSONB TYPE but I think that is wrong.
so about variant table the Purchase table is supposed to hold many variant or item being purchased so It was making sense to me to have Purchase hold list of variants or items.

[tshedor]

@richard457 In this case you may need to do

@Supabase(ignoreTo: true)
final List<Variant> variants;

and (assuming you have a purchase_variants join table)

final existingPurchaseVariants = await Repository().get<PurchaseVariant>(query: Query.where('purchaseId', purchase.id));
final variantsToKeep = <PurchaseVariant>{}; 
for (final variant in purchase.variants) {
  final purchaseVariant  = existingPurchaseVariants.firstWhere(
    (p) => p.purchaseId == purchase.id && p.variantId == variant.id, 
    orElse: PurchaseVariant(purchaseId: purchase.id, variantId: variant.id),
  );

  if (purchaseVariant.id == null {
    await Repository().upsert<PurchaseVariant>(purchaseVariant);
  } else {
    variantsToKeep.add(purchaseVariant);
  }
}

final variantsToDelete = existingPurchaseVariants.where((p) => !variantsToKeep.contains(p));
for (final variant in variantsToDelete) {
  await Repository().delete<PurchaseVariant>(variant);
}

Untested code; your logic may differ

[richard457]

Thank you, that really helped me, I now have progress
I realized this work I can get purchase.variants

 final purchases = await repository.get<Purchase>();
      for (final purchase in purchases) {
        if (purchase.variants != null) {
          for (final variant in purchase.variants!) {
            print('Variant ID: ${variant.id}');
          }
        }
      }

but

 final purchases = await repository.get<Purchase>(
          policy: OfflineFirstGetPolicy.alwaysHydrate,
          query: brick.Query(
              where: [brick.Where('spplrTin').isExactly("34cd7397f16918e")]));

the purchase.variants will be null
My model did not change a lot

class Purchase extends OfflineFirstWithSupabaseModel {
  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String id;
  List<Variant>? variants;
}

@ConnectOfflineFirstWithSupabase(
  supabaseConfig: SupabaseSerializable(tableName: 'variants'),
)
class Variant extends OfflineFirstWithSupabaseModel {
  @Supabase(unique: true)
  @Sqlite(index: true, unique: true)
  final String id;
  @Supabase(foreignKey: 'purchase_id')
  @Sqlite(index: true)
  String? purchased;
}

[richard457]

Realized that

 final purchases = await repository.get<Purchase>();
      for (final purchase in purchases) {
        if (purchase.variants != null) {
          for (final variant in purchase.variants!) {
            print('Variant ID: ${variant.id}');
          }
        }
      }

will work because as attached, it will call 28 purchases and the first 4 will have an empty variants array, and then the rest will be populated.

[richard457]

The problem happens when I add a query to await repository.get(); it will not populate the relationship

[tshedor]

I don't understand what the problem is, can you please consolidate your comments to a single message with relevant samples?

[richard457]

@tshedor I will try to summarize my issue as much as I can though I must accept that I might have skill issue regarding brick I apologize for that:

Question:

I might be experiencing some challenges regarding how Brick handles one-to-many relationships. I'll summarize the issue and my expectations to make it clear:

Context

I have two models:

• Model A
• Model B

Model B contains a list of Model A objects (List<A>), and Model A has a b_id field that acts as a foreign key referencing the id of Model B in Supabase. The database schema ensures that b_id is properly set up as a foreign key.

Expectations

When querying for an instance of Model B using Brick (e.g., repository.get<B>()), I expect the resulting object to automatically include the associated List<A>. This means that each Model B should be populated with its related Model A instances.

Observation

This expectation is not consistently met. Specifically:

• If I query Model B without any condition (e.g., repository.get<B>()), the associated List<A> is sometimes populated correctly.
• However, in other cases, especially with queries involving conditions or filters, the relationship does not seem to resolve, and the List<A> is left empty or incomplete.

Request

I would like to understand:

1. How Brick handles one-to-many relationships when querying models, particularly in the context of foreign keys.
2. Whether this behavior is expected or if there might be a misconfiguration in my models or repository setup.
3. Any recommended steps to ensure that querying Model B reliably includes the associated List<A> objects.

[tshedor]

@richard457 splitting your question to #533 . This problem is one-to-many joins instead of this issue's original many-to-many.

@schvenk This has gotten a little muddy. I'd like to bring it back to your original questions. Does this comment help?