How do I return only selected fields from the model and its relation

Questions and Answers
1
System Information
  • Strapi Version: 3.2.5
  • Operating System: ubuntu 18.04
  • Database: postgresql
  • Node Version: 12.18.3
  • NPM Version: 6.14.3
  • Yarn Version: -

I have a home-page model

home-page
 -message
 -images
 -categoryA
 -categoryB

“home-page”->model has many [“categoryA”,“categoryB”] relations

/home-page → gives all the data which is present,

but i need only specific fields from the model and its relations

typical response would look like this.

{
 "message":"hello",
 "categoryA":{
             name:"abc",
             image:"/uploads.."
             },
"categoryB":{
             name:"xyz",
             image:"/uploads..."
            }
}

this post How do I return only selected certain fields in Strapi? - Stack Overflow solves half of my problem but i am not aware how to use populate option with this approach to access specific fields from relations.

    module.exports = {
       findPaths: async ctx => {
           const result = await strapi
               .query('home-page')
               .model.fetchAll({ columns: ['message'] }) // here we wait for one column only
           ctx.send(result);
       }
    }

Thank you

2

Mongoose ORM:

strapi.query('products').model.find({}).select({
    title: 1, //include title field
    body: 1, //include body field
    orders: 1 //include title field
}).populate('orders', 'amount'); //populate 'orders' relation and populate its 'amount' field

Will include only id,title, body fields and Orders relation from products model. For Orders relation, we populate only the amount field.

Result:
image

Bookshelf ORM:

strapi.query('products').model.fetchAll({
      columns:['id','title','body'], //populate id, title, body fields for products
      //Please note that its mandatory to get `id` in 'Products' columns, otherwise you won't be able to get Orders relation. 
      withRelated: [
        {
          'orders': qb => { //populate 'Orders' relation
            qb.columns([
              'orders.amount' //populate only the 'amount' field of the 'Orders'
            ]);
          },
        },
      ],
    });

Will include only id,title, body fields and Orders relation from Products model. For Orders relation, we populate only the amount field.

Result:
image

2 Likes
3

Thanks for the detailed response.
I could somehow arrive at this solution with little modification as per my database schema relations.

final working query for me is .

  find: async (ctx) => {
        const result = await strapi.query("subcategory").model.fetchAll({
          columns: ["id", "name","basecategory"],
          withRelated: [
            {
              'basecategory': qb => {qb.columns("id","name")},
              'image': qb => {qb.columns('id','url')},
            },
          ],
        });
        ctx.send(result)
     },

The only issue i left with is, populating the image relation.

subcategory

  • id
  • name
  • desc
  • image->has_one_relation
  • basecategory->has_one_relation

This is the current output response i am getting

[
  {
    "id": 1,
    "name": "esso1",
    "basecategory": {
      "id": 1,
      "name": "Esso"
    },
    "image": {
      "id": 6,
      "name": "esso.png",
      "alternativeText": "",
      "caption": "",
      "width": 416,
      "height": 260,
      "formats": {
        "thumbnail": {
          "ext": ".png",
          "url": "/uploads/thumbnail_esso_6e3f8e59fb.png",
          "hash": "thumbnail_esso_6e3f8e59fb",
          "mime": "image/png",
          "name": "thumbnail_esso.png",
          "path": null,
          "size": 60.33,
          "width": 245,
          "height": 153
        }
      },
      "hash": "esso_6e3f8e59fb",
      "ext": ".png",
      "mime": "image/png",
      "size": 138.21,
      "url": "/uploads/esso_6e3f8e59fb.png",
      "previewUrl": null,
      "provider": "local",
      "provider_metadata": null,
      "created_by": 1,
      "updated_by": 1,
      "created_at": "2020-11-08T13:25:35.286Z",
      "updated_at": "2020-11-08T13:25:35.304Z"
    }
  }
]

i tried all the variations to get only the image url field but i coudn’t make it.

4

Hello,

