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

Questions and Answers
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
    }
  }
]