add leftJoin conditions

BIZ TMS Aurelius
1

Hi, Wagner

CLASSES:
TPerson
  Id: Integer
  Name: String
end;
TFriend
  Id: Integer
  PersonId: TPerson
  PersonIdFriend: TPerson
end;
TEventPerson
  Id: Integer
  EventId: TEvent
  PersonId: TPerson
end;

DATA:
TPerson
 1, Peter
 2, Ana
 3, John
TFriend
 1, 1, 2
 2, 2, 1
TEventPerson
 1, 1, 1
 2, 1, 2
 3, 1, 3
SQL:
SELECT E.Id, E.EventId, E.PersonId, F.PersonIdFriend
FROM Event E
    left join Friend F on E.PersonID = F.PersonIdFriend
                      and F.PersonId = 2 //How to do thist?
where E.EventId= 1

Expected Result: 
1, 1, 1, null
2, 1, 2, 1
3, 1, 3, null

How to add left Join conditions?

Thanks Wagner



2

You should need to worry about the left join, Aurelius will handle it for you, but then you have to have your model built accordingly.

I guess you should add a Friends property in the TPerson class?
3

Hi,
  "You should need to worry  ..."
  R: Yes Aurelius will handle it for me, but i need one more condition "...and F.PersonId = 2 ..."
    if add(TLinq.Eq(..)) to the criteria, the result have just one row,
    Expected Result:
    2, 1, 2, 1
    because aurelius add condition to "where".
    I Expected :
    1, 1, 1, null
    2, 1, 2, 1
    3, 1, 3, null
   
  "I guess you should add a Friends property in the TPerson class?"
   R: No, the Person has many friends.
 
  I hope have been clear.
  Many thanks for your help and your patience :)


4

 "I guess you should add a Friends property in the TPerson class?"
   R: No, the Person has many friends.


That's why I told you, add a Friends property which is a TList<TPerson> with a ManyValuedAssociation attribute. Then you can do the "Join" by creating an alias to Friends property and filtering by id.
5

Hi
I don't understand what you mean, send you a email with Data Modeler Schema,
i think we are talk about different things
Really really grateful

6

if your class is like this:


TPerson
  Id: Integer
  Name: String
  Friends: TList<TPerson>; 
end;

you could do something like

List := Manager.Find<TEvent>
  .CreateAlias('PersonId', 'PersonId')
  .CreateAlias('PersonId.Friends', 'Friends')
  .Where(TLinq.Eq('Friends.Id', 2))

?
7

Hi, again :)
  How to Generate
  ...
  Friends: TList<TPerson>;
  ...
with de Data Modeler, with the model i sent to you by email?

Thanks

8

In the export dialog, select TPerson class, click many valued associations tab and check the one you want to be generated

9

Hi already try, the result:
...
    [ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], [TCascadeType.SaveUpdate, TCascadeType.Merge], 'FPersonIdF')]
    FFriendList: Proxy<TList<TFriend>>;
...

10

Hi, Wagner
  You see my last post?
  thanks
 

11

Yes, it generates the ManyValuedAssociation in lazy load mode (using Proxy) which is the preferred one. What is your question?

12

review every post is a long story, :)
Many thanks
 

13

As I said in my previous post, now you can use the Friends property and CreateAlias for it in your criteria.

14

Hi, Wagner
  Sent you a database by email
thanks