Individual methods or parameters in MySQL queries?

1

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

Partners (all)
Active contributors (status = true)
Inactive contributors (status = false)

I can do this in two ways.

Via parameter in 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'];
}

As far as I understand, the first mode (with parameters) would have less performance because it has checks, so several separate methods would be better.

The question is:

  

Is there a difference in running from one to another? (even if derisory)   If so, which one would be better? If not, which one follows the 'correct standard' followed by the majority?

     

If there is another way, what would it be?

    
asked by anonymous 25.08.2017 / 15:20

5 answers

3
  

Is there a difference in running from one to another?

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

  

If yes, which one would be best?

The one that does not hurt the semantics and atomicity of your code, not necessarily the fastest. As I commented in the question, the PHP language itself was not designed for great performance, so it's unnecessary to worry about such small details.

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

What are "code units"?

What defines clean code?

What makes a source code easy to maintain?

How to write readable and easy-to-maintain code

What is elegant coding?

Particularly I think writing only a method that returns according to the value of the parameter - that's his function, is not 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'];
}

I'm not sure what class this is running in, but considering there is a class just to work with the data_collaborators table, something like this could be done:

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 contributors;
  • Collaborators::get(Collaborators::ACTIVE) for all active employees; and
  • Collaborators::get(Collaborators::INACTIVE) for all inactive collaborators.

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 at Ideone .

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

    
25.08.2017 / 16:37
4

In this case, creating three methods fragments your logic unnecessarily so leaving the logic in one place just seems the best alternative. You can change this if by array because 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'];
}
    
25.08.2017 / 16:03
2

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

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

Following is an example taken from 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();
    
25.08.2017 / 15:39
1

In case you can change the parameter so you do not need to do those verifications 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'];
}
    
25.08.2017 / 15:34
0

Yes, there is difference in execution for as little as it can be. This happens because get_collaborators() will be executed, you will have 3 different evaluations to perform: if , elseif , and elseif .

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

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

About the Myth of SELECT *

It is already proven that 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 variable length columns can result in a small search overhead: to return to field 20, the previous 19 should be examined and the offset values are calculated.
  • Only more data needs to be returned (passed through the connection). As 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 10 times more space than all the other fields together.

tl; dr

Perform separate functions and do not use SELECT * .

    
25.08.2017 / 15:27