thing_image.go 2.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125
  1. package repositories
  2. import (
  3. "context"
  4. "fmt"
  5. sq "github.com/Masterminds/squirrel"
  6. "git.dmitriygnatenko.ru/dima/homethings/internal/models"
  7. )
  8. const thingImageTableName = "thing_image"
  9. type ThingImageRepository struct {
  10. db DB
  11. }
  12. func InitThingImageRepository(db DB) *ThingImageRepository {
  13. return &ThingImageRepository{db: db}
  14. }
  15. func (r ThingImageRepository) Add(ctx context.Context, req models.AddThingImageRequest) error {
  16. q, v, err := sq.Insert(thingImageTableName).
  17. PlaceholderFormat(sq.Dollar).
  18. Columns("thing_id", "image").
  19. Values(req.ThingID, req.Image).
  20. ToSql()
  21. if err != nil {
  22. return fmt.Errorf("build query: %w", err)
  23. }
  24. _, err = r.db.ExecContext(ctx, q, v...)
  25. if err != nil {
  26. return fmt.Errorf("exec: %w", err)
  27. }
  28. return nil
  29. }
  30. func (r ThingImageRepository) Get(ctx context.Context, id uint64) (*models.Image, error) {
  31. q, v, err := sq.Select("id", "image", "thing_id", "created_at").
  32. From(thingImageTableName).
  33. PlaceholderFormat(sq.Dollar).
  34. Where(sq.Eq{"id": id}).
  35. ToSql()
  36. if err != nil {
  37. return nil, fmt.Errorf("build query: %w", err)
  38. }
  39. var res models.Image
  40. err = r.db.GetContext(ctx, &res, q, v...)
  41. if err != nil {
  42. return nil, fmt.Errorf("get: %w", err)
  43. }
  44. return &res, nil
  45. }
  46. func (r ThingImageRepository) GetByThingID(ctx context.Context, id uint64) ([]models.Image, error) {
  47. var res []models.Image
  48. q, v, err := sq.Select("id", "image", "thing_id", "created_at").
  49. From(thingImageTableName).
  50. PlaceholderFormat(sq.Dollar).
  51. Where(sq.Eq{"thing_id": id}).
  52. OrderBy("created_at DESC").
  53. ToSql()
  54. if err != nil {
  55. return nil, fmt.Errorf("build query: %w", err)
  56. }
  57. err = r.db.SelectContext(ctx, &res, q, v...)
  58. if err != nil {
  59. return nil, fmt.Errorf("select: %w", err)
  60. }
  61. return res, nil
  62. }
  63. func (r ThingImageRepository) GetByPlaceID(ctx context.Context, id uint64) ([]models.Image, error) {
  64. var res []models.Image
  65. q := "WITH RECURSIVE cte (id, parent_id) AS (" +
  66. "SELECT id, parent_id " +
  67. "FROM " + placeTableName + " " +
  68. "WHERE id = $1 " +
  69. "UNION ALL " +
  70. "SELECT p.id, p.parent_id " +
  71. "FROM " + placeTableName + " p " +
  72. "INNER JOIN cte ON p.parent_id = cte.id " +
  73. ")" +
  74. "SELECT ti.id, ti.image, ti.thing_id, ti.created_at " +
  75. "FROM cte, " + placeThingTableName + " pt, " + thingImageTableName + " ti " +
  76. "WHERE pt.place_id = cte.id AND pt.thing_id = ti.thing_id " +
  77. "ORDER BY ti.created_at DESC"
  78. err := r.db.SelectContext(ctx, &res, q, id)
  79. if err != nil {
  80. return nil, fmt.Errorf("select: %w", err)
  81. }
  82. return res, nil
  83. }
  84. func (r ThingImageRepository) Delete(ctx context.Context, id uint64) error {
  85. q, v, err := sq.Delete(thingImageTableName).
  86. PlaceholderFormat(sq.Dollar).
  87. Where(sq.Eq{"id": id}).
  88. ToSql()
  89. if err != nil {
  90. return fmt.Errorf("build query: %w", err)
  91. }
  92. _, err = r.db.ExecContext(ctx, q, v...)
  93. if err != nil {
  94. return fmt.Errorf("exec: %w", err)
  95. }
  96. return nil
  97. }