[OT] Idea to get all columns from multiple tables in DB

[Steven Schveighoffer]

I have a database serialization package that I use to fetch data from a 
database. The column names are all identified or tagged appropriately, 
so I just use:

connection.query("SELECT * FROM user").byItem!User;

and it serializes the data from the resulting rows into the struct User 
type for consumption as a range.

However, if there's a join, it poses a problem. Let's say I have an 
Equipment item, which is owned by a user. I can select both the 
equipment and the owner via:

SELECT * FROM equipment left join user on (user.id = equipment.owner_id)

However, this causes problems because I get duplicate columns (e.g. name 
might be in both, or id). Which one goes with which?

I have a mechanism to deal with this. I can prefix each column name with 
a prefix, and then specify that on the byItem function with a uda-like 
directive:

connection.query("SELECT e.*, u.name AS user_name, u.id AS user_id, 
u.email AS user_email FROM ...").byItem!(Equipment, 
DB.colPrefix!"user_", User);

used like so:

foreach(equip, user; dbSequence) ...

But this requires you spell out every single column name in an extremely 
verbose manner, and you can't use that cool '*' selector which is 
somewhat future-proof.

But I realized I can put a "divider" in between the two table .* 
selectors, and then even though I have duplicate names, I can detect the 
divider and, and know which duplicate-named columns apply to which:

connection.query("SELECT e.*, 'user_' as __prefix, u.* FROM 
...").byItem!(Equipment, DB.colPrefix!"user_", User)

In my byItem function, when I'm mapping column names to actual fields at 
the beginning of iteration, I see a column named __prefix, and I use the 
value to prepend to all subsequent column names. And I think this will 
work beautifully!

Is this a technique that is used by anyone regularly? If so, what is it 
called? I'm very excited about this, because I want to minimize DB round 
trips, and also fetch related items as they come in, instead of 
serializing all of one type to an array and then fetching the rest with 
individual queries, but I didn't want to type out a lot of SQL (or 
generate it based on possibly stale struct field names), and have to 
maintain it any time I change something. This solves all the problems in 
a neat way.

-Steve