Subquery not work

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()

 $articles = $this->Articles
        ->where([' IN' => $s])
        ->order(['Articles.created' => 'DESC'])

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

Same with :

$s = $this->Users->find()

$users = $this->Users
        ->where([' IN' => $s])
        ->order(['Users.country_id' => 'DESC'])


SELECT AS Users__id, 
  Users.alias AS Users__alias, 
  Users.username AS Users__username, 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, AS Countries__id, AS Countries__name, 
  Countries.slug AS Countries__slug, 
  Countries.user_count AS Countries__user_count 
  users Users 
  LEFT JOIN countries Countries ON = (Users.country_id) 
WHERE in (
    SELECT AS Users__id 
      users Users 
  Users.country_id DESC

Why is it not working in CakePHP?

Thanks a lot for your help

I think you can change ORDER BY DESC

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.

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

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

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
   SELECT user_id, MIN (score) AS score
   FROM highscores GROUP BY user_id
) AS min USING (user_id, score)

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