convert query to CakePHP 3.5 [closed]

Question

convert query to CakePHP 3.5 [closed]

Navigation

#1 by (0 votes)

2

I need to calculate the ranking of users who have a score. For this I found in another thread the following query that I would need to translate to cakephp format 3.0 or 3.5:

SELECT *, FIND_IN_SET(points,  
 (SELECT  GROUP_CONCAT(DISTINCT points ORDER BY points  DESC) 
  FROM    view_ent_users)) as rank
  FROM   view_ent_users
  WHERE nickname = 'Sebass'
  ORDER BY points DESC

Could you help me translate it?

Thanks, regards.

query cakephp

asked by Mar 14.04.2018 в 18:03

source

1 answer

How to generate a proper join query that brings me data from two tables? What do these commands do? I do not understand them very well

score 0 · Accepted Answer

I will assume that your entity is called Users and that we are in UsersController , you can achieve this with the Query Builder from CakePHP like this:

$result = $this->Users
    ->find()
    // Seleccionamos el campo rank
    ->select(['rank' => 'FIND_IN_SET((points), (SELECT GROUP_CONCAT(DISTINCT points ORDER BY points DESC) FROM view_ent_users))'])
    // Selecionamos todos los campos de la tabla
    ->select($this->Users)
    ->where(['nickname' => 'Sebass'])
    ->order(['points' => 'DESC']);