I have the following code:

function get($min, $max) { // Хосты и хиты $query = $this->db->query(' SELECT COUNT(DISTINCT ip2long) AS hosts, COUNT(*) AS hits, `date` FROM tracking WHERE user_id = "' . $this->user_id . '" ' . ($this->sub_id != 0 ? 'AND sub_id = "' . $this->db->escape($this->sub_id) . '" ' : '') . ($this->offer_id != 0 ? 'AND offer_id = "' . $this->db->escape($this->offer_id) . '" ' : '') . 'AND time BETWEEN "' . strtotime($this->days_array[$min] . ' 00:00') . '" AND "' . strtotime($this->days_array[$max] . ' 23:59') . '" GROUP BY `date` ASC '); foreach ($query->result_array() as $k) { $this->data[$k['date']]['tracking']['hosts'] = $k['hosts']; $this->data[$k['date']]['tracking']['hits'] = $k['hits']; } // Конверсии $query = $this->db->query(' SELECT COUNT(*) AS count, SUM(summa) AS summa, `date` FROM conversions WHERE user_id = "' . $this->user_id . '" ' . ($this->sub_id != 0 ? 'AND sub_id = "' . $this->db->escape($this->sub_id) . '" ' : '') . ($this->offer_id != 0 ? 'AND offer_id = "' . $this->db->escape($this->offer_id) . '" ' : '') . 'AND status = "1" AND time BETWEEN "' . strtotime($this->days_array[$min] . ' 00:00') . '" AND "' . strtotime($this->days_array[$max] . ' 23:59') . '" GROUP BY `date` ASC '); foreach ($query->result_array() as $k) { $this->data[$k['date']]['conversions']['count'] = $k['count']; $this->data[$k['date']]['conversions']['summa'] = $k['summa']; } // Регистрации $query = $this->db->query(' SELECT COUNT(*) AS count, SUM(summa) AS summa, `date` FROM event_register WHERE user_id = "' . $this->user_id . '" ' . ($this->sub_id != 0 ? 'AND sub_id = "' . $this->db->escape($this->sub_id) . '" ' : '') . ($this->offer_id != 0 ? 'AND offer_id = "' . $this->db->escape($this->offer_id) . '" ' : '') . 'AND status = "1" AND time BETWEEN "' . strtotime($this->days_array[$min] . ' 00:00') . '" AND "' . strtotime($this->days_array[$max] . ' 23:59') . '" GROUP BY `date` ASC '); foreach ($query->result_array() as $k) { $this->data[$k['date']]['register']['count'] = $k['count']; $this->data[$k['date']]['register']['summa'] = $k['summa']; } // Покупки $query = $this->db->query(' SELECT COUNT(*) AS count, SUM(summa) AS summa, `date` FROM event_buy WHERE user_id = "' . $this->user_id . '" ' . ($this->sub_id != 0 ? 'AND sub_id = "' . $this->db->escape($this->sub_id) . '" ' : '') . ($this->offer_id != 0 ? 'AND offer_id = "' . $this->db->escape($this->offer_id) . '" ' : '') . 'AND status = "1" AND time BETWEEN "' . strtotime($this->days_array[$min] . ' 00:00') . '" AND "' . strtotime($this->days_array[$max] . ' 23:59') . '" GROUP BY `date` ASC '); foreach ($query->result_array() as $k) { $this->data[$k['date']]['buy']['count'] = $k['count']; $this->data[$k['date']]['buy']['summa'] = $k['summa']; } // Заработок с рефералов $query = $this->db->query(' SELECT SUM(summa) AS summa, `date` FROM referals WHERE user_id = "' . $this->user_id . '" AND time BETWEEN "' . strtotime($this->days_array[$min] . ' 00:00') . '" AND "' . strtotime($this->days_array[$max] . ' 23:59') . '" GROUP BY `date` ASC '); foreach ($query->result_array() as $k) { $this->data[$k['date']]['referals']['summa'] = $k['summa']; } $return = array(); foreach ($this->data as $key => $k) { if (strtotime($key) >= strtotime($this->days_array[$min]) && strtotime($key) <= strtotime($this->days_array[$max])) { $return[$key] = $this->data[$key]; $return[$key]['date'] = $key; // Трекинг $return[$key]['tracking']['hosts'] = (isset($return[$key]['tracking']['hosts']) ? $return[$key]['tracking']['hosts'] : 0); $return[$key]['tracking']['hits'] = (isset($return[$key]['tracking']['hits']) ? $return[$key]['tracking']['hits'] : 0); // Конверсии $return[$key]['conversions']['count'] = (isset($return[$key]['conversions']['count']) ? $return[$key]['conversions']['count'] : 0); $return[$key]['conversions']['summa'] = (isset($return[$key]['conversions']['summa']) ? $return[$key]['conversions']['summa'] : 0); // Регистрации $return[$key]['register']['count'] = (isset($return[$key]['register']['count']) ? $return[$key]['register']['count'] : 0); $return[$key]['register']['summa'] = (isset($return[$key]['register']['summa']) ? $return[$key]['register']['summa'] : 0); // Покупки $return[$key]['buy']['count'] = (isset($return[$key]['buy']['count']) ? $return[$key]['buy']['count'] : 0); $return[$key]['buy']['summa'] = (isset($return[$key]['buy']['summa']) ? $return[$key]['buy']['summa'] : 0); // Рефералы $return[$key]['referals']['summa'] = (isset($return[$key]['referals']['summa']) ? round($return[$key]['referals']['summa'], 2) : 0); // Менеджеры $return[$key]['managers']['summa'] = (isset($return[$key]['managers']['summa']) ? round($return[$key]['managers']['summa'], 2) : 0); // Общая сумма $return[$key]['summa'] = round($return[$key]['conversions']['summa'] + $return[$key]['register']['summa'] + $return[$key]['buy']['summa'] + $return[$key]['referals']['summa'] + $return[$key]['managers']['summa'], 2); // CTR $return[$key]['ctr'] = ((isset($return[$key]['buy']['count']) && isset($return[$key]['conversions']['count']) && $return[$key]['buy']['count'] > 0 && $return[$key]['conversions']['count'] > 0) ? round(($return[$key]['buy']['count'] / $return[$key]['conversions']['count']) * 100) : 0); // CR (Конверт) $return[$key]['cr'] = ((isset($return[$key]['tracking']['hosts']) && isset($return[$key]['conversions']['count']) && $return[$key]['tracking']['hosts'] > 0 && $return[$key]['conversions']['count'] > 0) ? round(($return[$key]['conversions']['count'] / $return[$key]['tracking']['hosts']) * 100) : 0); // Всего трекинг $this->total['tracking']['hosts'] += $return[$key]['tracking']['hosts']; $this->total['tracking']['hits'] += $return[$key]['tracking']['hits']; //Конверсии всего $this->total['conversions']['count'] += $return[$key]['conversions']['count']; // Регистрации всего $this->total['register']['count'] += $return[$key]['register']['count']; // Покупки всего $this->total['buy']['count'] += $return[$key]['buy']['count']; // Рефералы всего $this->total['referals']['summa'] += $return[$key]['referals']['summa']; // Общая сумма $this->total['summa'] += ($return[$key]['summa']); } } krsort($return); return $return; }

As you can see, not the best way to get data. I would like to combine these into one. At the same time, I thought about using the FULL JOIN. That's why. I need the following table enter image description here

The number of rows in this table will be equal to the number of rows of the largest sample of one of the selects. For an example I will take the first. Here is the query:

 SELECT user_id, COUNT(DISTINCT ip2long) AS hosts, COUNT(*) AS hits, date FROM tracking WHERE user_id = 8 AND time BETWEEN '1533884400' AND '1534402800' GROUP BY tracking.date;

Here is the result: enter image description here

But I would like something like this:

enter image description here

For this, I wrote this request:

 SELECT * FROM (SELECT MIN(time) as min_time, user_id, COUNT(DISTINCT ip2long) AS hosts, COUNT(*) AS hits, date FROM tracking WHERE user_id = 8 AND time BETWEEN '1533884400' AND '1534402800' GROUP BY tracking.date) as track LEFT JOIN ( SELECT time, user_id, COUNT(*) AS count, SUM(summa) AS summa, date FROM conversions WHERE user_id = 8 AND time BETWEEN '1533884400' AND '1534402800' GROUP BY date, time) conv ON conv.user_id = track.user_id AND track.min_time = conv.time UNION ALL SELECT * FROM (SELECT MIN(time) as min_time, user_id, COUNT(DISTINCT ip2long) AS hosts, COUNT(*) AS hits, date FROM tracking WHERE user_id = 8 AND time BETWEEN '1533884400' AND '1534402800' GROUP BY tracking.date) as track RIGHT JOIN ( SELECT MIN(time) as min_time, user_id, COUNT(*) AS count, SUM(summa) AS summa, date FROM conversions WHERE user_id = 8 AND time BETWEEN '1533884400' AND '1534402800' GROUP BY date, time) conv ON conv.user_id = track.user_id AND track.min_time = conv.min_time WHERE track.user_id is NULL OR track.user_id is NULL AND track.min_time is NULL ;

Here I am trying to implement full join (since it is not in mysql). The question is, is this the best way or is there any better way?

  • one
    You do not need to combine selects into one. You need to divide your function into 5-6 functions - ArchDemon
  • This only optimizes the appearance and improves the readability of the code. Will not the same several queries be executed to the database, thereby terribly loading the page? I would like to reduce everything to just one request. Now thinking about the transaction. In fact, the task is to get what is on the view by ONE REQUEST) - Aleksej_Shherbak pm
  • one
    Break into functions and make profiling each. Merging queries probably won't help you. And the request will be made very incomprehensible (to you in a couple of months). The code must be written so that it is understandable to man. - ArchDemon 5:53 pm
  • Thanks for the advice. I'll try. As for "The code should be written so that it is understandable to the person." I agree, but the DB itself is still quite so-so before me. And now there is a requirement that it work QUICKLY. - Aleksej_Shherbak pm

0