Can you share your ./api/*/models/*.settings.json file for subcategory, please? So I could reproduce it locally.

5

Hi, Here is the models/*.settings.json file for subcategory

{
  "kind": "collectionType",
  "collectionName": "sub_category",
  "info": {
    "name": "subcategory",
    "description": ""
  },
  "options": {
    "increments": true,
    "timestamps": true,
    "draftAndPublish": false
  },
  "attributes": {
    "name": {
      "type": "string"
    },
    "desc": {
      "type": "richtext"
    },
    "spec_fields": {
      "type": "json",
      "private": true
    },
    "misc_fields": {
      "type": "json",
      "private": true
    },
    "catalog": {
      "collection": "file",
      "via": "related",
      "allowedTypes": [
        "files"
      ],
      "plugin": "upload",
      "required": false
    },
    "product_image": {
      "collection": "file",
      "via": "related",
      "allowedTypes": [
        "images"
      ],
      "plugin": "upload",
      "required": false
    },
    "image": {
      "model": "file",
      "via": "related",
      "allowedTypes": [
        "images"
      ],
      "plugin": "upload",
      "required": false
    },
    "basecategory": {
      "model": "basecategory"
    },
    "bc": {
      "type": "integer",
      "default": 2,
      "max": 2,
      "min": 2
    }
  }
}
6

I am not sure that it is possible to access url from image relation.
As the relation reference is “image” and media storing service is done with upload plugin, upload plugin is using two tables to handle media, 1.upload_file and 2.upload_file_morph.

maybe this multi table handling for media with upload-plugin might not have handled with bookshelf. or query need to be written in different way which i am not aware of.

However, i ended up using knex for the custom output which i was looking for, but here i ended up with bit complex query,

        const knex = strapi.connections.default
        const resp = await knex
          .select(
            "upf.id",
            "base.id",
            "base.name",
            knex.raw(
              "array_agg(json_build_object('id',sub.id,'name',sub.name)) as subcategory, upf.formats->'thumbnail' as thumbnail"
            )
          )
          .from("base_category as base")
          .leftJoin("sub_category as sub", "base.id", "sub.basecategory")
          .leftJoin("upload_file_morph as upfm", "base.id", "upfm.related_id")
          .leftJoin("upload_file AS upf", "upfm.upload_file_id", "upf.id")
          .where("upfm.related_type", "base_category")
          .where("upfm.field", "image")
          .groupBy("base.id", "upf.id");
        return resp

it gives me the following desired output for one of my api, where i require only thumbnail from image relation.

[
  {
    "id": 3,
    "name": "base3",
    "subcategory": [
      {
        "id": null,
        "name": null
      }
    ],
    "thumbnail": {
      "ext": ".png",
      "url": "/uploads/thumbnail_control_Transformers_4bc33e997e.png",
      "hash": "thumbnail_control_Transformers_4bc33e997e",
      "mime": "image/png",
      "name": "thumbnail_controlTransformers.png",
      "path": null,
      "size": 22.58,
      "width": 183,
      "height": 156
    }
  }
]
7

Actually, you can retrieve images by using withRelated, but .columns() seems to have no effect here on the morph relationship. As it returns all of the columns in any case.

withRelated: {
        images: qb => { //includes images relations
          qb.columns('url'); //doesn't return only URL. returns all fields..
        },
      }

Also, you could filter images and get the desired URL by using only lodash functions. By adding .then() after .fetchAll()

let entities = await strapi.query('articles').model.fetchAll({
      columns: ['id', 'title', 'body'],
      withRelated: ['images'],
    }).then(data => {
      let output = data.toJSON();
      output = _.map(output, (item) => {
        item.imageUrl = _.get(item, ['images', 'formats', 'thumbnail', 'url']);
        delete item.images;
        return item;
      });
      return output;
    });

Result:

[
    {
        "id": 1,
        "title": "fweq",
        "body": "fwqef"
    },
    {
        "id": 2,
        "title": "fewqf",
        "body": "fqwf",
        "imageUrl": "/uploads/thumbnail_photo_2020_03_29_16_28_23_3_bcf552acd3.jpg"
    },
    {
        "id": 3,
        "title": "fwqefqwe",
        "body": "fw"
    }
]
1 Like
8

facing error with _.

9

Hi,i want fetch only few columns under collection and i m handling this in controller.

Above query is not working for me.

1 Like
10

Can you provide the current state of your code and errors?

11

Can you provide your error traceback?

12

const _ = require(“lodash”);

13

If anyone would like to find a record that returns only certain columns and no population, try
Bookshelf ORM:

const author = await strapi.query('post').model.forge({ id: ctx.params.id}).fetch({
      columns: ['id', 'title', 'author'],
      withRelated: []});

Without withRelated:[] option strapi will populate automatically, that in many usecases you would not like to.