Help with multi-table data retrieval

This is my table schema, I’m open to suggestions on this.

In simple terms:

  • Users have Sellers (1:N)
  • Organizations have Fundraisers (1:N)
  • Sellers are connected to Fundraisers with the SellersFundraisers table (1:N)
  • SellersFundraisers connect to the Fundraisers table 1:1

To get the Sellers for a currently logged in User I am doing this:

Now I want to return all the sellers for the logged-in user and a binary Y/N if they have a relationship to one or more fundraisers in a date range (plus/minus 1 month from today)? This a check, so I don’t care about the data, it’s just important to know if they do or don’t have fundraisers in the date range.

Is this a situation where I’d be better off getting all the sellers in one step, then checking for fundraisers in the data range in a second step? I’m trying to do it in one step and I can’t get it to work in the REST console.

I can get the two sellers for the current user if I do this, but the returned fundraisers are not in the date range invalidating the check I want to do.

https://magicaljelly.backendless.app/api/data/Sellers?where=Users[Sellers].objectId%20%3D%20’3D13BF11-F48C-43D2-81FE-B29E61EC8198’%20%20AND%20SellersFundraisers.Fundraisers.StartDate%20<%201650900066000%20AND%20SellersFundraisers.Fundraisers.EndDate%20>%201645802466000&loadRelations=SellersFundraisers.Fundraisers%2CSellersFundraisers

[
{
“Status”: “Active”,
“MicrostoreURL”: “https://shop.meadowfarms.com/amalia-jones-1000000”,
“FirstName”: “Amalia”,
“created”: 1646965633000,
“SellersFundraisers”: [
{
“ZipCode”: “02116”,
“created”: 1647023201000,
“Address2”: null,
“StateCode”: “MA”,
“Fundraisers”: {
“Status”: “Active”,
“created”: 1646926721000,
“DeliveryDate”: null,
“MarkSchoolId”: 2,
“ownerId”: null,
“AppealMessage”: null,
“EndDate”: 1619496000000,
“Name”: “Spring 2021”,
“StartDate”: 1618200000000,
“Type”: “DSH”,
“___class”: “Fundraisers”,
“MarkRepId”: null,
“updated”: 1648222879000,
“objectId”: “E1CA5B18-4C32-4FF5-A5BB-7ABF23F42D32”,
“Notes”: null
},
“City”: “Boston”,
“Address1”: “123 Main St”,
“ownerId”: null,
“AppealMessage”: “Support Spring 21”,
“TeacherLastName”: “Smith”,
“Ship2X”: true,
“___class”: “SellersFundraisers”,
“updated”: 1648223435000,
“objectId”: “460A3D04-E393-43E8-BAF5-60D56ED0ABA2”
},
{
“ZipCode”: “02116”,
“created”: 1648223295000,
“Address2”: null,
“StateCode”: “MA”,
“Fundraisers”: {
“Status”: “Active”,
“created”: 1646873937000,
“DeliveryDate”: null,
“MarkSchoolId”: 1,
“ownerId”: null,
“AppealMessage”: null,
“EndDate”: 1634875200000,
“Name”: “Fall 2021”,
“StartDate”: 1633320000000,
“Type”: “DSH”,
“___class”: “Fundraisers”,
“MarkRepId”: null,
“updated”: 1648222857000,
“objectId”: “81D2D2E3-854A-4C47-97EA-48F9F505E5AB”,
“Notes”: null
},
“City”: “Boston”,
“Address1”: null,
“ownerId”: null,
“AppealMessage”: “Supporting Fall 21”,
“TeacherLastName”: “Johnson”,
“Ship2X”: false,
“___class”: “SellersFundraisers”,
“updated”: 1648223376000,
“objectId”: “AE21CB69-60C3-4D06-B635-047BDC67BD83”
},
{
“ZipCode”: null,
“created”: 1648223256000,
“Address2”: null,
“StateCode”: null,
“Fundraisers”: {
“Status”: “Active”,
“created”: 1648222917000,
“DeliveryDate”: null,
“MarkSchoolId”: 3,
“ownerId”: null,
“AppealMessage”: null,
“EndDate”: 1649995200000,
“Name”: “Spring 2022”,
“StartDate”: 1647835200000,
“Type”: “Online”,
“___class”: “Fundraisers”,
“MarkRepId”: null,
“updated”: null,
“objectId”: “756AF14F-E7B3-4E9A-96F2-1CCDBD00F04E”,
“Notes”: null
},
“City”: null,
“Address1”: null,
“ownerId”: null,
“AppealMessage”: “Support Spring 22!”,
“TeacherLastName”: “Walsh”,
“Ship2X”: false,
“___class”: “SellersFundraisers”,
“updated”: 1648223273000,
“objectId”: “C31E8F7C-9B74-4D00-B167-D191D052A186”
}
],
“Grade”: 3,
“Birthday”: null,
“ownerId”: “3D13BF11-F48C-43D2-81FE-B29E61EC8198”,
“Avatar”: null,
“___class”: “Sellers”,
“LastName”: “Jones”,
“updated”: 1647287173000,
“objectId”: “2686BFA4-75E8-4E2F-BE20-11C2CD7CC412”,
“ShopId”: 1000000
}
]

