0

In Doctrine, is it possible to bind the name of a parameter (contrary to binding the value of a parameter)?

Why I need it

There is a table having 7 boolean columns, one for each day of the week: monday, tuesday, etc. (these correspond to the structure of the calendar entity defined by GTFS, https://gtfs.org/schedule/reference/#calendartxt).

Given a day of the week (say, monday), I want to get all rows, which are available on Mondays, i.e.:

$statement = $this
    ->getEntityManager()
    ->getConnection()
    ->prepare('
        SELECT id
        FROM calendar
        WHERE monday = 1
    ');

Generally, I want to be able to supply the name of a day in that query, which I can do simply by:

->prepare("
    SELECT id
    FROM calendar
    WHERE $dayName = 1
");

I wonder whether it's possible to use the parameter binding for the names of parameters, i.e. something like

$statement = $this
    ->getEntityManager()
    ->getConnection()
    ->prepare('
        SELECT id
        FROM calendar
        WHERE :dayName = 1
    ');

$statement->bindValue('dayName', $dayName);

which does not work, see below.

What I tried

#1

WHERE :dayName = 1

which translates to the following SQL query:

SELECT calendar.id
FROM calendar
WHERE 'monday' = 1

and, because that condition is never true, returns an empty set, [].

#2

WHERE `:dayName` = 1

SELECT calendar.id
FROM calendar
WHERE `'monday'` = 1

Column not found: 1054 Unknown column ''monday'' in 'where clause'

#3

WHERE ":dayName" = 1

# no query

Invalid parameter number: number of bound variables does not match number of tokens

iloo
  • 926
  • 12
  • 26
  • 1
    Parameter binding only supports binding values, not table names, columns etc – Jaquarh Feb 18 '23 at 12:31
  • 1
    FYI, this looks like an N+1 problem. Normalize your database and you won't have this issue – Jaquarh Feb 18 '23 at 12:44
  • Closely related, but not quite duplicate because Doctrine *may* have some abstractions for things like this: https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter – IMSoP Feb 18 '23 at 13:01
  • @Jaquarh, I checked what the "N+1" problem was, not sure how it relates to this case. Even if I needed to check values of all 7 columns, I could do it with a single query using multiple `AND`s. No need for N queries there. – iloo Feb 20 '23 at 15:04
  • @Jaquarh, you mentioned normalization. Did you mean something like converting multiple boolean fields to a single `BIT` field? – iloo Feb 20 '23 at 15:06

1 Answers1

3

This is of course not possible.

Within the statement, placeholders can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it. The parameter markers should not be enclosed within quotes, even if you intend to bind them to string values. Parameter markers can be used only where expressions should appear, not for SQL keywords, identifiers, and so forth.

In short: you can use parameter markers for literals (string, numbers, ..) only

If you can't change your database design I would recommend using another SQL statement with a simple bit check:

prepare("select id, (monday + 
                     tuesday * 2 + 
                     wednesday * 4 + 
                     thursday * 8 + 
                     friday * 16 + 
                     saturday * 32 + sunday *64) as day
from calendar having (day) & (1 << :daynumber)")

Now you can simply check if a service is available on a specific weekday, by binding the daynumber (monday=0, tuesday=1, .. sunday=6).

Georg Richter
  • 5,970
  • 2
  • 9
  • 15
  • Actually, I *can* change the DB design. I guess I could combine those columns into a single column of type `BIT`, if this is what you had in mind. This would require creating a custom type for Doctrine, but I could give it a try. – iloo Feb 20 '23 at 15:19
  • That bitmask seems massively over-engineered; if you want a derived column, you a `CASE` would be more readable, e.g. `WHERE 1 = CASE :daynumber WHEN 1 then monday WHEN 2 then tuesday ... END` – IMSoP Feb 20 '23 at 15:41
  • @iloo The normal *schema design* solution to this would be *normalisation*: move the values into a secondary table, with `daynumber` as a real column, and (up to) 7 rows for each `calendar` row. – IMSoP Feb 20 '23 at 15:43
  • I think a bitmask would be the better solution, since a service might operate on multiple days and normalization would generate a lot of overhead. Another option would be to use SET datatype. – Georg Richter Feb 20 '23 at 16:01