I need to get the positions held by a person. Person hasMany
Position. The rub is that Position has a field called effective which
is a date field. It's so that HR can assign a person to a position
effective on whenever. It's straightforward when it's a new person,
but when someone is changing positions effective on a certain date,
and/or holds multiple positions, querying for the list of current
positions held by someone is tricky.
I should also mention that if someone loses their position or changes
position, the position record is never dropped from the database.
This is so that a history of how an employee has moved around in the
company is always kept.
Basically, I need to ORDER BY and then GROUP BY and finally filter and
connect other pieces of data to those results.
I've turned to the dark side of using the Model->query like so, but
I'm hoping to find a way to do this right.
$positions = $this->Position->query('
SELECT
*
FROM
(
SELECT
*
FROM
(
SELECT
*
FROM
positions Position
WHERE
Position.person_id = '.$this->data['Person']['id'].'
ORDER BY
Position.effective DESC
) Position
GROUP BY
Position.title_id,
Position.department_id,
Position.person_id
) Position,
titles Title,
departments Department,
people Person
WHERE
Position.retired != 1 AND
Title.id =
Position.title_id AND
Department.id =
Position.department_id AND
Person.id =
Position.person_id
ORDER BY
Title.name,
Department.name
');
In the code above, the innermost query is grabbing a certain person's
most recently attained positions. The next query out is still just
grabbing position data, but it's grouping the positions by title,
department, and person. This is so that if you have a position X
effective last week, and you are slated to retire from position X next
week, that's 2 records in the database, but you only want to see the
person holding that position once. The outermost query is the easy
part, knocking out any records which are marked for retirement and
connecting the Position to a Person, Title, and Department record.
I'll take suggestions on either how to solve this, or a better way to
structure the project. I won't be able to change this project in
particular, but I will learn from it for future projects.
Thanks,
Jim
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
