Using where clause to filter a related table

Data Service
1

Application ID

FDF6D44D-169A-56A5-FFB7-8FC364D66E00

Expected Behavior

I want to query a whole table (posts_meta) with its relation (column post_data) and filter some of the results based on attributes of the post_data. I try filtering the results with the where clause unfortunately the filters which refer to the relation do not seem to apply.

  1. A single query against the database joining retrieving the data
  2. which has applied the where clause filters to the results
  3. a Behave similar to SQL
	const queryBuilder = Backendless.DataQueryBuilder.create();
	queryBuilder.setRelated("post_data")
	queryBuilder.setSortBy(["posted_date"])
	queryBuilder.setWhereClause("post_data.lang = 'en' AND post_data.post_type='published'")
	const PAGE_SIZE = 100
	let offset = 0
	queryBuilder.setPageSize(PAGE_SIZE)
	queryBuilder.setOffset(offset)
	const postsDB = Backendless.Data.of('posts_meta');
	console.log("Fetching posts...")
	postsDB.find(queryBuilder).then(log_success).catch(log_error)

Actual Behavior

Instead of a flat list with all the values what I get is a list of the posts_meta objects which contain all the relations in the column field post_data (a nested object) with all related objects rather than the filtered ones.

I read all the documentation but it only speaks about doing multiple queries to filter the subqueries and then joining the data in the client code using multiple transactions for each ObjectID of the parent table which I wish I wouldn’t have to do for many many reasons.

I thought we were having a SQL database.

2

Hi Victor!

Unfortunately there is no other way to do filtration on relations at the current moment.
I will discuss with team possibility for adding this feature in one of future releases.

Regards,
Andriy

3

ok… thank you Andriy, the issue is that for a table of M elements and N relations I need to do M*N transactions, in addition to more complex code, bugs on client side, non atomic operations, etc… in SQL I could have done it in one single line immediately (but backendless theoretically offers much more than SQL).

Where/when can I know if the feature will be added?

4

We discuss it today and I will write back to you right after discussion.

Regards,
Andriy

5

Hi Andriy,

I am not a JS super expert, but I can program in quite a few other languages, and I felt the query was not just complex but also quite complicated compared to the documentation examples.
Is this really the way to go or am I missing something? I could perhaps join both objects in one but what worries me more is the boilerplate and nested promises, and the query was still not a complex one (I am still toying with a personal blog page to evaluate backendless for a more complex projects).

const queryBuilder = Backendless.DataQueryBuilder.create();
	queryBuilder.setSortBy("posted_date")
	const PAGE_SIZE = 100
	let offset = 0
	queryBuilder.setPageSize(PAGE_SIZE)
	queryBuilder.setOffset(offset)
	const postsDB = Backendless.Data.of('posts_meta');
	
	console.log("Fetching posts...")
	postsDB.find(queryBuilder).
		then(function (objects) {
			var promises = objects.map(object => {
				const oid = object.objectId
				const loadRelationsQueryBuilder = Backendless.LoadRelationsQueryBuilder.create()
				loadRelationsQueryBuilder.setRelationName("post_data")
				loadRelationsQueryBuilder.setWhereClause("lang = '" + lang + "' AND post_type='published'")
				return Backendless.Data.of("posts_meta").loadRelations(oid, loadRelationsQueryBuilder).
					then(function (elements) {
						return elements.map(function (e) {
							return {
								header_pic: object.header_pic,
								header_vid: object.header_vid,
								url: object.url,
								posted_date: object.posted_date,
								title: e.title,
								post_type: e.post_type,
								introduction: e.introduction,
								body: e.body,
								lang: e.lang
							}	
						})
					})
			});
			var p2 = promises.flat()
			var p3 = Promise.all(p2)
			return p3
		}).
		then(e => log_success(e.flat()))
		.catch(log_error)

Note that the goal here was get posts by language and published state; each post is translated in a few languages and they share some common metadata.

6

Hi Victor!

No, you don’t miss anything.
You can try to rearange your code by moving loadRelationsQueryBuilder out of promise and move result mapping to separate function if possible.
You will not be able to get rid from promisses since sync API is deprecated currently and will be removed from SDK in future releases.

Regards,
Andriy

7

Hi @Victor_Escobar_Olmos!

Sorry for delay with response.

We discussed this feature today and decided to implement it in one of the future releases. It is hard to tell about exact date when it will be released but I think that it will be available in 2-3 months.
Internal issue BKNDLSS-20750.

Regards,
Andriy

8

Has this been implemented?

Cheers,

Michael Iverson

9

Hello @Michael_Iverson,

it is not implemented yet.
I will make sure we discuss this feature with our team.
As soon as we do have news regarding filtering relations we’ll be sure to reach out.

Cheers,
Stanislaw