Alternatively if set additional constraints in the columns/properties I get the right results for the date range, but lose the second Seller.

[
{
“Status”: “Active”,
“MicrostoreURL”: “https://shop.meadowfarms.com/amalia-jones-1000000”,
“FirstName”: “Amalia”,
“created”: 1646965633000,
“SellersFundraisers”: [
{
“ZipCode”: “02116”,
“created”: 1647023201000,
“Address2”: null,
“StateCode”: “MA”,
“Fundraisers”: {
“Status”: “Active”,
“created”: 1646926721000,
“DeliveryDate”: null,
“MarkSchoolId”: 2,
“ownerId”: null,
“AppealMessage”: null,
“EndDate”: 1619496000000,
“Name”: “Spring 2021”,
“StartDate”: 1618200000000,
“Type”: “DSH”,
“___class”: “Fundraisers”,
“MarkRepId”: null,
“updated”: 1648222879000,
“objectId”: “E1CA5B18-4C32-4FF5-A5BB-7ABF23F42D32”,
“Notes”: null
},
“City”: “Boston”,
“Address1”: “123 Main St”,
“ownerId”: null,
“AppealMessage”: “Support Spring 21”,
“TeacherLastName”: “Smith”,
“Ship2X”: true,
“___class”: “SellersFundraisers”,
“updated”: 1648223435000,
“objectId”: “460A3D04-E393-43E8-BAF5-60D56ED0ABA2”
},
{
“ZipCode”: “02116”,
“created”: 1648223295000,
“Address2”: null,
“StateCode”: “MA”,
“Fundraisers”: {
“Status”: “Active”,
“created”: 1646873937000,
“DeliveryDate”: null,
“MarkSchoolId”: 1,
“ownerId”: null,
“AppealMessage”: null,
“EndDate”: 1634875200000,
“Name”: “Fall 2021”,
“StartDate”: 1633320000000,
“Type”: “DSH”,
“___class”: “Fundraisers”,
“MarkRepId”: null,
“updated”: 1648222857000,
“objectId”: “81D2D2E3-854A-4C47-97EA-48F9F505E5AB”,
“Notes”: null
},
“City”: “Boston”,
“Address1”: null,
“ownerId”: null,
“AppealMessage”: “Supporting Fall 21”,
“TeacherLastName”: “Johnson”,
“Ship2X”: false,
“___class”: “SellersFundraisers”,
“updated”: 1648223376000,
“objectId”: “AE21CB69-60C3-4D06-B635-047BDC67BD83”
},
{
“ZipCode”: null,
“created”: 1648223256000,
“Address2”: null,
“StateCode”: null,
“Fundraisers”: {
“Status”: “Active”,
“created”: 1648222917000,
“DeliveryDate”: null,
“MarkSchoolId”: 3,
“ownerId”: null,
“AppealMessage”: null,
“EndDate”: 1649995200000,
“Name”: “Spring 2022”,
“StartDate”: 1647835200000,
“Type”: “Online”,
“___class”: “Fundraisers”,
“MarkRepId”: null,
“updated”: null,
“objectId”: “756AF14F-E7B3-4E9A-96F2-1CCDBD00F04E”,
“Notes”: null
},
“City”: null,
“Address1”: null,
“ownerId”: null,
“AppealMessage”: “Support Spring 22!”,
“TeacherLastName”: “Walsh”,
“Ship2X”: false,
“___class”: “SellersFundraisers”,
“updated”: 1648223273000,
“objectId”: “C31E8F7C-9B74-4D00-B167-D191D052A186”
}
],
“FundraisersStartDate”: 1647835200000,
“Grade”: 3,
“Birthday”: null,
“ownerId”: “3D13BF11-F48C-43D2-81FE-B29E61EC8198”,
“Avatar”: null,
“___class”: “Sellers”,
“FundraiserEndDate”: 1649995200000,
“LastName”: “Jones”,
“updated”: 1647287173000,
“objectId”: “2686BFA4-75E8-4E2F-BE20-11C2CD7CC412”,
“ShopId”: 1000000
}
]

