合并对象而不是连接表：这是不好的做法吗？

Is this bad practice and should I be shot for coming up with this code?

这是不好的做法，我是否应该为了提出这段代码而被枪毙？

function get_business_addresses($business_id) {

    $query = $this->db->get_where('contact_business_addr_rel', array('business_id'=> $business_id));

    if ($query->num_rows() > 0) {
        foreach ($query->result() as $row) {
            $address_id = $row->address_id;
            $address_type_id = $row->address_type_id;
            $this->db->select('type');
            $q = $this->db->get_where('contact_business_address_type',array('id'=> $address_type_id));

            $query = $this->db->get_where('contact_business_addresses',array('id'=> $address_id)); 

            if ($query->num_rows() > 0) {
                foreach ($query->result() as $row) {

                    $row2 = $q->row();
                    $obj_merged = (object) array_merge((array) $row, (array) $row2);
                    $data[] = $obj_merged;
                }

            }
        }
    }
    return $data;
}

2 个解决方案

#1

Probably. I can't say for certain, but that looks like it should be significantly slower than just using a JOIN.

大概。我不能肯定地说，但看起来它应该比使用JOIN慢得多。

I would say benchmark it to be sure, but the rule of thumb I go by is "if it can be done cleanly in the SQL, the DB engine can probably do it better than I can".

我会说基准测试是肯定的，但我遵循的经验法则是“如果可以在SQL中干净利落地完成，那么数据库引擎可能会做得比我更好”。

#2

In short, yes this is bad. For a few rows (e.g. 10) this doesn't make much of a difference. However, as soon as your table happens to have 10 000 rows it means it first has to fetch 10 000 rows, then loop through each row and merge all the stuff. A simple JOIN in this case will be much faster.

简而言之，是的，这很糟糕。对于几行（例如10行）而言，这并没有多大区别。但是，一旦你的表碰巧有10 000行，这意味着它首先必须获取10 000行，然后遍历每一行并合并所有的东西。在这种情况下，简单的JOIN会快得多。

#1