Gorm Count On Preloaded Field

Question

I am using Postgres with Go Lang & the Echo framework as my base, with that I am using Gorm to build my database queries.

So Here is my Profile Model,

type Profile struct {
  gorm.Model
  InvoiceCount     uint      `gorm:"-"`
  CompanyName      string    `gorm:"size:255"`
  CompanyNumber    string    `gorm:"size:10"`
  CompanyVatNumber string    `gorm:"size:10"`
  DateAdded        time.Time `gorm:"type:date"`
  PrimaryEmail     string    `gorm:"size:255"`
  IsActive         bool
  Invoice []*Invoice `gorm:"foreignkey:profile_fk" json:",omitempty"`
  Address []*Address `gorm:"foreignkey:profile_fk" json:",omitempty"`
} 

This is linked into my Invoice model, which I am trying to do a count with on a preload. I added the InvoiceCount uint has a means of adding the count into this model.

So this is what I have tied,

dbCon().
  Preload("Invoice", func(db *gorm.DB) *gorm.DB {   
    return db.Count(&profile)
  }).
  Find(&profile).
  RecordNotFound()

However, I apart from this not working, it returns the following error: (pq: zero-length delimited identifier at or near """").

I am trying to do this with a simple query, but this that wrong? Do I need to just loop around all my profiles and add a count to each? Or drop down to a raw SQL query with a sub select?

Thanks,

Answer 1

I have built this Raw SQL query,

dbConn().
    Raw("SELECT p.*, count(i.id) as invoice_count FROM profiles p left join invoices i on i.profile_fk = p.id group by p.id").
    Scan(&result)