How to make joins

[BoikoIlya]

General info

• [X] I checked the troubleshooting page for similar problems

What is your question?

if I have first table "stories" and second table that contain story_id and user_id. how to select stories that not associated with passed user_id??

Relevant log output (optional)

please give Kotlin example

[jan-tennert]

Did you take a look at the docs: https://supabase.com/docs/guides/database/joins-and-nesting?

[BoikoIlya]

Did you take a look at the docs: https://supabase.com/docs/guides/database/joins-and-nesting?

ok I made that , it is working

supabaseClient.from("stories_and_users")
                    .select(
                        Columns.raw(
                            "stories (id, title, genre, sentence_limit, image, sentence_count)"
                        )
                    ){

                        filter {
                            neq("user_id", supabaseClient.auth.currentUserOrNull()!!.id)
                        }
                        range(offset.toLong(), (offset+ limit).toLong())
                    }.decodeList<StoryDtoWrapper>()

But I also need in that case select only rows that sentence_limit < 5, how to make that??

[jan-tennert]

@BoikoIlya You can just use the filter lt:

lt("stories.sentence_limit", 5) //You can acccess the joined table by prefixing with the table name

[BoikoIlya]

@BoikoIlya You can just use the filter lt:

lt("stories.sentence_limit", 5) //You can acccess the joined table by prefixing with the table name

and how to make group by ??

[BoikoIlya]

I don't need group by anymore, could you explain how to make this select in Kotlin

SELECT
    stories.id,
    stories.title,
    stories.genre,
    stories.sentence_limit,
    stories.image,
    stories.sentence_count
FROM
    stories
WHERE
    stories.is_done = FALSE
    AND stories.id NOT IN (
        SELECT story_id
        FROM stories_and_users
        WHERE user_id = 'dd47fbbc-71c8-4e35-8b42-f9dafa547a96'
    )

[jan-tennert]

Not sure if that is possible, but you can do two requests or a use database function.

[jan-tennert]

Closing due to inactivity.