query one-to-many with leftJoin

[arashi-dev]

I have an article table which has a one-to-many relation to another table called heading. and this Is how I query the articles:

const articles = await db
    .select()
    .from(schema.article)
    .leftJoin(
        schema.heading,
        eq(schema.article.id, schema.heading.articleId),
    )
    .orderBy(
        desc(schema.article.publishedAt),
        desc(schema.article.title),
     )
    .where(...)
    .offset((page - 1) * LIMIT)
    .limit(LIMIT);

the problem is that the LIMIT value is 10 and it will only retrieve 3 articles because the total of the headings becomes 10. how can I get exactly 10 number of the articles?

PS. I am using .select() over .query.article because I have much more complexity in my code. this is just an example

[Angelelz]

What is in your where? If you are filtering only on stuff from your article, this is a solution:

const articlesSQ = db
    .select()
    .from(schema.article)
    .orderBy(
        desc(schema.article.publishedAt),
        desc(schema.article.title),
     )
    .where(...)
    .offset((page - 1) * LIMIT)
    .limit(LIMIT)
    .as("articlesSQ")

const articles = await db
    .select()
    .from(articlesSQ)
    .leftJoin(
        schema.heading,
        eq(articleSQ.id, schema.heading.articleId)
    );

You might need to add the selected columns to your select for this to work properly. I didn't test.

[arashi-dev]

Thank you so much! it's working perfectly

but another question: should I be worried about the performance that each article gets repeated many times just because it has many headings? is there any solution to prevent this data repetition?

[Angelelz]

That's how joins work, unfortunately.

One solution is to try and do json array aggregation for your headings. I'd suggest running this query with the RQB and the logger turned on, to see how it works.

[arashi-dev]

@Angelelz I'll check. thanks for your time :)