Left join in FindAgent

Hello,

I am creating an app which will read phone numbers of some contacts and persons, and address info too. But there is contacts and persons in the database who have no address row.

Can I get it in one request?

I was trying it with Dynamic provider

// Personen lesen
string[] columnNameResultP = { "person.person_id",
"person.contact_id",
"person.lastname",
"person.firstname",
"person.(address->owner_id).address1",
"person.(address->owner_id).zipcode",
"person.(address->owner_id).city"};

SuperOffice.CRM.ArchiveLists.ArchiveRestrictionInfo[] restrictionsP = {
SO.UtilsOnline.Helpers.SODataHelper.GetRestriction("person.(address->owner_id).atype_idx","16387")
};

page = 0;

FindResults findResults;
using (FindAgent findAgent = new FindAgent())
{
findResults = findAgent.FindFromRestrictionsColumns(restrictions, SuperOffice.CRM.ArchiveLists.DotSyntaxProvider.ProviderName, columnNameResult, 100, page);
}

 

And with FindContact provider:

// Personen lesen
string[] columnNameResultP = { "personId",
"contactId",
"firstName",
"lastName",
"personAddress/line1",
"personAddress/zip",
"personAddress/city"};
ArchiveRestrictionInfo[] restrictionsP = { SO.UtilsOnline.Helpers.SODataHelper.GetRestriction("contactDeleted", "false") };

using (FindAgent fa = new FindAgent())
{
page = 0;
items = fa.FindFromRestrictionsColumns(restrictionsP, "FindContact", columnNameResultP, 100, page).ArchiveRows;

}

 

Both seems to have a inner join to address table, no person without the address coming (my test example).

Can I manage to get persons left joined to addresses or I have to get it in a separately request?

 

Thank you!

RE: Left join in FindAgent

Hi Andrey,

I'm not able to observe the same results you do... I've used both the dynamic archive provider (via REST) and the CRMScript SearchEngine and getting results with empty address fields where appropriate.

GET /api/v1/archive/dynamic?$select=person.person_id,person.contact_id,person.(address->owner_id).address1,person.(address->owner_id).city&$filter=person.(address->owner_id).atype_idx eq 16387 HTTP/1.1
Content-Type: application/json
Accept: application/json
Authorization: Bearer {{token}}

SearchEngine se;
se.addFields("person", "person_id,contact_id,lastname,firstname");
se.addField("person.(address->owner_id).address1");
se.addField("person.(address->owner_id).zipcode");
se.addField("person.(address->owner_id).city");
se.addCriteria("person.(address->owner_id).atype_idx", "Equals", "16387");
printLine(se.executeTextTable());​
The SearchEngine generates the SQL:
select a0.person_id,a0.contact_id,a0.lastname,a0.firstname,a1.address1,a1.zipcode,a1.city 
from person a0 
left join address a1 on (a1.owner_id = a0.person_id and a1.owner_id = a0.person_id) 
where (a1.atype_idx = ?)
 
Curious...
 
 
Von: Tony Yates 28. Jan 2021

RE: Left join in FindAgent

Hello Tony,

thank you for response.

I tested it till now in our SOD.
Will check the resultung SQL in a local installation.

Von: Andrey Stupak 28. Jan 2021

RE: Left join in FindAgent

I tested it.

The problem was not the join (was really left), but the restriction over address.atype_idx, was null in the persons without address.
Afer replacing with restrictions

SO.UtilsOnline.Helpers.SODataHelper.GetRestriction("person.(address->owner_id).atype_idx",new String[] {"16387"},InterRestrictionOperator.Or,"equals", 1),

SO.UtilsOnline.Helpers.SODataHelper.GetRestriction("person.(address->owner_id).atype_idx",new String[] {""},InterRestrictionOperator.Or,"IsNull", -1)

it works.

Thank you Tony one more time!

Andrey

 

Von: Andrey Stupak 29. Jan 2021