Relationship Query

Posted by rgoyal@test.com on 03-Nov-2014 00:25

I would like to know how we can query object and related object in a single query. I'm new to Rollbase.

ex: rbf_selectQuery("SELECT id,name, R11XX FROM bXXX b, 100, callback1); // where R11XX is the relationship name of other object. 

I'm looking to construct a query which will also show values from other related objects.

Can anyone help. Thanks in advance.

All Replies

Posted by IramK on 03-Nov-2014 03:21

Use rbf_selectValue() using your Query.

Posted by rgoyal@test.com on 03-Nov-2014 03:31

Hi Iramk. Thanks for the reply.

But my query may return multiple records and I need to process all records so I couldn't use this rbf_selectValue() method.

Posted by Orchid Corpin on 03-Nov-2014 08:29

Hi,

To get the related object's fields you can use rbf_getFields. If the record has multiple child you can use Loop_begin / loop_end if it is only one child then you use directly the rbf_getFields with the id of your related record.

<script>
/*
	Assumes you are in the parent objects view page
	R56036   - > this is the related field
	product1 - > will be the object name of R56036
*/
'{!#LOOP_BEGIN.R56036}'
	rbf_getFields("product1", parseInt("{!id}"), "name, product_code, unit_price", function(relName, objId, values) {
		console.log(values['name']);
		console.log(values['product_code']);
		console.log(values['unit_price']);
	});
'{!#LOOP_END.R56036}'
</script>

Hope this may help.

Regards,

Orchid

Posted by rgoyal@test.com on 03-Nov-2014 09:16

Thank Orchid.

What I'm looking for is to show many to many relationship over the portal page. So for this we may need to query all the parents ids and then we would fetch the related child records.

In your previous example we are processing child records from the object view page but I was looking for such useful stuffs over the portal pages.

Can you assist?

Thanks,

Rohit

Posted by Orchid Corpin on 03-Nov-2014 09:45
Yes sure! That code will work on portal page too (Record view). First, make sure you enable "Ajax calls" in Portal Properties.
You want the query to be in a Generic Page portal?

Regards,
Orchid
Posted by Orchid Corpin on 03-Nov-2014 10:31
We may have problems querying all Parent records, assuming we are in the Generic Page we use selectQuery since we cannot use loop_begin/loop_end on this page type, maxRows in selectQuery is limited to 20,000
What is your main objective, so we can find another option?

Regards,
Orchid
Posted by rgoyal@test.com on 04-Nov-2014 03:11
Simply what I need is let say we have two object A and B. A has a look-up to B.(i.e A is parent and B is child).
I want to query A fields (let say name,..) from B object select query. Is it possible to do this in Generic Portal pages ?
My portal page will show various objects records along with other related data.

Ex: Query on B and find related A records values.
Can anyone help?

Thanks,
Rohit

[collapse]
On Mon, Nov 3, 2014 at 10:02 PM, Orchid Corpin <bounce-ocorpin@community.progress.com> wrote:
Reply by Orchid Corpin
We may have problems querying all Parent records, assuming we are in the Generic Page we use selectQuery since we cannot use loop_begin/loop_end on this page type, maxRows in selectQuery is limited to 20,000
What is your main objective, so we can find another option?

Regards,
Orchid
Stop receiving emails on this subject.

Flag this post as spam/abuse.




--
Rohit Goyal   Software Developer  I  Astrea IT Services   rgoyal@astreait.com  +91-9654373714 l  www.astreait.com  l 
[/collapse]
Posted by pvorobie on 04-Nov-2014 10:59

Simplest solution: create field of type "Related Field" on B object, point it to a field on A object. This way you don't need to query anything.

Posted by Orchid Corpin on 04-Nov-2014 16:59

[mention:05b5f00eae4a468d844fa8bedcafd110:e9ed411860ed4f2ba0265705b8793d05] is right, this is much easier if you want to get the children from a parent's view page, no more query.

But since you are in a Generic Page you can't make use of that. I created a sample query from parent to child.

You may change: warehouse = parent, product1 = child


<script>
//QUERY ALL PARENT
rbf_selectQuery("SELECT name, id FROM warehouse", 5, my_callback);

function my_callback(data) {
	for(i=0; i<data.length; i++) {
		console.log("Parent ID: "+data[i][1]);
		console.log("Parent Name: "+data[i][0]);

		/*
			QUERY ALL CHILD FROM EACH PARENT
			WHERE  = QUERY CHILDREN WHICH HAS SAME ID AS THE CURRENT PARENT
			R56036 = parent ID
		*/
		rbf_selectQuery("SELECT name, id, R56036 FROM product1 WHERE R56036='"+data[i][1]+"'", 10, function(child) {
			for(x=0; x<child.length; x++) {
				console.log("Child ID of : "+child[x][2]+" : "+child[x][0]+" -- "+child[x][1]);
			}	//END CHILDREN LOOP
		});

	}	//END ALL PARENT LOOP
}
</script>

Regards,

Orchid

Posted by Orchid Corpin on 13-Nov-2014 16:36
Hi Rohit,

Are the suggested solution / workaround did fix the issue? or do you have any questions related to this thread?
Thank you.

Regards,
Orchid
This thread is closed