Postgresql check if string contains many letters

Question

So right now i have a column in my table of School classes called days which contains the days that specific section will run on, like 'TR' would be that it runs on Tuesday and Thursday, and 'MWF' will be it runs on Monday, Wednesday, and Friday.

i want the user to be able to filter by days it runs, so if a class runs 3 times, MT,MW,TF i want the user to be able to enter MTW, and get the first 2. But the only way i could figure out how to query it correctly, is to make the input string to an array of all possibilities so MTW would become M,T,W,MT,MW,TW,MWT and just see if days is in that array. it works but i am wondering if there is a better way.

Yes, it is still heavily in development. what would you suggest — Tomer Shemesh
– Tomer Shemesh, Commented Aug 18, 2015 at 18:11

FuzzyTree · Accepted Answer · 2015-08-18 18:37:35Z

3

I recommend creating another table, class_days, which contains each day a class will run.

So MWT stored in days would instead be stored as 3 rows in a separate table

class_days
class_id, day
1,M
1,W
1,T

Then you can use not exists to select classes that do not have classes scheduled on a day not given by a user (therefore all classes are scheduled by the given days)

select c.id from classes c
where not exists (
  select 1 from class_days cd
  where cd.class_id = c.id
  and cd.day not in ('M','W','T')
)

The query above assumes that every row in classes has at least 1 corresponding row in class_days. If that's not the case, then add an exists condition to ensure you're not returning classes that do not have any days setup.

select c.id from classes c
where not exists (
  select 1 from class_days cd
  where cd.class_id = c.id
  and cd.day not in ('M','W','T')
) and exists (
  select 1 from class_days cd
  where cd.class_id = c.id
)

These queries can take advantage of a composite index on class_days(class_id,day)

edited Aug 18, 2015 at 18:37

answered Aug 18, 2015 at 18:16

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tomer Shemesh Over a year ago

Hmm i was trying to avoid having another table. How do you think the speed will compare to this vs the way i am doing it now? since now i will have another table with more than twice the rows as my class table

FuzzyTree Over a year ago

@TomerShemesh with a composite index on class_id,day the additional table should run much faster than what you have for the query of retrieving all classes that are given on a specific set of days

Tomer Shemesh Over a year ago

Ok. would it be bad practice to also have my days column still in classes so that when i am just looking up classes by name, i wont need to do a join to know what the days are?

FuzzyTree Over a year ago

@TomerShemesh it depends. if you're doing it for performance reasons then it's a valid to store data to avoid a lookup - but this only applies to very large systems - in your case I would advise against storing the days separately to avoid a join because then you have to maintain 2 sets of code for creating/updating/deleting days

Tomer Shemesh Over a year ago

The table only has 15,000 rows so i assume that its small enough where it would be better to not have the column

|

Juan Carlos Oropeza · Accepted Answer · 2015-08-18 18:31:24Z

0

You need three tables

Classes
ClassID Primary Key
Days
DayID Primary Key
ClassDay
ClassID Foreing Key
DayID Foreing Key

UPDATE

Or you could use bitwise operator

Lets say Monday =1, Tuesday = 2, Wensday = 4, Thursday = 8, Friday = 16

In case user want MTW.

You update class

UPDATE Class
SET DAY = 1 + 2 + 4

and search

SELECT *
FROM Class 
WHERE 
     DAY & 1 
AND  DAY & 2
ADN  DAY & 4

edited Aug 18, 2015 at 18:31

answered Aug 18, 2015 at 18:14

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

3 Comments

Juan Carlos Oropeza Over a year ago

@TomerShemesh I think the third table is the way to go, but I add the bitwise solution for your consideration.

Tomer Shemesh Over a year ago

i dont see why i need 3 tables when i think i would only need two as seen above

Juan Carlos Oropeza Over a year ago

Yes, you may skip DAYS table for your case. I just give you the general normalized table. But is the same answer as Fuzzy. Or you create a field to store days with bitwise operator and only need one table.

Collectives™ on Stack Overflow

Postgresql check if string contains many letters

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related