I'm trying to fetch a single category using slug

http://localhost:1337/api/categories/{slug}

In my controller:

 async findOne(ctx) {     
     const {id : slug} = ctx.params     
         const response = await strapi.db
           .query("api::category.category")
           .findOne({
             where: { slug: slug },
             populate: {
               blogs: {
                 select: ["id", "title"],
                 orderBy: ["id"],
               },
             },
           }); 

This works fine... but when add another field

select: ["id", "title", "image"], 

I get the error

error: select distinct t1.blog_order, t0.id, t0.id, t0.title, t0.image, t1.category_id from blogs as t0 left join categories_blogs_links as t1 on t0.id = t1.blog_id where (t1.category_id in (2)) order by t0.id asc, t1.blog_order asc - no such column: t0.image SqliteError: select distinct t1.blog_order, t0.id, t0.id, t0.title, t0.image, t1.category_id from blogs as t0 left join categories_blogs_links as t1 on t0.id = t1.blog_id where (t1.category_id in (2)) order by t0.id asc, t1.blog_order asc - no such column: t0.image

but there IS a field called "image"

CodePudding user response：

really fun ussage of populate, have't thought actually that you can orderBy and select inside populate hmm.

The problem you have it's likely due to that you have to also explicitly populate relations inside. Try this:

.findOne({
   where: { slug: slug },
   populate: {
     blogs: {
       populate: ["image"],
       select: ["id", "title", "image"],
       orderBy: ["id"],
     },
   },

CodePudding user response：

The reason I'm getting this error is because the media fields are not stored in database, so the sqlite is looking for a column that does not exists. So we may have to populate it seperately.

const {id : slug} = ctx.params     
         const data = await strapi.db
           .query("api::category.category")
           .findOne({
             where: { slug: slug },
             populate: {
               blogs: {               
                 select: ["id", "title", "publish_date","slug"],
                 orderBy: ["publish_date"],
                 populate:{"image" : true}
               },
             
             },
           });