# Subquery not work

**URL:** <https://discourse.cakephp.org/t/subquery-not-work/8170>\
**Category:** Need Help\
**Created:** [August 4, 2020, 8:33am UTC](https://discourse.cakephp.org/t/subquery-not-work/8170 "2020-08-04T08:33:46Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![kentosama](https://avatars.discourse-cdn.com/v4/letter/k/bbce88/32.png) [@kentosama](https://discourse.cakephp.org/u/kentosama)\
**Post date:** [August 4, 2020, 8:33am UTC](https://discourse.cakephp.org/t/subquery-not-work/8170/1 "2020-08-04T08:33:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Maniraj](https://avatars.discourse-cdn.com/v4/letter/m/51bf81/32.png) [@Maniraj](https://discourse.cakephp.org/u/Maniraj)\
**Post date:** [August 4, 2020, 9:08am UTC](https://discourse.cakephp.org/t/subquery-not-work/8170/2 "2020-08-04T09:08:12Z")

</div>

> [@kentosama](#):
>
> ```auto
> ORDER BY 
> Users.country_id DESC
> 
> ```

I think you can change ORDER BY  
Users.id DESC

---

<div class="post-metadata">

**Author:** ![kentosama](https://avatars.discourse-cdn.com/v4/letter/k/bbce88/32.png) [@kentosama](https://discourse.cakephp.org/u/kentosama)\
**Post date:** [August 4, 2020, 11:35am UTC](https://discourse.cakephp.org/t/subquery-not-work/8170/3 "2020-08-04T11:35:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Maniraj](https://avatars.discourse-cdn.com/v4/letter/m/51bf81/32.png) [@Maniraj](https://discourse.cakephp.org/u/Maniraj)\
**Post date:** [August 4, 2020, 12:37pm UTC](https://discourse.cakephp.org/t/subquery-not-work/8170/4 "2020-08-04T12:37:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![kentosama](https://avatars.discourse-cdn.com/v4/letter/k/bbce88/32.png) [@kentosama](https://discourse.cakephp.org/u/kentosama)\
**Post date:** [August 4, 2020, 3:43pm UTC](https://discourse.cakephp.org/t/subquery-not-work/8170/5 "2020-08-04T15:43:59Z")

</div>

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!