PDO Auto-aliasing when fetching?

Question

I have the following two tables:

foo:

ID     NUMBER(38)
DATA   VARCHAR2(10)

bar:

ID     NUMBER(38)
FOO_ID NUMBER(38)
DATA   VARCHAR2(10)

When using PDO to issue the following query:

SELECT * FROM foo f INNER JOIN bar b ON (f.id = b.foo_id)

Is there any way to get back all of the columns from the join in some kind of auto-aliased format (e.g. "FOO.ID", "FOO.DATA", "BAR.ID", etc.) so that I don't need to specify and alias every single column in the query?

I've read all of the documentation on the various fetch modes, and experimented with most of the flags/options, but still can't seem to find what I'm looking for.

Update:

Using PDO::FETCH_ASSOC, the columns from foo seem to be overwritten by the columns from bar:

array(3) {
  ["ID"]=>
  string(1) "1"
  ["DATA"]=>
  string(5) "bar 1"
  ["FOO_ID"]=>
  string(1) "1"
}

Using PDO::FETCH_NUM, the columns from both foo and bar appear, in order, but without any way to identify which columns came from which tables, except by knowing the number of columns in each table and exact order of those columns (error prone):

array(5) {
  [0]=>
  string(1) "1"
  [1]=>
  string(5) "foo 1"
  [2]=>
  string(1) "1"
  [3]=>
  string(1) "1"
  [4]=>
  string(5) "bar 1"
}

Using PDO::FETCH_BOTH, we seemingly run into the same problems as PDO::FETCH_ASSOC and PDO::FETCH_NUM combined, and create a horribly unintelligible result set:

array(8) {
  ["ID"]=>
  string(1) "1"
  [0]=>
  string(1) "1"
  ["DATA"]=>
  string(5) "bar 1"
  [1]=>
  string(5) "foo 1"
  [2]=>
  string(1) "1"
  ["FOO_ID"]=>
  string(1) "1"
  [3]=>
  string(1) "1"
  [4]=>
  string(5) "bar 1"
}

IDEALLY, the result set should look similar to this:

array(5) {
  ["FOO.ID"]=>
  string(1) "1"
  ["FOO.DATA"]=>
  string(5) "foo 1"
  ["BAR.ID"]=>
  string(1) "1"
  ["BAR.FOO_ID"]=>
  string(1) "1"
  ["BAR.DATA"]=>
  string(5) "bar 1"
}

You can always refer a column with it's qualified name `tableName.columnName`, if this is, what you meant (it's not a alias though). — KingCrunch, Aug 30 '12 at 15:05
It's sort of a good habit to get into explicitly listing columns in select queries instead of `SELECT *` anyway... — Michael Berkowski, Aug 30 '12 at 16:00
@MichaelBerkowski: Unfortunately, I don't have that luxury in this instance. — FtDRbwLXw6, Aug 30 '12 at 16:05

score 2 · Answer 1 · answered Aug 30 '12 at 15:02

2

No, you can't, at least you need to do:

SELECT f.*, b.* FROM foo f INNER JOIN bar b ON (f.id = b.foo_id)

answered Aug 30 '12 at 15:02

xdazz

158,678
38
247
274

This does not seem to give the desired result in PDO, though. None of the keys when you fetch are labeled `f.id` or similar. In fact, it seems to actually overwrite the non-numeric keys and produce really strange result sets. – FtDRbwLXw6 Aug 30 '12 at 15:47

score 1 · Accepted Answer · answered Sep 26 '12 at 21:09

1

There appears to be no way to do this behind the curtains. Explicit aliasing is the only way.

answered Sep 26 '12 at 21:09

FtDRbwLXw6

27,774
13
70
107

min · Answer 3 · 2015-12-08T18:18:09.630

0

try this:

SELECT f.ID as fID, f.DATA as fDATA, b.*, FROM foo f INNER JOIN bar b ON (f.id = b.foo_id)

the idea is to change only the conflicting attributes so that there is no more conflict.

edited Dec 08 '15 at 18:18

answered Dec 08 '15 at 18:09

min

1
1

PDO Auto-aliasing when fetching?

3 Answers3

Linked