如何在CakePHP中编写带子查询的Select

Question

I have the following query:

$queryResult = $this->Hit->query(
    "select P, count(S) as S
    from 
    (
        select pattern_id as P, srn as S from hits 
        where job_id=".$id." and srn != ''
        group by srn, pattern_id 
        order by pattern_id, srn
    ) as T 
        group by P
        order by P;"
);

So basically I have a select .. from (select .. ) ... statement. It's working perfect while I'm using MySQL. But I have to migrate the DB to PostgreSQL so I would like to change this to the Cake-way. So my question is, how can I interpret this type of query (select from select) in CakePHP?

Thanks in advance.

Answer 1

I am not sure what you mean by interpret this query in CakePHP since your code is valid CakePHP.

That being said, I would rewrite your query as:

SELECT pattern_id as P, COUNT(DISTINCT srn) as S
FROM hits
WHERE job_id=".$id." and srn != ''
GROUP BY pattern_id
ORDER BY pattern_id;

(I believe this is equivalent - you could load sqlfiddle.com with sample data for better testing)

If that works and you have a Hits model, then you could re-write the query using the find method.