https://magicaljelly.backendless.app/api/data/Sellers?where=Users[Sellers].objectId%20%3D%20’3D13BF11-F48C-43D2-81FE-B29E61EC8198’%20%20AND%20SellersFundraisers.Fundraisers.StartDate%20<%201650900066000%20AND%20SellersFundraisers.Fundraisers.EndDate%20>%201645802466000&property=SellersFundraisers.Fundraisers.StartDate%20as%20FundraisersStartDate&property=*&property=SellersFundraisers.Fundraisers.EndDate%20as%20FundraiserEndDate&loadRelations=SellersFundraisers.Fundraisers%2CSellersFundraisers

Question on the schema: what is the reason for the intermediary SellersFundraisers table? If a seller has multiple fundraisers, why one connect Sellers with Fundraisers with a 1:N relation?

I knew you were going to ask that. When a relationship between the Seller and the Fundraiser is established there are options that the Seller can opt in/out of. As each seller will have a unique choice of options per fundraiser the data can’t be in the Seller or Fundraiser table.

I think that would be better modelled as an additional table (let’s call it FundraiserOptions). The table would be linked to Fundraiser with a 1:1 relationship.

That’s an interesting idea. It would also be linked to Sellers with a 1:1 as well, correct?

Tim

I don’t think so.

  • A seller has multiple fundraisers. For that you have a 1:N relation between Sellers and Fundraisers
  • A fundraiser has a specific options, that is captures through a 1:1 link between Fundraiser and FundraiserOptions

This will work well IF the same fundraiser is used only by one seller. Do you envision a scenario where different sellers use the same fundraiser?

Do you envision a scenario where different sellers use the same fundraiser?

Yes. You might be thinking of something like gofundme where an individual is creating a fundraiser. We work with organizations to create fundraisers and then get sellers to sign up to sell the fundraiser to friends and family.

  • Organizations are K-12 schools we work with (Organizations table)
  • Users are the parent of students in those schools (Users table)
  • Sellers are the children of Users (parents) and students of Organization(s) we work with (Sellers table)
  • An organization/school will run 1-2 fundraisers per year, resulting in the 1:N on Organizations to Fundraisers.
  • When an organization starts a new fundraiser the parents (Users) opt-in their children (sellers) to that fundraiser and have to agree to the options (SellersFundraisers or your proposed FundraiserOptions).

In summary:

  • Organizations have multiple fundraisers
  • Fundraisers have multiple sellers
  • User have multiple Sellers
  • Sellers have multiple fundraisers
  • Sellers may sell for multiple fundraisers from different organizations e.g. one year they’re in elementary school, the next they’re in middle school
  • For every Fundraiser a Seller opts into, there can be different options depending on the configuration of the Fundraiser by the Organization

This is very helpful. To reflect these definitions and constrains, I would recommend to model your schema as follows:

  1. Organizations has 1:N relation to Fundraisers
  2. Users has 1:1 relation to Organizations (this could be 1:N if children in a family go to different schools). Alternatively, (which might be a better option) Organizations has 1:N relation to Users.
  3. Users has 1:N relation to Sellers - these are the kids of a parent
  4. Fundraisers has 1:N relation to Sellers - these are the kids that are enrolled in a specific fundraiser.
  5. Fundraisers has 1:1 relation to FundraiserOptions

I think this captures everything you described. What do you think?

I have to think about most of that. The FundraisersOptions table still doesn’t make sense to me.

Think of a paper form that each Seller has to complete to register for a fundraiser. It has some checkboxes, it might require their shipping address. That’s information is unique to each Seller in the fundraiser and might not be consistent for the same seller in different fundraisers.

