Help with data source query setup

I have 3 tables in use:

  • Group
  • Item
  • GroupItem

The reason is that there will be a many to many relationship. An item will have more than 1 group. Groups will have more than 1 item.

I would like to -combine- the fields from both GroupItem (which has settings for the relationship, such as start date and end date) and from Item (which has Item name, for example). Before I even reach that goal however, I’ve noticed I can’t construct a query using the tool where “self” is an option for evlauation. For example, if I set the data thing type to Item, and then select Data Source and build a query of type Item, I have the following fields available for evaluation:

  • Name
  • Slug
  • unique id

What I had been looking for was something akin to “This Item” or “Self” so that I could evaluate it against an “is in” and then a query against GroupItem and return GroupItem’s Item as a list.

I’m assuming either Slug or unique id can be used in this fashion, but a few attempts to work that out did not return results where I’m certain results exist.

Once I get past that barrier, I’m still stuck with being unable to figure out how to merge the fields for these objects for use without taking the somewhat drastic step of building a “fact table” that I kept 100% updated based on changes to these three tables. And unfortunately, the plugin I’m using does require that the results be in a single data set - Unlike a repeating group where I was able to work around this with a sub-repeating group and subquery for labels within each repeating group row, I don’t have that nesting option here.

Thank you in advance!

You’ll want to use 1:1 so the GroupItem record will look like

[Group_id][Item_id]

If [Group_id] is Apple
[Apple][3]
[Apple][4]
[Apple][5]
etc

where 3, 4, 5 are item ids.

Then you group them by [Group_id] and it’ll give you all the items.

Each Group should be a single entry and same with Item.

Thank you glyons - Forgive me, but I’m not sure what you mean by “use 1:1”.

Currently GroupItem is setup as follows:

Group of type Group
Item of type Item

Is use 1:1 a reference to a type of query I can write for the datasource?

1 to 1 relationship. You’re going to have a difficult time setting up many to many or any kind of list relations - especially in bubble.

I’m not sure why you have a ‘type’ - isn’t the table itself acting as your type?

For example it could be like this in excel,
image

It is a 1:1 relationship per record. The table itself is what enables the many-to-many relationships, with multiple records. I assumed (and have enjoyed the benefits of) that the preferred methodology for a relational table was to use a field of the type you wished to relate.

Group table is Group Type. As you’ve shown. Same for item. But the GroupItem table has these fields:

Group - Group (type)
Item - Item (type)

Rather than

group_id - number/other (type)
item_id - number/other (type)

This has made it easy to work with repeating lists etc, by pulling out GroupItem’s Item and GroupItem’s Group. But when I’m working with an object that binds to a list of the related type, such as Item, and need to source that list from a collection of GroupItem records, there doesn’t seem to be a way to do that.

This topic was automatically closed after 70 days. New replies are no longer allowed.