0

I have been dipping my toes into object orientated PHP programming and I'm finding it useful but I can't help but feel I'm missing something in regards to how to utilize it.

Normally my classes and method result in hundreds if not thousands of queries to the database in order to retrieve information for one day or staff member (due to continually looping through the month for each staff member)

Below is, for example, one of my methods that deals with getting timeclock data.

public function get_TimeclockStatus() {
    $query = "SELECT *
    FROM info
    WHERE fullname = '$this->user'
    AND DAY(FROM_UNIXTIME(`timestamp`)) = '$this->day'
    AND MONTH(FROM_UNIXTIME(`timestamp`)) = '$this->month'
    AND YEAR(FROM_UNIXTIME(`timestamp`)) = '$this->year'
    order by timestamp DESC";
    $result = Timeclock::$db_connection->query($query) or mysqli_error(Timeclock::$db_connection);
    if(!$result) return "out";
    $row = $result->fetch_object();
    return $row;
  } 

It could actually be the case that I'm trying to force something to work when it truly isn't needed but I'm not experienced enough nor have I found anything online which clearly details this as such. Any tips or advice on for example dealing with going through an entire month per day without querying hundreds/thousands of times would be very helpful.

8
  • 4
    For me after reading several times it is not clear what you are trying to achieve. It seems you are talking abut a large algorithm but showing just a SQL request. Could you try to clarify your question?
    – f222
    Aug 28, 2020 at 8:26
  • 2
    Besides the fact this question is really unclear, I recommend heavily this canonical XKCD about using unsanitized strings (especially user names) in SQL statements.
    – Doc Brown
    Aug 28, 2020 at 8:32
  • Yes this is my issue, I don't even have the experience of being able to lay things out correctly, I'm sort of caught between a rock and a hard place where I'm only half sure what I'm doing is the proper way of working. That is one method of a larger class, one which is based on getting times/holidays per day for each staff member, due to the way I've coded it, it results in thousands of SQL queries on method use. Essentially my question is, is it intentional that due to the simplicity and modularity of classes/methods you need to peform all of these queries. @f222 Aug 28, 2020 at 8:39
  • 2
    @ConnorMartin You can not edit your question ?
    – f222
    Aug 28, 2020 at 8:50
  • 2
    Ah, you've stumbled across one of the major flaws in OOP: It doesn't like to do things in batches. It would be sensible to do a single query to get all the time clock data for all the displayed staff members, but you can't do that if the query is inside a TimeClockMonth or a StaffMember object Aug 28, 2020 at 11:19

2 Answers 2

4

In a comment (which you better should have edited into the question) you asked:

is it intentional that due to the simplicity and modularity of classes/methods you need to peform all of these queries.

If it is "intentional" is debatable - I see the "intention" in the responsibility of the programmer, not in OOP. But the effect you observed may indeed sometimes occur when one implements classes and related queries in (probably too) simplistic 1:1 manner. This can be the result of following the OO idea of "combining functions/operations and related data into one object" too literally, putting some "standard queries" into each object, and then trying to build business logic on top of that abstraction, without dealing with SQL any more. And since these "standard SQLs" are usually not sufficient, one runs into performance issues.

There are different solutions for this problem, and it is hard to say which one will help you best in your specific situation, since there is hardly enough context in your question. But let me scetch one standard strategy here, which is the idea of separating data access / persistence from the business logic by different layers.

Instead of designing your queries exclusively around your "business objects", and implementing the queries directly in there, you implement a data access layer (DAL) (or repository layer) with all the required SQL queries. This layer will typically contain those "standard queries" for your business objects, but also optimized queries for the specific requirements of the application. It retrieves the data in form of records, recordsets or sets of "data access objects" (DAOs) from a database, and the business objects are constructed from those records. The business objects itself are ideally persistent-ignorant.

If you now say that this might result in a lot of additional boilerplate code, then you are fully correct, and that's why people have invented object-relational mappers, which are intended to make implementing a standard DAL or "repo layer" more simple. These frameworks, however, lead you back to your original problem: they are often fine for generating "standard SQLs" which match the business objects 1:1, but most of them are not very successful in creating "optimzed queries" automatically.

Note that, when your application requires optimized SQLs to fulfill its performance, one has to write them, and that is in no way different when you do not use OOP.

0

Doc Brown has some very good advice, but I would like to expand on it with respect to ORMs putting you right back into your current situation.

When the complexity of your use cases grow, it can become useful to split your models into specialized "read" and "write" models, each with their own data access methods. This allows you to leverage an ORM for classes used to manipulate data according to business rules while creating other classes that specialize in the minimal data required to present information in a business context.

For example, an ORM-mapped class (the "write" model) might need additional attributes related to auditing such as who created a record and when it was updated. A separate model to display the data to the user (the "read" model) might not need the created/updated information in most use cases. The "read" model might not need an ORM at all, so the SQL can be optimized to reduce the number of queries to the database.

Since applications tend to read far more often than write, the plethora of queries and statements an ORM generates will not be as big of a deal since it affects the lesser used use cases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.