I have clients and a list of various stages in our business process
which I call advancements in the database. Each time a client
progresses, I make a note of it in the advancement_histories table.
I want a client list, with the 'advancement_id' of the latest
advancement.
Each advancement belongs to an advancement category. So, what I really
want is a client list, grouped by advancement category id, where
clients are only listed once in the advancement category of the latest
advancement recorded.
clients
----------
id
firstname
lastname
advancement_histories
--------------------------------------------
id
advancement_id
client_id
datetime
advancements
--------------------
id
advancement_category_id
name
advancement_categories
-----------------------------------
id
sort_id
name
The following query in PhpMyAdmin's SQL box gives me exactly what I
want. Running the same query (or rather 3 queries) in a single query()
call throws a MYSQL syntax error. I assume that cakephp does not like
multiple queries.
I thought I could try running a .sql file with the executeSQL()
function, but I keep getting unknown function call errors.
========
CREATE TEMPORARY TABLE `temptable` (
id int(11) NOT NULL,
firstname varchar(200) NOT NULL,
lastname varchar(200) NOT NULL,
maxofid int(11) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO temptable
SELECT
clients.id,
clients.firstname,
clients.lastname,
Max(advancement_histories.id) AS MaxOfid
FROM
clients LEFT JOIN advancement_histories ON
clients.id=advancement_histories.client_id
GROUP BY
clients.id, clients.firstname, clients.lastname;
SELECT
advancement_categories.name as Category,
temptable.id,
advancement_categories.sort_id,
temptable.lastname,
temptable.firstname,
temptable.MaxOfid
FROM
((temptable LEFT JOIN advancement_histories ON
temptable.MaxOfid=advancement_histories.id)
LEFT JOIN advancements as Advancements ON
advancement_histories.advancement_id=Advancements.ID)
LEFT JOIN advancement_categories ON
Advancements.advancement_category_id=advancement_categories.id
GROUP BY
advancement_categories.name, temptable.id,
advancement_categories.sort_id, temptable.lastname,
temptable.firstname, temptable.MaxOfid
ORDER BY
advancement_categories.sort_id, temptable.lastname,
temptable.firstname;
=========
Ideas?
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
