0

I have a nestjs api using typeorm and postgres where I need to query for any users that have the days 'Mo,Sa'. I am open to better ways of organizing the dayswillworkcolumn if a comma delimited string is not a good way to go about doing this.

userId name dayswillwork
1 Joe "Su,Mo,Tu"
2 Amber "Fr,Sa,Su"
3 Craig "Su,Tu,Th,Fr"
4 Steve "Mo,Tu,We,"

I have tried...

query.where('employee.dayswillwork LIKE :days', {
        days: days,
      });

and...

query.where(":dayswillwork = ANY ( string_to_array(employee.dayswillwork , ','))", { dayswillwork : dayswillwork })

but neither one is returning anything.

Jacob
  • 61
  • 2
  • look at the queries that each approach produces and try to manage that to select from the db what you need – Micael Levi Apr 20 '21 at 22:59
  • "I am open to better ways of organizing the dayswillworkcolumn if a comma delimited string is not a good way to go about doing this." -- Hve a look here: ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes it is.). – sticky bit Apr 21 '21 at 00:59
  • Thanks. I am new to databases so I am trying to figure the design on my own, but I want to implement best practice too so I will switch it from a comma delimited string to its own table. – Jacob Apr 21 '21 at 04:51

1 Answers1

0

I was able to get it to work like this...

query.where('employee.dayswillwork SIMILAR TO  :days', {
        days: `%(${days.split(',').join('|')})%`,
      });
Jacob
  • 61
  • 2