How to retrieve model that is joined in query.

[IvanGarzon]

Hello guys I am struggling a bit to retrieve the data that I need. I got the user_testers and users tables where in users_testers I store the user_id among other data. I am getting all data properly when there are not parameters in the request. However, when I passed the search parameter the query works as expected with the exception of the user model I am joining.

getTesters: async ctx => {

        const params = ctx.query;

        const page = parseInt( params.page ) || 0;
        const search = params.search || null;
        const status = params.status || null;

        try {

            let testers = await UserTesters
                .query()
                .joinRelated( `[ user( defaultUserSelect, filterTester ) ]` )
                .modifiers({
                    filterTester: query => query.modify( 'filterBySearch', search )
                })
                .orderBy( 'id' )
                .page( page, 5 );
            
            ctx.ok( testers );

        } catch ( err ) {
        
            // No results found.
            console.log( err ); // --> true\
        }
    },

UserTester Model

class UserTesters extends Model {
   static get tableName() {
       return 'user_testers';
   }

   static modifiers = {
       defaultUserSelect( query ) {
           const { ref } = UserTesters;
           query.select( ref( 'id' ), ref( 'status' ));
       },

       filterByStatus( query, status ) {
           if ( status && status.length ) { 
               const { ref } = UserTesters;
               query.where( ref( 'status' ),  status );
           }
       },
   }

   static get relationMappings() {
       
       const User = require( './user.model' );

       return {
           user: {
               relation: Model.BelongsToOneRelation,
               modelClass: User,
               filter( builder ) {
                   builder.where( 'status', 'ACTIVE' );
               },
               join: {
                   from    : 'user_testers.user_id',
                   to      : 'user.id'
               }
           }
       }
   }
};

User Model

class User extends Model {
    static get tableName() {
        return 'user';
    }

    static get idColumn() {
        return 'id';
    }

    static modifiers = {
        defaultSelects( query ) {
            query.select( 'id' );
        },

        defaultUserSelect( query ) {

            const { ref } = User;
            query.select( ref( 'id' ), ref( 'email' ), ref( 'name' ), ref( 'last' ) );
        },

        filterBySearch( query, search ) {
            if ( search && search.length ) {

                query.modify( ( q ) => { 

                    const { ref } = User;
                    q = q.where(  ref( 'email' ), "like", `%${ search }%` ); 
                });
            }
        },
    }
};

Response:

Without search parameter is OK:

{
    results: [
       {
           id: 2
           status: "ACTIVE"
           user_id: '1'
           user: [
              {
                  id: 1
                  name: 'James',
                  last: 'Gordon'
                  email: '[email protected]'
              }
           ]
       },
       {
           id: 3
           status: "ACTIVE"
           user_id: '2'
           user: [
              {
                  id: 2
                  name: 'Bruce',
                  last: 'Wayne',
                   email: '[email protected]'
              }
           ]
       }
   ],
   total: 2
}

Now the real issue if i pass search parameter as "[email protected]"

I got this response:

{
    results: [
       {
           id: 3
           status: "ACTIVE"
           user_id: '2'
       }
   ],
   total: 1
}

So as you notice the user instance is missing in the response, any help would be so much appreciate it.

[lehni]

Could you provide a simple reproducible example using the reproduction-template.js in the objection repo? This would help a lot with debugging and understanding this issue.