provide field name dynamically in where clause in query

Question

I have a table that reads day_sunday, day_monday, etc. I need what data is stored for today's column if the value is true. For example 17 may is Wednesday so the query will be

For example 17 may is a Wednesday so the query will be

select * from table where day_wednesday

Similarly for tomorrow the query won't change except it should use field day_thursday in the where clause.

I have written the query below but it is not working.

select restaurant_id from day_matrix where "day_"||lower(dayname(now())) = 1;

This data needs to get joined in another query.

This is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. Your first paragraph is not clear about columns or values or conditions or queries. Don't use unexplained symbols, write clear full prose. — philipxy, May 19 '23 at 06:56

score 0 · Accepted Answer · answered May 17 '23 at 12:55

Columns and expressions in the WHERE clause must be fixed at the time you prepare the query. There's no way to parameterize columns or expressions in SQL.

The way most people solve this problem is by writing application code to add terms to your WHERE clause based on application logic and variables. In other words, append fragments of SQL syntax to a string inside code constructs like if (). The specific methods for doing this depend on the programming language you're using.

To help in this technique, sometimes frameworks provide a query builder API.

Reference: https://stackoverflow.com/a/7476727/4476745

score 0 · Answer 2 · answered May 17 '23 at 13:04

Fix your data model! These columns should be represented as rows in a separate table, as in:

create table restaurant (
    id int primary key
    -- more columns for other restaurant attributes
);

create table restaurant_schedule (
    restaurant_id   int not null references restaurant (id),
    week_day        int not null,   -- weekday as a number (eg : Monday=0 .. Sunday=6)
    is_active       int not null,   -- 0 or 1
    primary key (restaurant_id, week_day)
);

Now, looking up restaurants that are active today is as simple as a join:

select r.*
from restaurant r
inner join restaurant_schedule rs on rs.restaurant_id = r.id
where rs.week_day = weekday(current_date) and is_active = 1

If for some reason you are stuck with your current schema, then a simple solution is boolean logic - but it requires enumarating all column names in the query:

select *
from mytable
where 1 = case dayname(current_date)
    when 'Monday'  then day_monday
    when 'Tuesday' then day_tuesday
    ...
    when 'Sunday'  then day_sunday
end

provide field name dynamically in where clause in query

2 Answers2