0

I'm looking for a way of doing this without listing out all of the fieldnames and using AS on each one. Maybe this is more of a mysql question. To be clear, I offer an example:

table1: users
+-id-+------name------+-preferred_moose_id-+-preferred_preparation_id-+
| 20 | Chester Chore  |          1         |             3            |
| 72 | George Garnish |          2         |             2            |
| 88 | Liver Laddykin |          2         |             3            |
+----+----------------+--------------------+--------------------------+

table2: moose_types
+-id-+------name------+-------size------+---------------diet--------------+
| 1  | Canadian Moose | Pretty Big      | Corn and snow                   |
| 2  | Red Moose      | Not as big      | Dolphins                        |
| 3  | Horses         | Size of a horse | Oats, cheese, okra, and farmers |
+----+----------------+-----------------+---------------------------------+

table3: preparation_types
+-id-+-------name-------+-difficulty-+
| 1  | Boiled           |     3      |
| 2  | Stuffed          |     5      |
| 3  | Butterflied      |     9      |
+----+------------------+------------+

If I have SQL like this:

SELECT users.*, moose_types.*, preparation_types.*
  FROM users
 INNER JOIN moose_types ON moose_types.id = users.preferred_moose_id
 INNER JOIN preparation_types ON preparation_types.id = users.preferred_preparation_id
 WHERE users.id = 88

And then I dump out the query, I end up with something like this:

+---diet---+-difficulty-+-id-+-id-+-id-+------name------+------name------+------name------+-preferred_moose_id-+-preferred_preparation_id-+-------size------+
| Dolphins |     9      | 88 | 88 | 88 | Liver Laddykin | Liver Laddykin | Liver Laddykin |         2          |             3            | Not as big      |
+----------+------------+----+----+----+----------------+----------------+----------------+--------------------+--------------------------+-----------------+

Notice the data being overwritten/duplicated. Liver Laddykin doesn't like to eat Liver Laddykinned Liver Laddykin, no matter if it's a difficulty of 9 or not. This is a safeguard intentionally done by CF, and I can kind-of understand why they did it this way. However, there has to be ways to avoid this. The first of which would be using the AS operator in the query and manually listing out all of the field names. BUT

Again, I iterate: I want to avoid listing out all of the fields manually and using AS on each one, as this would complicate things a little bit, unless it can be done without listing the fields and can be applied either by coldfusion, or done by MySQL. If there is NO solution, I'm fine with that, but I know I'm not a CF expert and certainly NOT a mySQL expert. Please feel free to point out errors or something I'm overlooking even if it seems trivial to you.

UPDATE Thanks so far to the commenters! Unfortunately, changing field names is not an option (though it would definitely work).

Clarification of actual question

What I'm looking for is some way that either ColdFusion or MySQL can yield me different field names (and thus differentiable) automatically or programmatically, without having to manually list out each of the fields (even trying to avoid using a cfloop, I know I'm making this hard), and declare it using AS.

I'm thinking that there may be some advanced SQL out there that I'm not aware of.

Groovetrain
  • 3,315
  • 19
  • 23
  • How do you "dump out" the query? I re – ypercubeᵀᴹ Mar 22 '11 at 19:20
  • @ypercube one of cf's built-in tags, `` – Groovetrain Mar 22 '11 at 19:23
  • What I don't understand is how you get three ids, three name fields. etc. Unless you have something like: `SELECT users.*, users.*, users.*, ...`. – ypercubeᵀᴹ Mar 22 '11 at 19:26
  • @ypercube if you've never used ColdFusion, then it makes sense that you don't understand (this is not a flame, please don't misunderstand my tone :) ). This seems to be specific to CF, as I've never encountered it in other programming languages before. The three id's represent the id of the `FROM` table, and then each of the two joined tables. The same occurs with the `name` field. – Groovetrain Mar 22 '11 at 19:34
  • OK, no misunderstanding. I was about to add "or unless I really know nothing about ColdFusion..." :) – ypercubeᵀᴹ Mar 22 '11 at 19:44
  • @Groovetrain - AFAIK, you must give the columns unique names using `AS`. Otherwise, CF has no way of knowing which value you want when you say #my_query.id#. – Leigh Mar 22 '11 at 19:45
  • What I do know is that the duplication of columns is NOT done by MySQL but by CF. – ypercubeᵀᴹ Mar 22 '11 at 19:46
  • @Leigh right, which is why I'm asking. I'm building some ORM stuffs and want to avoid listing out each and every field name I need, for the sake of reusability. – Groovetrain Mar 22 '11 at 19:49
  • Ah, I see the light after Leigh's comment. You could change the field names in the tables (not with 'AS`, in the table create scripts), using say `mt_id, mt_name` in table `moose_types`. So no two fields have the same name, no matter which table they belong. – ypercubeᵀᴹ Mar 22 '11 at 19:50
  • @ypercube I agree, and it is quite annoying. – Groovetrain Mar 22 '11 at 19:50
  • @ypercube This is a valid solution, but unfortunately, the database can't be altered. – Groovetrain Mar 22 '11 at 19:52
  • @Groovetrain - I do not use much ORM. But for basic queries, AFAIK there is no way to avoid using aliases. It would be like trying to have one variable represent three different values. Not possible - it would be very confusing if it were. – Leigh Mar 22 '11 at 19:56
  • @Groovetrain - You can use db metadata like INFORMATION_SCHEMA.COLUMNS to generate (and manipulate) a list of columns. It would be "dynamic", though not as simple/elegant as using `AS`. Without dipping into java, those are the only two options I am aware of. – Leigh Mar 22 '11 at 20:18
  • Another solution would be (if you are allowed), to create `VIEWS` in database without thus altering the table descriptions. – ypercubeᵀᴹ Mar 22 '11 at 21:14

1 Answers1

0

For ColdFusion your column names will need to be unique, there is no way around that.

However, listing out the field names and using AS on each one isn't so bad. With a quick loop or two you can dynamically generate the SQL (no need to explicitly last things out) and you will be all set!

Aaron Silverman
  • 22,070
  • 21
  • 83
  • 103