Revize 4b851957
Přidáno uživatelem Ondřej Fibich před téměř 8 roky(ů)
| application/models/email_queue.php | ||
|---|---|---|
|
}
|
||
|
|
||
|
// filter
|
||
|
$having = "";
|
||
|
$where = "";
|
||
|
if ($filter_sql != '')
|
||
|
$having = "HAVING $filter_sql";
|
||
|
|
||
|
$where = "WHERE $filter_sql";
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT eq.id, eq.from, eq.to, eq.subject, eq.state, eq.access_time,
|
||
|
fuc.user_id AS from_user_id,
|
||
|
CONCAT(fu.name,' ',fu.surname) AS from_user_name,
|
||
|
tuc.user_id AS to_user_id,
|
||
|
CONCAT(tu.name,' ',tu.surname) AS to_user_name
|
||
|
FROM email_queues eq
|
||
|
LEFT JOIN contacts fc ON eq.from = fc.value AND fc.type = ?
|
||
|
LEFT JOIN users_contacts fuc ON fc.id = fuc.contact_id
|
||
|
LEFT JOIN users fu ON fuc.user_id = fu.id
|
||
|
LEFT JOIN contacts tc ON eq.to = tc.value AND tc.type = ?
|
||
|
LEFT JOIN users_contacts tuc ON tc.id = tuc.contact_id
|
||
|
LEFT JOIN users tu ON tuc.user_id = tu.id
|
||
|
WHERE eq.state = ?
|
||
|
GROUP BY eq.id
|
||
|
$having
|
||
|
SELECT COUNT(eq.id) as count
|
||
|
FROM
|
||
|
(
|
||
|
SELECT eq.id, eq.from, eq.to, eq.subject, eq.state, eq.access_time,
|
||
|
fuc.user_id AS from_user_id,
|
||
|
CONCAT(fu.name,' ',fu.surname) AS from_user_name,
|
||
|
tuc.user_id AS to_user_id,
|
||
|
CONCAT(tu.name,' ',tu.surname) AS to_user_name
|
||
|
FROM email_queues eq
|
||
|
LEFT JOIN contacts fc ON eq.from = fc.value AND fc.type = ?
|
||
|
LEFT JOIN users_contacts fuc ON fc.id = fuc.contact_id
|
||
|
LEFT JOIN users fu ON fuc.user_id = fu.id
|
||
|
LEFT JOIN contacts tc ON eq.to = tc.value AND tc.type = ?
|
||
|
LEFT JOIN users_contacts tuc ON tc.id = tuc.contact_id
|
||
|
LEFT JOIN users tu ON tuc.user_id = tu.id
|
||
|
WHERE eq.state = ?
|
||
|
) eq
|
||
|
$where
|
||
|
", array
|
||
|
(
|
||
|
Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL,
|
||
|
self::STATE_OK
|
||
|
))->count();
|
||
|
))->current()->count;
|
||
|
}
|
||
|
|
||
|
/**
|
||
| ... | ... | |
|
}
|
||
|
|
||
|
// filter
|
||
|
$having = "";
|
||
|
$where = "";
|
||
|
if ($filter_sql != '')
|
||
|
$having = 'HAVING '.$filter_sql;
|
||
|
|
||
|
$where = 'WHERE '.$filter_sql;
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT eq.id, eq.from, eq.to, eq.subject, eq.state, eq.access_time,
|
||
|
fuc.user_id,
|
||
|
CONCAT(fu.name,' ',fu.surname) AS from_user_name,
|
||
|
tuc.user_id,
|
||
|
CONCAT(tu.name,' ',tu.surname) AS to_user_name
|
||
|
FROM email_queues eq
|
||
|
LEFT JOIN contacts fc ON eq.from = fc.value AND fc.type = ?
|
||
|
LEFT JOIN users_contacts fuc ON fc.id = fuc.contact_id
|
||
|
LEFT JOIN users fu ON fuc.user_id = fu.id
|
||
|
LEFT JOIN contacts tc ON eq.to = tc.value AND tc.type = ?
|
||
|
LEFT JOIN users_contacts tuc ON tc.id = tuc.contact_id
|
||
|
LEFT JOIN users tu ON tuc.user_id = tu.id
|
||
|
WHERE eq.state <> ?
|
||
|
GROUP BY eq.id
|
||
|
$having
|
||
|
SELECT COUNT(eq.id) as count
|
||
|
FROM
|
||
|
(
|
||
|
SELECT eq.id, eq.from, eq.to, eq.subject, eq.state, eq.access_time,
|
||
|
fuc.user_id AS from_user_id,
|
||
|
CONCAT(fu.name,' ',fu.surname) AS from_user_name,
|
||
|
tuc.user_id AS to_user_id,
|
||
|
CONCAT(tu.name,' ',tu.surname) AS to_user_name
|
||
|
FROM email_queues eq
|
||
|
LEFT JOIN contacts fc ON eq.from = fc.value AND fc.type = ?
|
||
|
LEFT JOIN users_contacts fuc ON fc.id = fuc.contact_id
|
||
|
LEFT JOIN users fu ON fuc.user_id = fu.id
|
||
|
LEFT JOIN contacts tc ON eq.to = tc.value AND tc.type = ?
|
||
|
LEFT JOIN users_contacts tuc ON tc.id = tuc.contact_id
|
||
|
LEFT JOIN users tu ON tuc.user_id = tu.id
|
||
|
|
||
|
) eq
|
||
|
$where
|
||
|
", array
|
||
|
(
|
||
|
Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL,
|
||
|
self::STATE_OK
|
||
|
))->count();
|
||
|
self::STATE_OK,
|
||
|
Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL
|
||
|
))->current()->count;
|
||
|
}
|
||
|
|
||
|
/**
|
||
Také k dispozici: Unified diff
Refs #1053: Default sort in emails set to ID which is indexed (previously by access_time), some small improvements in related queries but filters are still very slow.