[OT] Idea to get all columns from multiple tables in DB
Steven Schveighoffer
schveiguy at gmail.com
Wed Jan 8 23:01:14 UTC 2020
On 1/7/20 9:28 PM, Jesse Phillips wrote:
> What I realized after watching your talk is that I've generally married
> the structure to the query rather then the tables or data.
>
> Select user.name, equip.product...
>
> struct Ownership {
> string usersname;
> string product;
> }
>
> I this situation the assignment comes from position and not by column
> name. You lose the * as you mentioned.
In my project, I only do generation of SQL for updating or inserting
rows. For fetching elements, I write the SQL (by hand or through a sql
building system I wrote) and then use the byItem serializer to get the
records.
Upon the first record, I use the column names in the list to map the
field order to the column order. So I don't have to worry about columns
being removed/added, etc. And then for each row fetched, everything is
extremely fast because the column order/names don't change.
And yeah, I've done stuff like the above when I have for instance to
fetch statistical data. But for the most part, I have one struct for
each table row that I fetch. In my code, if you don't fetch all the
columns, it just doesn't fill those in.
But I hadn't thought of a good way until now to rename the columns
fetched so they can be serialized correctly when there are conflicting
column names without spelling all the columns out (which I hate). In
fact, I might change this later to avoid having to fetch the column
prefix for every row (I only need it for the first row), but this
technique is definitely more efficient than fetching all of one type
first (probably in a single query), and then fetching each related item
one at a time.
-Steve
More information about the Digitalmars-d
mailing list