The information qualifies the Sellers participation in the fundraiser. It needs to relate to both a seller and a fundraiser.

Answers on that form are unique to each user, however, the form is the same for all, isn’t it?
The content of the form to display to users could come from the FundraiserOptions table (or you could hardcode the form in UI Builder if that makes sense). Answers they provide would need to be stored elsewhere. For that I’d create something like FundraiserEnrollment table that would have:

  • A 1:1 relationship to Seller
  • A 1:1 relationship to Fundraiser

Regards,
Mark

The options are hard coded in UI builder for now. An option table, as you suggested is in the backlog.

Your FundraiserEnrollment table is the same idea as my SellersFundraisers table but with a 1:1 relationship to sellers instead of sellers having a 1:n relationship with SellersFundraisers.

In either case isn’t have the relationship from Sellers to Fundraisers directly redundant, albeit easier? Since that relationship would exist in FundraiserEnrollment?

Yes, with FundraiserEnrollment in place, the direct relationship between Sellers and Fundraisers becomes redundant.

With that in mind it’s basically the same as my SellersFundraisers table but with the relationship to Sellers inverter.

With those changes to the schema made, does it help with the ability to retrieve a User’s Sellers and any Fundraisers within a certain date range?

Tim

Maybe :wink: I am multitasking here and it is a bit hard to re-visualize everything we have discussed. Do you mind posting an updated schema?

Not at all! I really appreciate the support.

  1. Organizations has 1:N relation to Fundraisers

This existed in my model

  1. Users has 1:1 relation to Organizations (this could be 1:N if children in a family go to different schools). Alternatively, (which might be a better option) Organizations has 1:N relation to Users.

We never show the connection between the Users and the Organization, it is always through the Sellers I don’t think adding this helps.

  1. Users has 1:N relation to Sellers - these are the kids of a parent

This existed in my model

  1. Fundraisers has 1:N relation to Sellers - these are the kids that are enrolled in a specific fundraiser.

We talked about this being redundant

  1. Fundraisers has 1:1 relation to FundraiserOptions

An Options table that controls the UI for the options is on my roadmap but not critical right now.

My SellersFundraisers and your FundraisersEnrollees are almost the same. The only difference is the direction of the relationship. I can change that if it helps you visualize the relationships.

It would be great if you could post the updated schema diagram. Also, to clarify your previous question below:

Am I understanding it right, that for the current user, you need to obtain all the Fundraisers that their kids are enrolled in within a certain date range of fundraisers?

I’ve updated the schema on the SellersFundraisers table. I can add the relationship for Users to Organizations if you really think that is needed. It seems like an additional redundant relationship to maintain that doesn’t have the granularity (more below) for most data queries I need in production.

Yes. But in that case, getting the sellers in one query and using a nested Dynamic List block with a second query probably makes more sense.

How this all started, and what I want to understand in Backendless is how to get the Sellers for the current user and an indicator (Y/N, 1/0, an ID/NULL, etc.) if they have an “active” fundraiser. Active in the context of our discussion is by date range, but there could be multiple factors that make the fundraiser active or not. I just simplified it for the sake of our discussion and so you don’t have to understand my entire app.

Get all Sellers for this user. If the Seller has one or more ‘active’ fundraisers, return some sort of a result, otherwise return null/0. In a relational DB, I’d use a left join or a sub select.

Is it difficult to retrieve data with many constraints in different tables? It seems like it so far. I’m only so-so at SQL and could have had this done in 10 mins or less. I feel like I’m missing something. If the answer is to get some initial data, then go back and get sub-constraints that’s a workable answer, I want to understand the principles that guide backendless UI-Builder development so I can get on with building!

Thanks @mark-piller

Hi Tim,

I added myself to your app so I could play with the query to assist you with your question. The problem I ran into is not with the schema, but incomplete data :wink: Specifically, I see there are two users with related Sellers. However, these sellers are not referenced from any of the SellersFundraisers objects. I didn’t want to mess with your data of course. Would you be able to set up a scenario in the data where there is a “closure” of data records so I can demonstrate a few queries for you?

Regards,
Mark

I’ve added the missing relationships to complete the schema changes.

I really appreciate your willingness to help me!

Tim