WHERE (or some filter) on relationship?

Tim_Jones · June 12, 2023, 8:19pm

Is it possible to have a WHERE on the primary table and a WHERE (or filter) on the relationship table?

In the above example, I only want to return Fundraisers where the StartDate is greater than today.

Thanks,
Tim

mark-piller · June 12, 2023, 9:03pm

How about this:

Fundraisers.StartDate > DATEADD(NOW(), 1, 'DAY')

Functions used in the example above are documented here.

Regards,
Mark

Tim_Jones · June 12, 2023, 9:23pm

Hi @mark-piller

Where would I put that? I tried something like that before I posted.

I would think this would only select a single fundraiser. Then I was thinking the WHERE only applied to anything at the top level…Then I was thinking I had no idea how it worked

mark-piller · June 12, 2023, 9:29pm

You put it into the where clause field:

If the Organizations table has a relation to Fundraisers, you can reference properties from Fundraisers (via the relation column name) to find the corresponding organizations.

Tim_Jones · June 12, 2023, 9:54pm

I need the Organizations data every time, which, if I understand your suggested solution, will not work if there are no fundraisers.

Right now, this is done with two queries. One gets the Organizations. The second gets the Organization’s fundraisers. I was trying to combine them to account for Scale Plan pricing.

Perhaps that is not possible.

Tim

mark-piller · June 12, 2023, 9:59pm

So you need to fetch organizations that either do not have any fundraisers, but if they do, then get only those organizations whose fundraisers start any time after today?

Tim_Jones · June 12, 2023, 10:04pm

Exactly!

mark-piller · June 12, 2023, 10:15pm

I do not see any fundraisers that start in the future and there are no organizations without fundraisers, so I cannot effectively test the query, however, the following should work:

Fundraisers is null or Fundraisers.StartDate > DATEADD(NOW(), 1, 'DAY')

Tim_Jones · June 13, 2023, 2:17am

I was simplifying the example to get conceptual help. If we’re going to real-world testing, I want the Fundraisers.Season.objectId to equal a specific ID. These are my attempts with your suggestion of IS NULL -

MicrostoreId = 100105 AND (Fundraisers IS NULL OR Fundraisers.Season.objectId = ‘F07719E5-ADC4-449D-B006-535FD1951A9D’)

MicrostoreId = 100105 AND (Fundraisers.Season IS NULL OR Fundraisers.Season.objectId = ‘F07719E5-ADC4-449D-B006-535FD1951A9D’)

I’m not having any more luck. I did include the relationship to the Fundraisers.Season table, but that doesn’t seem to make a difference.

Thanks @mark-piller

mark-piller · June 13, 2023, 2:26am

Including relationships does not have any bearings on how where clause is processed.

There is only one organization with microstoreId 100105, therefore adding additional conditions may reduce the results to none. I am curious what the expectations are for what you believe the server should be returning.

Tim_Jones · June 13, 2023, 2:33am

I have no expectations. I was simply wondering if it could be done in a single query.

mark-piller · June 13, 2023, 2:35am

What makes you think the query is not working right?

Tim_Jones · June 13, 2023, 2:40am

“not working right” in this context is not giving me the data I need for my application. It might be functioning exactly as designed.

I want to be able to filter the Fundraisers relationship to only return rows from Fundraisers where the Fundrasers.Season.objectId = ‘F07719E5-ADC4-449D-B006-535FD1951A9D’

The query returns all the Fundraisers for the Organization without applying the secondary filter.

Basically, I want a LEFT JOIN. EDIT that is how I would think about this; I could be totally wrong calling it a LEFT JOIN.

mark-piller · June 13, 2023, 2:44am

I think there is a conceptual misunderstanding. When you send a query to the Organizations table, you will get all the organizations that match the query, however, any related objects (such as Fundraisers) will be returned without any filtering.

Tim_Jones · June 13, 2023, 2:47am

Thanks, Mark. That is what I wanted to know.

Backendless has a lot of cool features. It occurred to me that there could be some feature to do a sub-query on the relation, so I wanted to check.

My presumption was it wasn’t possible. Confirming that lets me move on to optimization in other places without second-guessing that this could be done in 1 query.

I appreciate the help.

Tim

Tim_Jones · June 13, 2023, 1:55pm

In case this is helpful for anyone else.

If you’re making two queries to get the parent and then the related data because it needs to be filtered. I found in testing it was slightly faster to load all the relations and then filter in code instead of asking the DB to do it for you.

This was on a small data set, so your results may vary.

I hope this helps someone.

Tim