Suppose I have a very basic class in an arbitrary OOP:

class MyClass
{
int Array1[];
int Array2[];
};

I made the following 3 tables

Array1
#######################
array1_id, data, myclass_id

Array2
#######################
array2_id, data, myclass_id

MyClass
#######################
myclass_id

I simply want to read all MyClasses from the database and fill the
arrays, using 1 query and yet not send unnecessary data over the
network... I made two naive attempts, yet I fail to reach both my
"design goals" at the same time.

Version 1
=========

"SELECT * FROM MyClass M;"
foreach(MyClass m in result)
{
"SELECT * FROM Array1 A WHERE myclass_id="+m.myclass_id;
foreach(int i in result)
m.AddToArray1(i);

"SELECT * FROM Array2 A WHERE myclass_id="+m.myclass_id;
foreach(int i in result)
m.AddToArray2(i);
}

======================
Queries: 1+2*(MyClass) (Bad)
Rows: (MyClass) + (MyClass*Array1) + (MyClass*Array2) =
(MyClass)*(1+(Array1)+(Array2)) (Few)
Columns: 1-3 (Few)
Data amount over the wire: Rows*Columns (Ok)

Version 2
=========

SELECT * FROM MyClass M
LEFT JOIN Array1 USING(myclass_id)
LEFT JOIN Array2 USING(myclass_id)
ORDER BY M.myclass_id;

foreach(MyClass m in result)
{
// Greatly simplified, to the point of beeing buggy... just meant
for illustration.
if(m.myclass_id is the same as it was in the previous iteration)
{
first_m_with_a_given_class_id.AddUniqueToArray1(m. Array1);
first_m_with_a_given_class_id.AddUniqueToArray2(m. Array2);
}
}

==================
Queries: 1 (OK)
Rows: (MyClass)*(Array1)*(Array2) (Excessive amount)
Columns: 5 (Many)
Data amount over the wire: Rows*Columns (Massive)

Greatful for any assistance.
Nicklas

Sorry for replying to my own message, but thought of one optimisation
myself:

SELECT * from MyClass
LEFT OUTER JOIN
(
SELECT data as data1, null as data2, array1_id as array_id,
myclass_id from Array1
UNION
SELECT null as data1, data as data2, array2_id as array_id,
myclass_id from Array2
) arrays
USING (myclass_id)
ORDER BY myclass_id;

Would this be considered a good approach?

I've written some general remarks about database handling in
object-oriented programs in
http://www.w-p.dds.nl/article/wrtabrec.htm

Why is it bad to use two queries? Especially when they are much more
simple to parse by the server and therefore probably faster?
If you want to speed things up (not suitable for all engines, though),
take a look at the HANDLER statement.

But that is not necessary either. If the queries are a performance
problem, you probably forgot to use indexes on myclass_id. But I
sincerely doubt that you have a performance problem anyway.
However, you DO have a maintenance problem if you start writing code the
least understandable way...

Best regards

Nicklas wrote:
> Suppose I have a very basic class in an arbitrary OOP:
>
> class MyClass
> {
> int Array1[];
> int Array2[];
> };
>
> I made the following 3 tables
>
> Array1
> #######################
> array1_id, data, myclass_id
>
> Array2
> #######################
> array2_id, data, myclass_id
>
> MyClass
> #######################
> myclass_id
>
> I simply want to read all MyClasses from the database and fill the
> arrays, using 1 query and yet not send unnecessary data over the
> network... I made two naive attempts, yet I fail to reach both my
> "design goals" at the same time.
>
> Version 1
> =========
>
> "SELECT * FROM MyClass M;"
> foreach(MyClass m in result)
> {
> "SELECT * FROM Array1 A WHERE myclass_id="+m.myclass_id;
> foreach(int i in result)
> m.AddToArray1(i);
>
> "SELECT * FROM Array2 A WHERE myclass_id="+m.myclass_id;
> foreach(int i in result)
> m.AddToArray2(i);
> }
>
> ======================
> Queries: 1+2*(MyClass) (Bad)
> Rows: (MyClass) + (MyClass*Array1) + (MyClass*Array2) =
> (MyClass)*(1+(Array1)+(Array2)) (Few)
> Columns: 1-3 (Few)
> Data amount over the wire: Rows*Columns (Ok)
>
> Version 2
> =========
>
> SELECT * FROM MyClass M
> LEFT JOIN Array1 USING(myclass_id)
> LEFT JOIN Array2 USING(myclass_id)
> ORDER BY M.myclass_id;
>
> foreach(MyClass m in result)
> {
> // Greatly simplified, to the point of beeing buggy... just meant
> for illustration.
> if(m.myclass_id is the same as it was in the previous iteration)
> {
> first_m_with_a_given_class_id.AddUniqueToArray1(m. Array1);
> first_m_with_a_given_class_id.AddUniqueToArray2(m. Array2);
> }
> }
>
> ==================
> Queries: 1 (OK)
> Rows: (MyClass)*(Array1)*(Array2) (Excessive amount)
> Columns: 5 (Many)
> Data amount over the wire: Rows*Columns (Massive)
>
> Greatful for any assistance.
> Nicklas
>