php - MySQL - PHP 按内部连接表 ID 而不是选定表 ID 排序？

Question

我有以下查询，我想按 UserView ID 降序排序，这似乎是一个非常基本的问题，但我不知道该怎么做，我想继续选择 User.*，就像现在一样，但按 UsersView 排序表主键 desc，有什么想法吗？

 $phql = " SELECT *
                      FROM User
                      WHERE user_id IN
                      ( SELECT user_to
                        FROM UsersView
                        WHERE user_from=:user_to: )
                      AND gender!=:gender:
                      AND user_id NOT IN (".implode(",",$this->user->getBlocked()).")
                      ORDER BY last_visit DESC
                 /* order by should be usersview.id DESC */ 
                      "; 

运气好吗？

Answer 1

根据您的数据模型，您可以额外连接回 UsersView，以便您可以在 ORDER BY 子句中使用它的 id 列。

 $phql = " SELECT *
                  FROM User
        JOIN UsersView on User.user_id = UsersView.user_to
                  WHERE user_id IN
                  ( SELECT user_to
                    FROM UsersView
                    WHERE user_from=:user_to: )
                  AND gender!=:gender:
                  AND user_id NOT IN (".implode(",",$this->user->getBlocked()).")
                  ORDER BY UsersView.id DESC"; 

您也可以避免使用嵌套选择来执行筛选，而是使用连接表来执行筛选:

 $phql = " SELECT *
                  FROM User
        JOIN UsersView on User.user_id = UsersView.user_to
                  WHERE user_from=:user_to:
                  AND gender!=:gender:
                  AND user_id NOT IN (".implode(",",$this->user->getBlocked()).")
                  ORDER BY UsersView.id DESC";