Related model fetching returns wrong sql query. (many to many)

[ozergul]

Introduction

Hello I am coding simple blog site with admin panel. I have Post, Tag and Category models.

Post.model

import bookshelf from '../core/config/bookshelf';
import Tag from "./post.model"
import Category from "./category.model"

const Post = bookshelf.Model.extend({
    tableName: 'posts',

    tags: function() {
        return this.belongsToMany(Tag, "posts_tags", "post_id", "tag_id");
    },

    categories: function() {
        return this.belongsToMany(Category, "posts_categories", "post_id", "category_id");
    },

})

export default Post

Category.model

import bookshelf from '../core/config/bookshelf';
import Post from "./post.model"

const Category = bookshelf.Model.extend({
    tableName: 'categories',

    posts: function() {
        return this.belongsToMany(Post, "posts_categories", "category_id", "post_id");
    }
})

export default Category

Tag.model

import bookshelf from '../core/config/bookshelf';
import Post from "./post.model"

const Tag = bookshelf.Model.extend({
    tableName: 'tags',

    posts: function() {
        return this.belongsToMany(Post, "posts_tags", "tag_id", "post_id");
    }
})

export default Tag

I am inserting post like this. I know it is a bit long sorry..

export async function addPost(req, res){

    let title = req.body.body.title
    let slug = functions.toSeoUrl(req.body.body.title)
    let content = req.body.body.content
    let tags = req.body.body.tags
    let categories = req.body.body.categories


    let user_id = 1 /* auth */
    let status = 1
    
    if(!title && !content) {
        res.json({
            success: false, message: "Fill title and content"
        })
        return false;
    }

    try {
        var obj = {title: title, slug: slug, content: content, user_id: user_id, status: status}
        let newPost = await Post.forge(obj).save().then((post) => {
            
            if(tags) {
                for(let tag of tags){
                    tag = tag.trim()
                    let tagSlug = functions.toSeoUrl(tag);
                    let tagTermObj = {term: tag, slug: tagSlug}
                    
                    Tag.where({slug: tagSlug}).fetch().then((fetchTag)=>{
                        if(fetchTag){
                            post.tags().attach(fetchTag)
                        } else {
                            Tag.forge(tagTermObj).save().then((newTag)=>{
                                post.tags().attach(newTag)
                            })
                        }
                    })
                }
            }


            if(categories) {
               for(let categoryID of categories){
                    Category.where({id: categoryID}).fetch().then((fetchCategory)=>{
                        if(fetchCategory){
                            post.categories().attach(fetchCategory)
                        }
                    })
                }
            }

            res.json({
                success: true, newPost: newPost, message: "Post added successfully"
            })

        })
 

        
    } catch (error) {
        new Error(error)
        logger.error(error);
        res.json({
            success: false, message: "Post cannot be added"
        })
    } 
}

And finally I am getting posts with their categories and tags. Categories are correct but tags is empty.

export async function getPosts (req, res) {
    let page = req.body.body.page

    if(!page) page = 1

    try {
        let posts = await Post.where({status: 1}).orderBy("-createdAt").fetchPage({page: page, pageSize: 5});
        let postsCount = await Post.forge({status: 1}).count();
        let send = []

        for (let p of posts) {
            let j = p.toJSON()
            j.content = converter.makeHtml(j.content)

            await p.tags().fetch().then(function(tags) {
                j.tags = tags.toJSON();
            });
            
            await p.categories().fetch().then(function(categories) {
                j.categories = categories.toJSON();
            });

            send.push(j)
        }
        res.json({
            posts: send,
            postsCount: postsCount
        })
    } catch (error) {
        new Error(error)
        logger.error(error);
        res.json({
            posts: [],
            postsCount: 0
        })
    }
}

Issue Description

Here are the knex sqls.

p.categories().fetch() generates this SQL

select distinct `categories`.*, `posts_categories`.`post_id` as `_pivot_post_id`, 
`posts_categories`.`category_id` as `_pivot_category_id` from `categories` 
inner join `posts_categories` on `posts_categories`.`category_id` = `categories`.`id` where `posts_categories`.`post_id` = ?

p.tags().fetch() generates this SQL

select distinct `posts`.*, `posts_tags`.`post_id` as `_pivot_post_id`, 
`posts_tags`.`tag_id` as `_pivot_tag_id` from `posts` 
inner join `posts_tags` on `posts_tags`.`tag_id` = `posts`.`id` where `posts_tags`.`post_id` = ?

As you can see select distinct posts`` is incorrect. It should be "tags".

If I correct and run this query, I can get tags. Correct query:

select distinct `tags`.*, `posts_tags`.`post_id` as `_pivot_post_id`, 
`posts_tags`.`tag_id` as `_pivot_tag_id` from `tags` 
inner join `posts_tags` on `posts_tags`.`tag_id` = `tags`.`id` where `posts_tags`.`post_id` = ?

What's wrong with my codes?

[ozergul]

Also my table structures

Posts

exports.up = function(knex, Promise) {
    return knex.schema.createTable('posts', function(t) {
        t.increments('id').primary();

        t.dateTime('createdAt').notNull().defaultTo(knex.fn.now())
        t.dateTime('updatedAt').nullable();

        t.string('title').notNull();
        t.string('content').notNull();
        t.string('slug').notNull();

        t.integer('user_id').notNull();
        t.integer('status').notNull();
    })
};

exports.down = function(knex, Promise) {
    return knex.schema.dropTable('posts');
};

Tags

exports.up = function(knex, Promise) {
    return knex.schema.createTable('tags', function(t) {
        t.increments('id').primary();
        t.string('term').notNull();
        t.string('slug').notNull();
   })
};

exports.down = function(knex, Promise) {
    return knex.schema.dropTable('tags');
};

Categories

exports.up = function(knex, Promise) {
    return knex.schema.createTable('categories', function(t) {
        t.increments('id').primary();
        t.string('term').notNull();
        t.string('slug').notNull();
   })
};

exports.down = function(knex, Promise) {
    return knex.schema.dropTable('categories');
};

Posts_Tags

exports.up = function(knex, Promise) {
    return knex.schema.createTable('posts_tags', function(t) {
        t.increments('id').primary();
        t.integer('post_id').unsigned().references('posts.id');
        t.integer('tag_id').unsigned().references('tags.id');
   })
};

exports.down = function(knex, Promise) {
    return knex.schema.dropTable('posts_tags');
};

Posts_Categories

exports.up = function(knex, Promise) {
    return knex.schema.createTable('posts_categories', function(t) {
        t.increments('id').primary();
        t.integer('post_id').unsigned().references('posts.id');
        t.integer('category_id').unsigned().references('categories.id');
   })
};

exports.down = function(knex, Promise) {
    return knex.schema.dropTable('posts_categories');
};

[ozergul]

Same situation but the solution not fixed my problem: https://github.com/bookshelf/bookshelf/issues/1724

Can you help @ricardograca