Limit join to one row using knex

Question

I am trying to fetch data from two tables using a join. The problem is forum_posts will contain several items with the same thread_id. I would only like to get the first one, either by id or by creating date.

function getByGroup(groupId) {
    return knex('forum_threads')
        .select('forum_threads.id', 'forum_threads.updated_at', 'forum_posts.content')
        .where('forum_threads.group_id', '=', groupId)
        .leftJoin('forum_posts', function() {
            this.on('forum_posts.thread_id', '=', 'forum_threads.id');
        })
        .orderBy('updated_at', 'desc')
        .then(function(threads) {
            return threads;
        });
}

I would like to add a limit(1) or a min to the join but not entirely sure how to do it.

Answer 1

You need to add a filter like the following to your left join criteria:

.andOn('forum_posts.created_at', '=', knex.raw("(select min(created_at) from forum_posts where forum_posts.thread_id = forum_threads.id)"))

This says to include the forum post record (as a left join match) if it has the minimum updated_at value for that id.

The full code. The below code isn't tested, although I did test the above snippet in a piece of my code.

function getByGroup(groupId) {
  return knex('forum_threads')
    .select('forum_threads.id', 'forum_threads.updated_at', 'forum_posts.content')
    .where('forum_threads.group_id', '=', groupId)
    .leftJoin('forum_posts', function() {
        this.on('forum_posts.thread_id', '=', 'forum_threads.id')
            /* The new line here */
            .andOn('forum_posts.created_at', '=', knex.raw("(select min(created_at) from forum_posts where forum_posts.thread_id = forum_threads.id)"))
    })
    .orderBy('updated_at', 'desc')
    .then(function(threads) {
        return threads;
    });
}

Cheers!

PS: You didn't ask, but something I find very helpful when debugging Knex is the .on() query reporting clauses:

    // ...
    .orderBy('updated_at', 'desc')
    /* your code above */
    .on('query', function(data) {
        // outputs the SQL query you generated & runtime data bindings.
        console.log(data);
    })
    .on('query-error', function(error, obj) {
        // outputs the Knex failed query, data, etc.
        console.log("Error:", error);
        console.log("Object: ", obj);
    })
    /* your code below */
    .then(function(threads) {
        return threads;
    });