cakephp pagination with complex query

Question

I have a database structure given below

id | Title   | brand | 
---+---------+-------+
1  |product1 | Lg    |
2  |pruduct2 | Lg    |
3  |pruduct3 | Lg    |
4  |pruduct4 | Lg    |
5  |pruduct5 | Lg    |
6  |pruduct6 |samsung|
7  |pruduct7 |samsung|
8  |pruduct8 |samsung|
9  |pruduct9 |samsung|
10 |pruduct10|samsung|
11 |pruduct11| sony  |
12 |pruduct12| sony  |
13 |pruduct13| sony  |
14 |pruduct14| sony  |
15 |pruduct15| sony  |

We need some query by which I can mix results by brands
Output should be like

id | Title   | brand | 
---+---------+-------+
1  |product1 | Lg    |
6  |pruduct6 |samsung|
11 |pruduct11| sony  |
2  |pruduct2 | Lg    |
7  |pruduct7 |samsung|    
12 |pruduct12| sony  |
.... and so on

I have following query which can retrieve same result

select id, title,brand row_number() over (partition by brand_id) as 
row_num from product_test order by row_;

This doesn't work with MySql unfortunately Need a solution based on cakephp find or any other apporach

Thanks in advance

It is not clear what you are trying to achieve and you haven't stated which version of CakePHP you are using. — drmonkeyninja, Jan 28 '16 at 11:04
It is pretty clear what needs to be done but he wants you to write the code for him. :) He wan's you to write the ORM code for the SQL query he provided. — floriank, Jan 28 '16 at 11:31

score 0 · Answer 1 · answered Feb 14 '16 at 18:55

You can emulate the query in the following way:

SELECT @rownum := 0, @brand := NULL;

SELECT
  id
, title
, brand
FROM (
  SELECT
    id
  , title
  , brand
  , @rownum := IF(@brand <=> brand, @rownum + 1, 1) AS row_num
  , @brand := brand AS dummy
  FROM
    product_test
  ORDER BY
    brand
  , id
  ) product_test
ORDER BY
  row_num
, brand
, id;

cakephp pagination with complex query

1 Answers1