Left Joins in relationships

[BumpaRoy]

If I had a Customers table and an Orders table, and wanted to create a relationship that would give me all of the Customers, even though they did not have an entry in the Orders table (Left Join). But if they did have an entry, it would return their orderId's, otherwise null. How would this be handled in building a relationship?

[yorek]

Hi BumpaRoy.

The left join is the default behavior, so you don't have to do anything specific for that. For example if you have the following database schema and data (I'm using Azure SQL or SQL Server in this sample):

drop table if exists dbo.Orders;
drop table if exists dbo.Customers;
go

create table dbo.Customers
(
    Id int identity(1,1) not null primary key,
    [Name] nvarchar(100) not null 
)
go
create table dbo.Orders
(
    Id int identity(1,1) not null primary key,
    CustomerId int null constraint FK_Orders_Customers foreign key references dbo.Customers(Id),
    Details nvarchar(100) not null
)
go

insert into dbo.Customers ([Name]) values ('Davide'), ('Ani'), ('Sean'), ('Jerry')
insert into dbo.Orders (CustomerId, Details) values 
    (2, 'Order 1'), 
    (2, 'Order 2'), 
    (3, 'Order 3'), 
    (3, 'Order 4'), 
    (4, 'Order 5')
go

you can create the configuration file just by doing:

dab add Customers --source dbo.Customers --permissions "anonymous:*" --rest customers
dab add Orders --source dbo.Orders --permissions "anonymous:*" --rest orders

dab update Orders --relationship Customer --cardinality one --target.entity Customers
dab update Customers --relationship Orders --cardinality many --target.entity Orders

and you'll be good to go. Using GraphQL you can query the data with the following query:

query {
  customers {
    items {
      Id
      Name
      Orders {
        items {
          Id
          Details
        }
      }
    }
  }
}

which will return the left-join result:

{
  "data": {
    "customers": {
      "items": [
        {
          "Id": 1,
          "Name": "Davide",
          "Orders": {
            "items": []
          }
        },
        {
          "Id": 2,
          "Name": "Ani",
          "Orders": {
            "items": [
              {
                "Id": 1,
                "Details": "Order 1"
              },
              {
                "Id": 2,
                "Details": "Order 2"
              }
            ]
          }
        },
        {
          "Id": 3,
          "Name": "Sean",
          "Orders": {
            "items": [
              {
                "Id": 3,
                "Details": "Order 3"
              },
              {
                "Id": 4,
                "Details": "Order 4"
              }
            ]
          }
        },
        {
          "Id": 4,
          "Name": "Jerry",
          "Orders": {
            "items": [
              {
                "Id": 5,
                "Details": "Order 5"
              }
            ]
          }
        }
      ]
    }
  }
}

If you want instead an inner-join behavior, read here: #1381

[BumpaRoy]

As I finally figured out the relationships, and was able to get a query running, I did notice that the joined table was returning Nulls. So thank you so much for your detailed answer!! Very helpful.