0

Firstly, it's not a problem to construct classes from a database, i.e. mysql, it's more a question about performance.

If I have a Class A which depends on class B. 

class A
{
    protected $depend;
    public function __construct($id == null)
    {
        // construct from mysql/postgresql/...
    }
}

And in the database has class A (say table "tbl_A") a foreign key to the table of class B (say "tbl_B"). Of course this classes are depending on much more than one table but i will simplify things here...

At the moment i construct class A from it's table:

select * from tbl_A where ID = $id

If they are successful, the statement of class A gives me something like that:

ID    | Name    | B_ID
1     | "test"  | 3

After that i had to construct class B in it's constructor. Is there any possibility to only make one statement with a join in constructor of class A and construct class B from there? I thought this will increase the performance of my application. Badly i don't found any functionality like friend classes (c++, etc) and i want to let my properties of class B stay protected or private.

Felix
  • 2,531
  • 14
  • 25
  • I think it would be better to create object from dependent tables where you want to use them. If you create all dependent objects in contstruct, then you may load some tables which you don't use at all. Instead of create them in constructor add method to create all dependent tables, for examble: getTblA(){}, getTblB(). – PKolos Dec 06 '13 at 08:42
  • You appear to be trying to reinvent the ORM wheel... – eggyal Dec 06 '13 at 08:58

2 Answers2

0

What is the largest performance overhead: MySQL/PostGre SQL or the PHP side?

If i understood correctly, for one instance of A, you have to do 2 queries (1 for A, one for the depended B). So for 1000 single instances of A, it makes 2000 queries total.

You can optimize your app to use "batch loading" of some kind. i.e. loading 1000 instances of A at once will be 1 query for A (1000 rows) + 1 query for B (1000 rows). So optimized from 2000 queries to just 2.

Your suggestion about joining tables is off course possible (join table A and table B on the correct keys and select all (needed) fields), but this would optimize from 2000 queries to 1000 queries plus your object-relation-mapping code would get more complicated.

Ronald Swets
  • 1,669
  • 10
  • 16
  • thats my point. I want to scale down the queries to a minimum. What's batch loading? a functionality of mysql/php? But that's not solving my problem at all. It's true, it will decrease overhead but how can i construct class B from the constructor of class A? – Felix Dec 06 '13 at 08:52
  • I don't know your exact architecture, but i assume you fire the queries within the constructor code of your objects. This means that for every object you already know what other classes are needed (i.e. for class A, you know you need class B) so you could create a larger join here. Although in class A, you don't really know if class B also needs class C or whatever other class. Staying in OO perspective, you don't want class A to know "all" about class B. – Ronald Swets Dec 06 '13 at 09:06
  • Oh that's a great answer. I didn't thought on that. – Felix Dec 06 '13 at 09:11
0

It's really going to depend on how deep down the rabbit hole you want to go, but if you want to catch all 2 steppers, you can do this:

select tA1.* from tbl_A tA1 where tA1.ID = $id UNION select tA3.* from tbl_A tA3 LEFT JOIN tbl_A tA2 ON tA3.ID = tA2.B_ID WHERE tA2.B_ID = $id

but then there's an issue if you need to go deep... this method becomes increasingly complex and less efficient than doing multiple queries (especially since a union is just making MySQL do it in that way).

Also once you've got them, you should loop through the list of ids you got and keep that in an array so you don't need to reload them.

virmaior
  • 424
  • 4
  • 14