Hello,
I am not very good with SQL, and I know I'm not supposed to call SQL
queries directly, but I don't know the CakePHP way exactly either.
What I'm trying to do is write a function that returns a list of
documents that an employee needs to be trained on, rather, return the
documents that an employees has yet to be trained on.
The tables involved are these (and I'm not listing every column, just
the important ones):
---------------------------
documents
---------------------------
id | name | number(varchar) | revision(char)
---------------------------
positions
---------------------------
id | name
---------------------------
employees
---------------------------
id | name
---------------------------
documents_positions
---------------------------
id | position_id | document_id
---------------------------
training_logs
---------------------------
id | employee_id | document_id | revision(char)
---------------------------
employees_positions
---------------------------
id | employee_id | position_id
Now, documents table is obvious I think. Positions is a list of
positions within a company (e.g. Engineer, Manager, Cook, Janitor,
whatever). The employees table is obvious too. The documents_positions
table outlines all of the documents that are required training for a
given position. The training_logs table outlines what training the
employees have completed including revision. This way if a document is
updated, we know which employees are to be re-trained. The
employees_positions tables outlines which employees are in what
positions, as some employees are more than one position (e.g. my boss
is 3 different positions).
So, what I'm trying to do is compare what is required training from
documents_positions, to what training someone has already done from
training_logs by comparing the revision letter in training_logs to the
revision letter in the documents table, and return which documents an
employee is deliquent on.
Part of the problem is I do not understand how to construct the query
using CakePHP methods. Since we are using a query that selects from
multiple tables at once, which model do I call the find method on? I
am currently trying to do this from within the TrainingLogs controller
and this is what I have right now, but it is only comparing what
training someone has done and not what they have to do (for e.g., if
someone has not done any training at all, meaning no records exist
inside training_logs table, then this returns 0 documents, instead of
all that are required). Also I know my SQL syntax is not really
proper, but it's the way I know how.
$this->DocumentsPosition->query('
SELECT DISTINCT documents.id, documents.number, documents.revision,
documents.location, documents.training_revision
FROM training_logs, documents, documents_positions, positions,
employees_positions, employees
WHERE
(documents.id = documents_positions.document_id) AND
(positions.id = documents_positions.position_id) AND
(positions.id = employees_positions.position_id) AND
(documents.id = training_logs.document_id) AND
(training_logs.employee_id = employees_positions.employee_id) AND
(employees.id = employees_positions.employee_id) AND
(employees.id = 33) AND
(training_logs.revision <> documents.revision)
');
In this example, I am trying to retrieve the delinquent training
documents for an employee with an id of 33.
Any help? I would love to understand the CakePHP way, but will settle
for anything that just works instead.
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
