Dbal: PDO::FETCH_KEY_PAIR Missing from FetchMode Constants

[chrisguitarguy]

There's no constant in Doctrine\DBAL\FetchMode for PDO::FETCH_KEY_PAIR.

Was this intentional? Could it be added? Happy to send a PR for this, but wasn't sure if the omission was intentional.

See also #3082 where FETCH_KEY_PAIR was also mentioned.

[morozov]

@chrisguitarguy it is intentional because this mode is only supported in PDO but not other underlying drivers. We may want to implement it across platforms in 3.0 but it requires some refactoring of the driver layer first.

[Majkl578]

You can still use the constant in 2.x though and it should work as long as you use PDO-based driver. Note that it'll emit deprecation warning now in 2.x since it's not explicitly supported a fetch mode across drivers.
See #2996 + #2958.

Marking as feature request for 3.0 where it may eventually become a supported FetchMode.

[chrisguitarguy]

it is intentional because this mode is only supported in PDO but not other underlying drivers.

Ah, I gotcha. Didn't think about non-PDO drivers.

For what it's worth, I "fixed" my own deprecation warning by just doing FetchMode::ASSOCIATIVE and using array_column to pull out what I needed. That may be a solution for other drivers, but DBAL probably doesn't have enough info about the query to do that without parsing SQL I'd guess.

[morozov]

@chrisguitarguy you probably can use FetchMode::NUMERIC and the first two columns instead without depending on the column names.

[chrisguitarguy]

Looked into this a bit more and I think I pretty much understand how it would get implemented. PDO stuff just works, but things like the mysqli, oci, and sql server drivers would need a supported added in their {DriverName}Statement classes.

Is that on the right track?

I've only every fixed stuff in DBAL for postgres and mysql, are there docs on how to run the OCI or other tests anywhere?

[morozov]

@chrisguitarguy, please hold off on implementing it. While it's technically possible, currently, there's no proper place to put the implementation. If it's implemented on the Driver\Statement level, it will have to be duplicated for every non-PDO driver (same as currently FetchMode::STANDARD_OBJECT and FetchMode::CUSTOM_OBJECT are).

As for testing on OCI, your options are:

1. Don't bother. We have OCI covered by ContinuousPHP builds.
2. Use wnameless/oracle-xe-11g as a server. You can find some instructions (for instance, this article) on building php_oci8 online.

[thomask]

Would this be the proper place to also add that PDO::FETCH_UNIQUE is "missing"? If not, I can open a separate issue.

[morozov]

@thomask let's leave it here for now.

[dave-redfern]

I guess these changes mean that PDO::FETCH_PROPS_LATE would be difficult to implement across drivers?

[morozov]

@dave-redfern the implementation itself is not difficult. The point is that we don't want to reimplement it across drivers, we want things like that to be implementable once on the wrapper level. But it requires API redesign.

[Philipp91]

This seems to be the thread for missing fetch modes :-)

+1 for PDO::FETCH_UNIQUE. However, it's not trivial, as it's usually not used alone. It can reasonably be combined with FETCH_ASSOC, FETCH_CLASS and so on. So would the new FetchMode enum also allow such combinations?

Besides those mentioned above, I also often use \PDO::FETCH_UNIQUE | \PDO::FETCH_COLUMN to retrieve a single value, e.g. a sum or sth like that. In my opinion, that's more reasonably expressed as FetchMode::SINGLE_VALUE, as there's not really any concept of columns and uniqueness when there's only one cell.

And +1 for \PDO::FETCH_KEY_PAIR, which is closely related to FETCH_UNIQUE, I'd say.

---

In fact, another way to look at this is to split off the decision whether everything should be keyed by the first column or not, which I'd find more intuitive as an API user, though it's conceptually different from PHP's PDO API. For the "value" side, there's things like ASSOCIATIVE, NUMERIC, MIXED, STANDARD_OBJECT, etc. What's currently known as COLUMN could be renamed to SINGLE_COLUMN for clarity. And for the "key" side, there's REGULAR (or ENUMERATED or just 0 mode) for the usual numerically indexed array, then there's KEYED for an array indexed by the first returned column (similar to current FETCH_UNIQUE), and then there's SINGLE_ROW which asserts that there is only row in the result and returns the plain value (not an array of values, though of course the value itself is an array in the case of ASSOCIATIVE).

So FETCH_KEY_PAIR would be KEYED + SINGLE_COLUMN. My \PDO::FETCH_UNIQUE | \PDO::FETCH_COLUMN case above would become SINGLE_ROW + SINGLE_COLUMN. And so on.

[Ocramius]

I don't have my computer at hand, so I can't find the original thread for this, but we are actually trying to reduce the supported fetch modes, as they are simple function composition patterns complicated by bitmasking, and they are trivial to implement in userland.

[Philipp91]

So when I'm using plain PDO in PHP, is there no performance benefit when I'm using FETCH_KEY_PAIR compared to just fetchin ASSOC and manually converting the result into an array mapping keys to values?

[Ocramius]

Generally not noticeable: still, I wouldn't rely on the underlying PDO layer to arrange data fir you, as the various PDO implementations are extremely quirky. Seriously, the less PDO-specific details you use, the better.

[jwage]

I am re-evaluating all issues currently associated with the doctrine/dbal 3.0.0 milestone. Removing the milestone for now on this issue while we try to narrow in on the most critical issues required to release 3.0.0. This issue is still important but it may not happen in 3.0.0