inner join syntax

[ajonno]

I've got the following query that works fine, and ive figured out how to transpose it to the Dart API, however I can't work out correct inner join syntax for the C# API. can u let me know the correct syntax to use for inner joins ? also how can I return fields from the different tables (as per select p.name, mc.job, mc.department, m.name as moviename) ?

        select p.name, mc.job, mc.department, m.name as moviename
        from movie_crew mc
            inner join movie m
                on m.id = mc.movie_id
        inner join person p
        on p.id = mc.person_id
        where m.id = '90330ca1-8e38-4349-aabd-f5d95ece43b1'
        order by mc.job;

I tried the following but it's not working correctly. Pretty sure I've got the wrong syntax for the inner join piece:

var res2 = await supabase
                .From<PostgresModels.MovieCrew>()
                .Select("*, movie!inner(*)")
                .Select("*, person!inner(*)")
                .Filter("movie_id", Constants.Operator.Equals, "90330ca1-8e38-4349-aabd-f5d95ece43b1")
                .Get();

[ajonno]

hey all, should I perhaps post this in the Github discussions instead?

[acupofjose]

Apologies @ajonno, had a lot of family stuff over the last 2 weeks. I’ll look into this tonight/tomorrow and get back to you!

[ajonno]

Thanks mate no rush, family comes first!

[acupofjose]

Appreciate your understanding - Definitely needed the time with them, but all's settled now!

Given the following schema:

CREATE TABLE public.movie (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  created_at timestamp without time zone NOT NULL DEFAULT now(),
  name character varying(255) NULL
);

CREATE TABLE public.person (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  created_at timestamp without time zone NOT NULL DEFAULT now(),
  first_name character varying(255) NULL,
  last_name character varying(255) NULL
);

CREATE TABLE public.movie_person (
  id int generated by default as identity,
  movie_id int references movie(id),
  person_id int references person(id),
  primary key(id, movie_id, person_id)
);

insert into "public"."movie" ("created_at", "id", "name") values ('2022-08-20 00:29:45.400188', 1, 'Top Gun: Maverick');
insert into "public"."movie" ("created_at", "id", "name") values ('2022-08-20 00:29:45.400188', 2, 'Mad Max: Fury Road');

insert into "public"."person" ("created_at", "first_name", "id", "last_name") values ('2022-08-20 00:30:02.120528', 'Tom', 1, 'Cruise');
insert into "public"."person" ("created_at", "first_name", "id", "last_name") values ('2022-08-20 00:30:02.120528', 'Tom', 2, 'Holland');
insert into "public"."person" ("created_at", "first_name", "id", "last_name") values ('2022-08-20 00:30:33.72443', 'Bob', 3, 'Saggett');

insert into "public"."movie_person" ("id", "movie_id", "person_id") values (1, 1, 1);
insert into "public"."movie_person" ("id", "movie_id", "person_id") values (2, 2, 2);
insert into "public"."movie_person" ("id", "movie_id", "person_id") values (3, 1, 3);

The following query to http://localhost:3000/movie?select=*,person!inner(*)&person.first_name=eq.Bob should produce:

[
  {  
    "id":1,
    "created_at":"2022-08-20T00:29:45.400188",
    "name":"Top Gun: Maverick",
    "person":[{"id":3,"created_at":"2022-08-20T00:30:33.72443","first_name":"Bob","last_name":"Saggett"}]
  }
]

The same result can be replicated by the following in C#:

var movies = await client.Table<MovieWithPerson>()
                .Select("*,person!inner(*)")
                .Filter("person.first_name", Constants.Operator.Equals, "Bob")
                .Get();

Referencing the following model:

using System;
using System.Collections.Generic;
using Postgrest;
using Postgrest.Attributes;
using Postgrest.Models;

namespace PostgrestExample.Models
{
    [Table("movie")]
    public class MovieWithPerson : BaseModel
    {
        [PrimaryKey("id", false)]
        public int Id { get; set; }

        [Column("name")]
        public string Name { get; set; }

        [Column("person")]
        public List<MoviePerson> Persons { get; set; }


        [Column("created_at")]
        public DateTime CreatedAt { get; set; }
    }

    public class MoviePerson
    {
        [PrimaryKey("id",false)]
        public int Id { get; set; }

        [Column("first_name")]
        public string FirstName { get; set; }

        [Column("last_name")]
        public string LastName { get; set; }

        [Column("created_at")]
        public DateTime CreatedAt { get; set; }
    }
}

Hopefully that helps! Joseph

[acupofjose]

@ajonno did that help? Or is something still not working on your end?

[ajonno]

hey @acupofjose sorry been crazy busy last week will have time to check all this next few days stay tuned!

[acupofjose]

No worries! Just wanted to be sure your issue gets resolved