Radim na manjem CRM-u i u modulu zadataka korisnik otvara za datak, dodjeljuje ga sebi ili nekome drugom, pridruzuje dodatne korisnike kojih se zadatak tiče...
Tabele:
1. tasks (id,naziv,...)
2. users (id,ime,prezime)
3. tasks_users (task_id,user_id,color,order,seen)
Sada bi trebalo da svaki korisnik može za sebe lično podesiti boju, redoslijed... tu se nalazi i polje "seen"
query ide ovako
SELECT
tasks.id,
tasks.project_id,
tasks.project_archived,
tasks.parent_id,
tasks.naziv,
tasks.opis,
tasks.datum,
tasks.rok,
tasks.company_id,
tasks.costumer_id,
tasks.old_cont_id,
tasks.assigned_to_user_id,
tasks.assigned_at,
tasks.assigned_by_id,
tasks.completed_at,
tasks.completed_by_id,
tasks.created,
tasks.created_by_id,
tasks.updated,
tasks.updated_by_id,
tasks.task_state_id,
tasks.task_route_id,
tasks.route_order,
tasks.task_priority,
tasks.is_private,
tasks.radni_nalog_servisa_id,
pacom.naziv AS company_naziv,
pacom.phone AS company_phone,
pacos.naziv AS costumer_naziv,
pacos.mobile AS costumer_mobile,
asto.username AS assigned_to_username,
asto.first_name AS assigned_to_first_name,
asto.last_name AS assigned_to_last_name,
asby.username AS assigned_by_username,
asby.first_name AS assigned_by_first_name,
asby.last_name AS assigned_by_last_name,
coby.username AS completed_by_username,
coby.first_name AS completed_by_first_name,
coby.last_name AS completed_by_last_name,
crby.username AS created_by_username,
crby.first_name AS created_by_first_name,
crby.last_name AS created_by_last_name,
upby.username AS updated_by_username,
upby.first_name AS updated_by_first_name,
upby.last_name AS updated_by_last_name,
group_concat(
tasks_users.user_id SEPARATOR ', '
) AS subscribed_users
FROM
tasks
LEFT OUTER JOIN partners AS pacom ON pacom.id = tasks.company_id
LEFT OUTER JOIN partners AS pacos ON pacos.id = tasks.costumer_id
LEFT OUTER JOIN users AS asto ON asto.id = tasks.assigned_to_user_id
LEFT OUTER JOIN users AS asby ON asby.id = tasks.assigned_by_id
LEFT OUTER JOIN users AS coby ON coby.id = tasks.completed_by_id
LEFT OUTER JOIN users AS crby ON crby.id = tasks.created_by_id
LEFT OUTER JOIN users AS upby ON upby.id = tasks.updated_by_id
LEFT OUTER JOIN tasks_users ON tasks.id = tasks_users.task_id
WHERE
tasks.project_id = 1
AND tasks.completed_by_id = 0
AND tasks.task_state_id = 10
AND (
tasks.assigned_to_user_id = 9
OR tasks.created_by_id = 9
OR tasks.assigned_by_id = 9
OR tasks_users.user_id = 9
)
GROUP BY
tasks.id
ORDER BY
tasks.`order` ASC /* OVDJE BI TREBAO DA IDE REDOSLIJED PO KORISNIČKOM SORTIRANJU tabel task_users */
Kako sam ja skonto, moram na svaku tabelu USERS dodati još jedan LEFT OUTER task_users ili da uradim subselect npr.
....
(SELECT color FROM task_users WHERE tasks_users.task_id = task.id and tasks_users.user_id = 9) as color,
(SELECT `order` FROM task_users WHERE tasks_users.task_id = task.id and tasks_users.user_id = 9) as `order`,
(SELECT seen FROM task_users WHERE tasks_users.task_id = task.id and tasks_users.user_id = 9) as seen
FROM
tasks
....
Ili se sve ovo radi na drugi način?