Individual methods or parameters in Mysql queries?

Question

In certain parts of my system, some selection filters occur, an example, is when selecting the area of general sector collaborators, I can select:

Employees (all)
Active employees (status = true)
Inactive contributors (status = false)

I can do it two ways.

Via parameter in the method:

public static function get_collaborators(string $filter = 'all')
{
  if($filter == 'all'){
    $sql = 'SELECT * FROM data_collaborators';
  } elseif($filter == 'active'){
    $sql = 'SELECT * FROM data_collaborators WHERE status = 1';
  } elseif($filter == 'inactive'){
    $sql = 'SELECT * FROM data_collaborators WHERE status = 0';
  }        
  $rs = \MySQL\MySQLSelector::select_by_query_string($sql);
  return $rs['data'];
}

Or, Via separate methods:

public static function get_all_collaborators()
{
  $sql = 'SELECT * FROM data_collaborators';
  $rs = \MySQL\MySQLSelector::select_by_query_string($sql);
  return $rs['data'];
}

public static function get_active_collaborators()
{
  $sql = 'SELECT * FROM data_collaborators WHERE status = "1"';
  $rs = \MySQL\MySQLSelector::select_by_query_string($sql);
  return $rs['data'];
}

public static function get_inactive_collaborators()
{
  $sql = 'SELECT * FROM data_collaborators WHERE status = "0"';
  $rs = \MySQL\MySQLSelector::select_by_query_string($sql);
  return $rs['data'];
}

From what I understand, the first mode (with parameters) would perform less because it has checks, so several separate methods would be better.

The question is:

Is there a difference in execution from one to the other ? (even if it is insignificant) If yes, which would be better ? If not, which follows the 'correct pattern' followed by the majority ?

If there is another way, what would it be ?

Answer 1

In this case, creating three methods fragments your logic unnecessarily so leaving logic in one place just seems the best alternative. You can swap this if for array as you are basically choosing a query.

public static function get_collaborators(string $filter = 'all'){
    $baseQuery = 'SELECT * FROM data_collaborators ';
    $queries = array('all' => $baseQuery, 'active' => $baseQuery .'WHERE status = 1' , 'inactive' => $baseQuery .'WHERE status = 1');
    $sql = isset($queries[$filter]) ? $queries[$filter] : $baseQuery;
    $rs = \MySQL\MySQLSelector::select_by_query_string();
    return $rs['data'];
}

Answer 2

There is difference in execution from one to another?

Yes, but it depends on many factors, at least when considering the response time. If you are having problems with performance in your application. for sure is not the if the problem. There must be other performance bottlenecks in other parts of the code or the database itself.

If yes, which would be better?

The one that doesn’t hurt the semantics and atomicity of your code, not necessarily the fastest. As I commented on the question, the PHP language itself was not designed to perform well, so it’s unnecessary to worry about such small details.

Between the two ways presented, I think none of them is the best, because in the first you repeat the SQL query for each condition - if you are writing 3 times the same thing, there is something wrong; in the second you would have three methods with exactly the same goal (semantic), which violates the principle of atomicity.

What are "code units"?

What defines a clean code?

What makes a source code an easy to maintain code?

Writing easy-to-maintain, readable code?

What is elegant coding?

I particularly think that writing only a method that returns according to the value of the parameter - that’s its function, isn’t it? - makes much more sense, makes the code cleaner and much easier to understand and maintain.

public static function get_collaborators(string $filter = NULL)
{
    $sql = "SELECT * FROM data_collaborators";

    if ($filter) {
        $status = ($filter == "active") ? 1 : 0;
        $sql .= " WHERE status = {$status}";
    }

    $rs = \MySQL\MySQLSelector::select_by_query_string($sql);
    return $rs['data'];
}

Still, I don’t know what class this is running in, but considering there’s a class just to work with the table data_collaborators, could be done something like this:

class Collaborators
{
    const INACTIVE = 0;
    const ACTIVE = 1;
    const ALL = 3;

    public static function get(int $filter = self::ALL)
    {
        $sql = "SELECT * FROM data_collaborators";

        if ($filter !== self::ALL)
        {
            $sql .= " WHERE status = " . $filter;
        }

        $rs = \MySQL\MySQLSelector::select_by_query_string($sql);
        return $rs['data'];
    }
}

So you could use:

• Collaborators::get(), or Collaborators::get(Collaborators::ALL) for all employees;
• Collaborators::get(Collaborators::ACTIVE) for all active employees; and
• Collaborators::get(Collaborators::INACTIVE) for all inactive employees.

Or even define auxiliary methods:

public static function actives()
{
    return self::get(self::ACTIVE);
}

public static function inactives()
{
    return self::get(self::INACTIVE);
}

See working on Ideone.

And so use Collaborators::actives() and Collaborators::inactives() to get active and inactive employees respectively. Note that, unlike its implementation, this does not hurt the principle of atomicity, because all responsibility is concentrated in the method get; the other two methods have no responsibility and exist only as one sugar-syntax.

Answer 3

This is not a simple answer, but in terms of performance the gain is derisory.

There is no cake recipe, but you can use a function only and use a filter with the in clause ().

An example from the Soen

$ids    = array(0,1);
$params = implode(",", array_fill(0, count($ids), "?"));
$sql    = "SELECT * FROM data_collaborators WHERE status IN ($params)";
$stmt   = $mysqli->prepare($sql);

call_user_func_array(array($stmt, 'bindparams'), $ids);
$stmt->execute();
$stmt->close();

Answer 4

In case you can change the parameter so you don’t need to check with if instead of filter use status as parameter:

public static function get_collaborators($status = null)
{

    $sql = 'SELECT * FROM data_collaborators'.$status?' WHERE status = '.$status:null;     

    $rs = \MySQL\MySQLSelector::select_by_query_string($sql);
    return $rs['data'];
}

Answer 5

Yes, there is a difference in execution however minimal it may be. It happens for the reason that the get_collaborators() be executed, will have 3 different evaluations to be executed: if, elseif and one more elseif.

In short: If the filter is for everyone... do...
If the filter is not for everyone and is for active do...
If the filter is not for everyone and not for everyone but is for inactive... Do...

And another "empaca-performance" is that you are performing a query SELECT *, which is always a problem. Even if you want to select all columns, specifically put their name in your query.

About the comment of myth of SELECT *..

It is more proven than the sintaxe * may be slower for the following reasons:

• Not all fields are indexed and the query uses full table scan.
• Returning output fields from a table that contains columns of variable length can result in a small search overload: to return field 20, the previous 19 must be examined and compensated values are calculated.
• Only more data needs to be returned (passed through the connection). Since you need almost every field, the last reason is probably the most important. Let’s say that the TEXT field of the description can be only 1 of the 50 unused fields on the page, but it can take up 10 times more space than all the other fields together.

---

tl;dr

Perform separate functions and do not use SELECT *.