Many to Many Relation not working on Second Relation

Questions, tips and tricks and techniques for scripting in Servoy

Many to Many Relation not working on Second Relation

Postby akaash » Fri Jan 31, 2014 4:59 pm

Hi all,

I'm not sure why this is but when I do a foundset.relation1.relation2 (from table 1 to table 2 to table3) I only get the first record from table 3 showing. Normal SQL inner joins would give me multiple records. The relationships are:

Table 1 to Table 2: One to Many
Table 2 to Table 3 : Many to Many

Any ideas on why this might be?
akaash
 
Posts: 30
Joined: Fri Jan 25, 2013 4:58 pm
Location: Montréal

Re: Many to Many Relation not working on Second Relation

Postby madimane » Sat Feb 01, 2014 1:02 am

Hi,
Effectively, when you use many to many relations, you will get Just the first record of the primary table and its related from the secondary table.

In this case, I suggest you to get your foundset by Query.
Othmane himadi
Analyst / Developper
Freelancer
---------------------
Whether we want it or not, being a developer means documenting our code is a necessity
User avatar
madimane
 
Posts: 85
Joined: Tue Oct 13, 2009 5:16 am
Location: Montréal,QC,Canada

Re: Many to Many Relation not working on Second Relation

Postby mboegem » Sat Feb 01, 2014 9:33 pm

akaash wrote:I'm not sure why this is but when I do a foundset.relation1.relation2 (from table 1 to table 2 to table3) I only get the first record from table 3 showing. Normal SQL inner joins would give me multiple records. The relationships are:

Table 1 to Table 2: One to Many
Table 2 to Table 3 : Many to Many

Any ideas on why this might be?


Use this:
Code: Select all
databaseManager.convertFoundSet(foundset.relation1, 'relation2')


https://wiki.servoy.com/display/public/ ... rtFoundSet
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Many to Many Relation not working on Second Relation

Postby akaash » Sun Feb 02, 2014 1:49 am

Thanks a lot for the replies! I'll try using the convert foundset. Although it would be great if servoy supported many-to-many relationships in the same ways SQL does.
akaash
 
Posts: 30
Joined: Fri Jan 25, 2013 4:58 pm
Location: Montréal

Re: Many to Many Relation not working on Second Relation

Postby Yeroc » Sun Feb 02, 2014 7:01 am

akaash wrote:Thanks a lot for the replies! I'll try using the convert foundset. Although it would be great if servoy supported many-to-many relationships in the same ways SQL does.

Indeed. You can try voting for SVY-324.
Yeroc
 
Posts: 109
Joined: Tue Aug 12, 2008 1:12 am
Location: Calgary, AB, Canada

Re: Many to Many Relation not working on Second Relation

Postby mboegem » Sun Feb 02, 2014 8:08 pm

akaash wrote:Thanks a lot for the replies! I'll try using the convert foundset. Although it would be great if servoy supported many-to-many relationships in the same ways SQL does.


I doubt this will happen as this will break a lot of existing solutions.

The way many to many relationships are handled is like I posted.
You can also use the query builder, which will most probably work this way and will provide you db-vendor independent way of writing queries.
But still this is more work than using the convert foundset way...
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam


Return to Methods

Who is online

Users browsing this forum: No registered users and 8 guests

cron