Subquery not work

Need Help
1

Hello, for several days I have encountered great difficulty in correctly sorting the recordings on CakePHP 4.

I want to view the last post a user posted, which is very simple, but it doesn’t work:

 $s = $this->Articles->find()
        ->select('Articles.id')
        ->distinct('Articles.user_id');

 $articles = $this->Articles
        ->find()
        ->where(['Articles.id IN' => $s])
        ->order(['Articles.created' => 'DESC'])
        ->contain(['Users']);

All articles are displayed without taking into account the “where” conditions.

Same with :

$s = $this->Users->find()
        ->select('id')
        ->distinct('country_id');

$users = $this->Users
        ->find()
        ->where(['Users.id IN' => $s])
        ->order(['Users.country_id' => 'DESC'])
        ->contain(['Countries']);

SQL:

SELECT 
  Users.id AS Users__id, 
  Users.alias AS Users__alias, 
  Users.username AS Users__username, 
  Users.email AS Users__email, 
  Users.password AS Users__password, 
  Users.country_id AS Users__country_id, 
  Users.presentation AS Users__presentation, 
  Users.secret_key AS Users__secret_key, 
  Users.account_status AS Users__account_status, , 
  Users.role AS Users__role, 
  Users.created AS Users__created, 
  Users.modified AS Users__modified, 
  Countries.id AS Countries__id, 
  Countries.name AS Countries__name, 
  Countries.slug AS Countries__slug, 
  Countries.user_count AS Countries__user_count 
FROM 
  users Users 
  LEFT JOIN countries Countries ON Countries.id = (Users.country_id) 
WHERE 
  Users.id in (
    SELECT 
      Users.id AS Users__id 
    FROM 
      users Users 
    GROUP BY 
      country_id
  ) 
ORDER BY 
  Users.country_id DESC

Why is it not working in CakePHP?

Thanks a lot for your help

2

I think you can change ORDER BY
Users.id DESC

3

Hello, thank you for your response. The result is the same except the sort order.

I still have too many results with duplicates. The “WHERE” sub-query is ignored. If I replace it with an array manually write indicating the IDs, it works. Otherwise, I have everything in duplicate.

4

Try like this
SELECT * FROM users LEFT JOIN countries ON users.country_id = countries.id WHERE users.id IN ( SELECT DISTINCT users.country_id FROM users ) ORDER BY users.id DESC

you can use subquery like this
SELECT DISTINCT users.country_id FROM users

5

Unfortunately, the query does not work, it returns no results. I worked on my own and think I found the right approach. I rather use HAVING which allows to do what I want here is an example:

SELECT Highscore. *
FROM highscores Highscore
INNER JOIN (
   SELECT user_id, MIN (score) AS score
   FROM highscores GROUP BY user_id
) AS min USING (user_id, score)
ORDER BY score DESC

I’m not sure if I could easily make this request in CakePHP, but I think it should be fine!