Preface

In the process of development, we usually encounter many one-to-one data processing situations. Most of the time we will get a list, and then a single record of the list corresponds to another table to achieve business. For example, the following two tables of commodity information and commodity details, here in order to demonstrate the use of only basic fields, the actual development may be more complex, the following demonstration code database connection using PDO for processing.

Table structure

goods

goods_detail

primary

To be frank, I've seen the following code both in the company and in some open source projects.

$query = $db->query('select * from goods');
$result = $query->fetchAll();
// Scheme 1
foreach($result as $key => $item){
    $query = $db->query('select * from goods_detail where goods_id=' . $item['id']);
    $result[$key]['goods_detail'] = $query->fetch();
}
var_dump($result);
// Option two
foreach($result as &$item){
    $query = $db->query('select * from goods_detail where goods_id=' . $item['id']);
    $item['goods_detail'] = $query->fetch();
}
unset($item);
var_dump($result);
// Option three
$result = array_map(function($item){
    $query = $db->query('select * from goods_detail where goods_id=' . $item['id']);
    $item['goods_detail'] = $query->fetch();
    return $item;
},$result);
var_dump($result);

It's the most violent way, and it's a stand-up view, and the code seems tedious at first sight, doesn't it? If you learn Quote Friends in this section should know the second usage. They should directly use references to manipulate source data. Of course, it's best not to forget unset to remove $item. Besides the second, we can use the third way to manipulate source data. array_map Admittedly, this is no different from the second way, but there is a very big problem: N+1 for database queries. From the execution, we can see that in addition to a query list of SQL, each query record corresponding to the need to execute an SQL, resulting in additional queries, think about if the query is not limit ed. What would it be like?

Advanced

Seeing this, someone might think of another solution: first query the list, then take out the goods_id in the list, then use in to query, and then circulate to the list to see the code.

$goods_id = array_column($result,'id');
$goods_id_str = implode(',',$goods_id);
$query = $db->query(sprintf('select * from goods_detail where goods_id in (%s)',$goods_id_str));
$goods_detail_list = $query->fetchAll();
foreach($result as &$item){
    $item['goods_detail'] = array_first($goods_detail_list,function($item1){
        return $item['id'] == $item1['goods_id'];
    });
}
unset($item);
var_dump($result);
/**
 * From Laravel
 */
if (!function_exists('value')) {
    function value($value)
    {
        return $value instanceof Closure ? $value() : $value;
    }
}
/**
 * From Laravel
 */
if (!function_exists('array_first')) {
    /**
     * @param               $array
     * @param callable|null $callback
     * @param null          $default
     * @return mixed
     */
    function array_first($array, callable $callback = null, $default = null)
    {
        if (is_null($callback)) {
            if (empty($array)) {
                return value($default);
            }

            foreach ($array as $item) {
                return $item;
            }
        }

        foreach ($array as $key => $value) {
            if (call_user_func($callback, $value, $key)) {
                return $value;
            }
        }

        return value($default);
    }
}

In this code, we perfectly avoid the predicament of N+1, use in query, then traverse the array, and then use array_first method to find and pass to goods_detail index. Although this efficiency is much higher than the first one, it is not perfect. Next, let's look at the last one. For array_first, you can see another article of mine. array_find in PHP multidimensional array』.

Best practices

$goods_detail_list_by_keys = array_column($goods_detail_list,null,'goods_id');
foreach($result as &$item){
    $item['goods_detail'] = array_key_exists($goods_detail_list_by_keys,$item['id']) ? $goods_detail_list_by_keys[$item['id']] : null ;
    // php 7.1+
    // $item['goods_detail'] = $goods_detail_list_by_keys[$item['id']] ?? null;
}
unset($item);
var_dump($result);

This time, we use two other functions. array_column , array_key_exists In fact, in the official Manual of array_column, we can illustrate the method we want in Example #2. Applied here is to reset the keys of the elements in goods_detail_list to goods_id under a single element. Later, we use array_key_exists directly to determine whether it exists or not, and then make the corresponding processing. Here we can also do another operation, that is the default value, because sometimes, the data may not be correct, if found out directly back to the front-end, the front-end did not anticipate that this situation does not do fault-tolerant processing will lead to the front-end page crash, next to rewrite the code.

// In the "Advanced" section, we use the "array_first" function. The third parameter of the function can directly set the default value. We will not talk more about it, but mainly about the last one.
$goods_detail_default = [
    'content' => 'Default content',
    'id'      => null,
    'goods_id'=> null,
];
foreach($result as &$item){
    $tmp = array_key_exists($goods_detail_list_by_keys,$item['id']) ? $goods_detail_list_by_keys[$item['id']] : [] ;
    // php 7.1+
    // $tmp = $goods_detail_list_by_keys[$item['id']] ?? [];
    $item['goods_detail'] = array_merge($goods_detail_default,$tmp);
}
unset($item);
var_dump($result);

End

Even if it's finished here, some friends will say, why not use leftJoin to deal with it? Indeed, in dealing with one-to-one relationships, we often choose innerJoin or leftJoin for processing. A single SQL can be done, and rarely use a solution like this. In fact, otherwise, in the mainstream framework, the default solution is almost the same, such as Laravel,ThinkPHP There are many scenarios to consider, such as sometimes I just need to take part on demand, or I need to decide whether to load one-to-one based on the results of the business behind me. However, in this case, join seems not suitable.