Revize 2026
Přidáno uživatelem Ondřej Fibich před více než 11 roky(ů)
| freenetis/branches/1.1/application/models/user.php | ||
|---|---|---|
|
SELECT
|
||
|
u.*,
|
||
|
m.name AS member_name,
|
||
|
IFNULL(email,'') AS email,
|
||
|
IFNULL(phone,'') AS phone,
|
||
|
IFNULL(jabber,'') AS jabber,
|
||
|
IFNULL(icq,'') AS icq
|
||
|
IFNULL(email.value,'') AS email,
|
||
|
IFNULL(phone.value,'') AS phone,
|
||
|
IFNULL(jabber.value,'') AS jabber,
|
||
|
IFNULL(icq.value,'') AS icq
|
||
|
FROM users u
|
||
|
JOIN members m ON u.member_id = m.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT uc.user_id, c.value AS email FROM users_contacts uc
|
||
|
JOIN contacts c ON uc.contact_id = c.id AND c.type = ?
|
||
|
) e ON e.user_id = u.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT uc.user_id, c.value AS phone FROM users_contacts uc
|
||
|
JOIN contacts c ON uc.contact_id = c.id AND c.type = ?
|
||
|
) p ON p.user_id = u.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT uc.user_id, c.value AS jabber FROM users_contacts uc
|
||
|
JOIN contacts c ON uc.contact_id = c.id AND c.type = ?
|
||
|
) j ON j.user_id = u.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT uc.user_id, c.value AS icq FROM users_contacts uc
|
||
|
JOIN contacts c ON uc.contact_id = c.id AND c.type = ?
|
||
|
) i ON i.user_id = u.id
|
||
|
LEFT JOIN users_contacts uc_e ON uc_e.user_id = u.id
|
||
|
LEFT JOIN contacts email ON uc_e.contact_id = email.id AND email.type = ?
|
||
|
LEFT JOIN users_contacts uc_p ON uc_p.user_id = u.id
|
||
|
LEFT JOIN contacts phone ON uc_p.contact_id = phone.id AND phone.type = ?
|
||
|
LEFT JOIN users_contacts uc_j ON uc_j.user_id = u.id
|
||
|
LEFT JOIN contacts jabber ON uc_j.contact_id = jabber.id AND jabber.type = ?
|
||
|
LEFT JOIN users_contacts uc_i ON uc_i.user_id = u.id
|
||
|
LEFT JOIN contacts icq ON uc_i.contact_id = icq.id AND icq.type = ?
|
||
|
) AS u
|
||
|
$where
|
||
|
GROUP BY u.id
|
||
| ... | ... | |
|
SELECT
|
||
|
u.*,
|
||
|
m.name AS member_name,
|
||
|
IFNULL(email,'') AS email,
|
||
|
IFNULL(phone,'') AS phone,
|
||
|
IFNULL(jabber,'') AS jabber,
|
||
|
IFNULL(icq,'') AS icq
|
||
|
IFNULL(email.value,'') AS email,
|
||
|
IFNULL(phone.value,'') AS phone,
|
||
|
IFNULL(jabber.value,'') AS jabber,
|
||
|
IFNULL(icq.value,'') AS icq
|
||
|
FROM users u
|
||
|
JOIN members m ON u.member_id = m.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT uc.user_id, c.value AS email FROM users_contacts uc
|
||
|
JOIN contacts c ON uc.contact_id = c.id AND c.type = ?
|
||
|
) e ON e.user_id = u.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT uc.user_id, c.value AS phone FROM users_contacts uc
|
||
|
JOIN contacts c ON uc.contact_id = c.id AND c.type = ?
|
||
|
) p ON p.user_id = u.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT uc.user_id, c.value AS jabber FROM users_contacts uc
|
||
|
JOIN contacts c ON uc.contact_id = c.id AND c.type = ?
|
||
|
) j ON j.user_id = u.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT uc.user_id, c.value AS icq FROM users_contacts uc
|
||
|
JOIN contacts c ON uc.contact_id = c.id AND c.type = ?
|
||
|
) i ON i.user_id = u.id
|
||
|
LEFT JOIN users_contacts uc_e ON uc_e.user_id = u.id
|
||
|
LEFT JOIN contacts email ON uc_e.contact_id = email.id AND email.type = ?
|
||
|
LEFT JOIN users_contacts uc_p ON uc_p.user_id = u.id
|
||
|
LEFT JOIN contacts phone ON uc_p.contact_id = phone.id AND phone.type = ?
|
||
|
LEFT JOIN users_contacts uc_j ON uc_j.user_id = u.id
|
||
|
LEFT JOIN contacts jabber ON uc_j.contact_id = jabber.id AND jabber.type = ?
|
||
|
LEFT JOIN users_contacts uc_i ON uc_i.user_id = u.id
|
||
|
LEFT JOIN contacts icq ON uc_i.contact_id = icq.id AND icq.type = ?
|
||
|
) AS u
|
||
|
$where
|
||
|
GROUP BY u.id
|
||
Také k dispozici: Unified diff
Upravy:
- closes #528: Pomale vyhledavani v uzivatelich