Left join with multiple rows

[Stetsyk]

I saw a response #71 here about left join, but still, it's puzzling for me how to create left join with multiple rows.
For example, I have two tables:
E1(a:, b:, c:, d:)
E2(c:, d:, e:, f:)
And I want E3(a:, b:, c:, d:, e:, f:) to be the result of E1 left join E2 on key c:, d:
From your previous response about left join, I think it can be done in this way:

E3(a:, b:, c:, d:, e:, f:) :-
    E1(a:, b:, c:, d:),
    e AnyValue = (x :- E2(c:, d:, e:x)),
    f AnyValue = (x :- E2(c:, d:, f:x));

But in this approach when we have a lot of fields in the table, we will have a problem with duplication.
Can we do it in a more elegant way?

PS. Thanks for all your work, Evgeniy, and the contributors, logica is really a powerful tool.

[EvgSkv]

Hello @Stetsyk ,

We can pull multiple parameters with one aggregating expression as so

E3(a:, b:, c:, d:, e: x.e, f: x.f) :-
    E1(a:, b:, c:, d:),
    x AnyValue = ({e:, f:} :- E2(c:, d:, e:, f:));

This appears reasonably concise to me.

Please let me know if you have further questions or comments.

And thank you for your supportive feedback!

[Stetsyk]

Hi @EvgSkv,

Yes, it's a much better way:) Thank you for your prompt reply!

[Stetsyk]

Hi @EvgSkv ,

I still have an answer considering left join, when the second table in left join may have more than one row with a given key.
You have written here #71 that it can be done with List, but I couldn't manage to do it.

I will show with example what I mean.
I have two tables (logica definition) :

Employee(name: "Alice", role: "Product Manager", office: "SEA");
Employee(name: "Bob", role: "Engineer", office: null);
OfficeLocation(office: "SEA", city: "Seattle");
OfficeLocation(office: "SEA", city: "Los Angeles");

And I want EmployeeLocation to consist of:
name | city | office
Alice | Seattle | SEA
Alice | Los Angeles | SEA
Bob | None | None

Could you please help?

[rvalek]

One way to do this would be:

EmployeeLocation(name:, city:x.city, office:x.office) distinct :-
  Employee(name:, office:),
  (OfficeLocation(city:, office:),
   x == {office: office, city: city}
  | x AnyValue= ({city:, office:} :- OfficeLocation(city:, office:)));

[EvgSkv]

@Stetsyk sure, that's a great question!

@rvalek thanks for this option!

Here is my couple ways of doing it. When I said do it with lists I meant something like this:

@Engine("sqlite");

Employee(name: "Alice", role: "Product Manager", office: "SEA");
Employee(name: "Bob", role: "Engineer", office: null);
OfficeLocation(office: "SEA", city: "Seattle");
OfficeLocation(office: "SEA", city: "Los Angeles");

EmployeeLocation(employee: name, city:, office:) :-
  Employee(name:, office:),
  cities List= (c :- OfficeLocation(office:, city: c)),
  ((city in cities) | (Size(cities) == 0, city == null)); 

On BigQuery the disjunct would be IsNull(cities), city == null, as List returns null when aggregation is empty.

But now I think that this way might be more readable:

EmployeeLocation(employee: name, city:, office:) :-
  Employee(name:, office:),
  (
      OfficeLocation(office:, city:) |
      (
          ~OfficeLocation(office:),
          city == null
      )
  );

The logic in the second approach is very close to how we would formulate the query naturally:
City is an office location, or when there is no office for the employee, the city is null.

Let me know if you have further questions!

[Stetsyk]

@rvalek, @EvgSkv thank you very much for your responses, all of them work.

@EvgSkv the last method suggested by you is awesome!)