Nested Join on a many-to-many relationship

[webdeveloper20213]

Many-to-many joins

we have a database which stored roles as per users (where each user could belong to many roles):

create table users (
  "id" serial primary key,
  "name" text
);

create table roles (
  "id" serial primary key,
  "role_name" text
);

create table user_roles (
  "user_id" int references users,
  "role_id" int references roles,
  primary key (user_id, role_id)
);

using below query to get user roles data get but in some row roles column is []

const { data, error } = await supabase.from("users").select(`
  *, 
  roles (*)
`)
.eq("roles.role_name", "user")

using above below is my array response.
so how to do if roles is not there that particular records is not return.
can you please help how to get data if roles is not empty then & then return that particular records?

[
    {
        "id": "ca004b9a-22e4-46cc-9e79-bc3a5d99c467",
        "username": "super admin",
        "roles": []
    },
    {
        "id": "03a8cab4-de77-4fe7-bd3c-7787522f0e66",
        "username": "demo",
        "roles": [
            {
                "id": "83c058ea-f213-400b-8705-fbda770497cb",
                "name": "user",
                "created_at": "2023-08-23T09:59:01.050232+00:00",
                "updated_at": null
            }
        ]
    },
    {
        "id": "dfd96008-6727-45c3-9303-36291fa824e8",
        "username": "test",
        "roles": [
            {
                "id": "83c058ea-f213-400b-8705-fbda770497cb",
                "name": "user",
                "created_at": "2023-08-23T09:59:01.050232+00:00",
                "updated_at": null
            }
        ]
    }
]  

[GaryAustin1]

This seems more a help question for discussion rather than a bug.

const { data, error } = await supabase.from("users").select(`
  *, 
  roles!inner (*)
`)
.eq("roles.role_name", "user")

Note the added !inner which tells the system to do an inner join and filter the parent table.
I think that is what you are asking for.
The documentation is a bit hidden...