Mysql:从表1中选择行并测试表2中是否存在行 [英] Mysql : select rows from table 1 and test if rows exists in table 2

问题描述

这是我的查询:

$q = $this->dao->prepare('
        SELECT wishlists.id, wishlists.title, wishlists.comment, 
               wishlists.date, users.name, users.username, 
               wishlist_following.id as wid
        FROM wishlists
            LEFT JOIN users ON wishlists.user = users.id
            LEFT JOIN wishlist_following ON wishlist_following.user_id = 25 
                AND wishlist_following.wishlist_id = wishlists.id
            WHERE users.username = :username
        ORDER BY wishlists.date DESC
        ');

其中25"是访问者的 ID.我的表中有一个wid"键，如果当前用户(id:25)没有关注返回的心愿单，则该键为空.

Where "25" is the ID of the visitor. I have a "wid" key in my table which is null if the current user (id : 25) is not following the returned wishlist.

这是我的数据库和目标:

Here is my DB and goal :

用户(id、用户名等)

Users (id, username, etc.)

Whislists (id, title, comment, user_id) -> 与用户表的多对一关系

Whislists (id, title, comment, user_id) -> many-to-one relationship with users table

wishlist_following (id, user_id, Wishlist_id) -> 与用户表的多对多关系

wishlist_following (id, user_id, wishlist_id) -> many-to-many relationship with users table

我的目标:我想从用户的个人资料中获取愿望清单(从 URL 中提取用户名)并了解每个愿望清单当前用户(访问者)是否关注此列表.

My goal : I want to get the wishlists from user's profile (extracting username from URL) AND to know for each wishlist if the current user (the visitor) is following this list.

这是最好的方法吗?

推荐答案

此查询

SELECT w.title
FROM users u, wishlists w, wishlist_following wf
WHERE u.id = 1 -- visitor
AND w.user_id = 3 -- viewed profile
AND wf.user_id = u.id
AND wf.wishlist_id = w.id

应该从 id=3 的用户个人资料中返回愿望清单，然后是访问者 (id 1)

should return wishlists, followed by visitor (id 1), from user profile with id=3

好的，我知道了，试试这个:

Okay, I figure it out, try this:

SELECT w.title, wf.id
FROM users u
LEFT JOIN wishlists w ON w.user_id = 3 -- viewed profile
LEFT JOIN wishlist_following wf ON wf.wishlist_id = w.id
WHERE u.id = 1 -- visitor

简化版:

SELECT w.title, wf.id
FROM users u, wishlists w
LEFT JOIN wishlist_following wf ON wf.wishlist_id = w.id
WHERE u.id = 1 -- visitor
AND w.user_id = 3 -- viewed profile

这篇关于Mysql:从表1中选择行并测试表2中是否存在行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！