OOP logic question

[cydeways06]

I am going to begin writing an application all based off OOP and need to make sure I have a good foundation before I get started. Right now I am going to be having a class for all of the tables in the database that will handle all of the data. My problem arises when I want to join to tables in a query. Following OOP it seems to me that I would have to do two seperate querys to get my information(if it involves two tables) or outside of the class write a query to the data that will execute my query. I am hoping that someone can help me come up(or point me in a direction) with a solution that is better than the two that I have thought of. Any thoughts are appreciated.

Mike
[e-mail]

[matto]

"1 table = 1 class":
this approach seems very unflexible to me. I would recommend it only if there were few tables and few kinds of complex queries required in your project.

for joins, you could create views (sql: create view ...) on sql level and treat them as if they were tables ... a view would be a class, too.
or, you could code the join query into a class itself, which would have the same effect.

however, I prefer this approach: you have a DB object which provides 3 main functions:

(2-dim array) query($sql)
(array) line_query($sql)
(string) field_query($sql)

example:

$db = new DB($username, $password);
$data = $db->query("select * from users where blablabla etc etc long join or not however");

this is the abstraction type I've been using for 3 years now, and I like it because it is both flexible and easy. all the db coding stuff goes into the DB class (resp. functions).

[cydeways06]

I believe I understand what you are telling me to do...but are you suggesting that I not use classes to access my tables. I thought of another approach that entails having a class for each of my tables but another class that is kind of like a wrapper class that contains contains and object for each of the other classes that I have made. That way each time I instantiate an object I have access to all of the information in the database that I need and when I perform query's with a join I can pull the information from the query and put it into the appropriate object. Please let me know what you think of this approach. Thanks

Mike [e-mail]

[ahundiak]

I create one class per table which represents a record in the table i.e. for the person table I have a CPerson class.

I create a 2nd class per table which represents the table itself and holds 0 or more record classes i.e. CPersons contains a list of CPerson objects.

A 3rd class represents the databse and is just an easy way to hold the various table classes.

Unlike matto, I find that the 1 class per table approach makes it easier to implement complex queries. In fact, it's why I went this route in the first place.

Consider a soccer game schedule showing what teams are playing and who the officials are.

/* Start with a comma delmited distinct list of the games to be shown. */
$gamesID = "1,5,8,10';

/* Load the games */
$games->addItemsForIDs($gameIDs);

/* Get the teams */
$teamIDs = $games->getTeamIDs();
$teams->addItemsForIDs($teamIDs);

/* Get the coaches */
$coachIDs = $teams->getPersonIDs(array(HEAD_COACH,ASST_COACH));
$persons->addItemsForIDs($coachIDs);

/* Get the officials */
$refereeIDs = $games->getRefereeIDs();
$persons->addItemsForIDs($refereeIDs);

Now I have one copy of every object and I can generate the report.

[cydeways06]

The method that you described above works...but I would really like to find a solution that will allow me to use query's with JOIN's with my objects. I have some ideas but I am not sure if they are going to work. They all involve have another class that controls the other classes(classes that are the tables). This class would call all the member functions and also have a member function that would execute a query, then after the query has been executed it would put each of the elements into the appropriate private object of the main class. Any ideas on that approach.

Mike
[e-mail]

[ahundiak]

I have considered the same approach. But it's a real pain to break up a joined result into individual objects. I do plan on running some bench marks but I am not convinced that one complex query is always faster than 3 or 4 simple queries. And I think speed would be the only reason for using joins.

My app has 20 tables so far and the performance is still acceptable.

[daveyboy]

Originally posted by matto

(2-dim array) query($sql)
(array) line_query($sql)
(string) field_query($sql)

excellent idea.

[ahundiak]

I think we are mixing apples and oranges here.

First, as matto suggested, one should always use some sort of generic interface when actually querying the database. It could be a class or it could be a set of functions.

But, I think that cydeways06 was asking about how to handle the data once you had it.

In "traditional" php programing, you fetch a row and echo out information directly from it.

echo $row['person_first_name'] . ' ' . $row['person_last_name'];

In a more OOP approach, the person information (for example) would be stored in a person object.

echo $person->getFullName();

An OOP approach isolates the report generator from the details of the database. Furthermore, the information for some reports is difficult to obtain with a single query.

[cydeways06]

Just wanted to post an updated idea. It consists of using a table for each class and one class as a container class. Each of the table classes will be a member variable of the container classes. The container class will also provide access to the database for all queries outside of these classes. It will provide two types of querys. One normal query and one for complex querys. The tables classes will contain an array containing all the fields in the database and will be accessible through two member function called get/set respectively. The container class will provide access the the table classes member function through a function. I think that sums it up....if anyone has any more comments please let me know.

Mike
[e-mail]