php - MYSQL中左外连接错误结果

Question

我正在使用 MySQL 连接。我有两个表需要连接。第一个表包含所有房地产属性，第二个表包含对该属性添加收藏夹的用户。现在我想为登录的用户显示带有最喜欢图标的所有属性。我在 MySQL 中编写了以下查询。但这个查询返回了我所有的记录。

SELECT a. * , b.property_id AS fav, b.user_id
FROM `property_for_sale` a
LEFT OUTER JOIN `cpo_favourite_property` b ON a.id = b.property_id
WHERE a.property_type = 'Commercial'
UNION
SELECT a. * , b.property_id AS fav, b.user_id
FROM `property_for_rent` a
LEFT OUTER JOIN `cpo_favourite_property` b ON a.id = b.property_id
WHERE a.property_type = 'Commercial'
ORDER BY id DESC

这还有一个问题。如果多个用户在收藏夹中添加相同的属性，则此查询将返回重复的属性

这是我使用此查询时从 api 获得的最终输出

{
    "success": "1",
    "user": [
        {
            "id": "1266",
            "date": "2016-01-25",
            "date_25_days": "2016-02-19",
            "date_30_days": "2016-02-24",
            "time": "04:47:40 PM",
            "user_name": "anil123", 
            "fileupload": "186_39151.jpg",
            "image2": "Null",
            "image3": "Null",
            "image4": "Null",
            "description": "gg",
            "fav": "1266",
            "user_id": "19"
        },
        {
            "id": "1266",
            "date": "2016-01-25",
            "date_25_days": "2016-02-19",
            "date_30_days": "2016-02-24",
            "time": "04:47:40 PM",
            "user_name": "anil123", 
            "fileupload": "186_39151.jpg",
            "image2": "Null",
            "image3": "Null",
            "image4": "Null",
            "description": "gg",
            "fav": "1266",
            "user_id": "480"
        },
        {
            "id": "1144",
            "date": "2015-12-07",
            "date_25_days": "2016-01-01",
            "date_30_days": "2016-01-06",
            "time": "05:45:30 PM",
            "user_name": "Realtyup Estate Agency", 
            "fileupload": "464_IMG-20140812-WA0063.jpg",
            "image2": "821_IMG-20140812-WA0064.jpg",
            "image3": "Null",
            "image4": "Null",
            "description": "Commercial showroom at Mansa Devi Complex Sector 4 Panchkula.Ground/basement/first/second fully constructed floors.Ample parking.Corner three side open.Well suited for any kind of business establishment",
            "fav": null,
            "user_id": null
        },
        {
            "id": "625",
            "date": "2016-02-01",
            "date_25_days": "2016-02-25",
            "date_30_days": "2016-03-01",
            "time": "02:25:40 AM",
            "user_name": "", 
            "fileupload": "Null",
            "image2": "Null",
            "image3": "Null",
            "image4": "Null",
            "description": "VjD7Gu http://www.FyLitCl7Pf7kjQdDUOLQOuaxTXbj5iNG.com",
            "fav": null,
            "user_id": null
        },
        {
            "id": "624",
            "date": "2016-01-31",
            "date_25_days": "2016-02-25",
            "date_30_days": "2016-03-01",
            "time": "05:44:10 PM",
            "user_name": "", 
            "fileupload": "Null",
            "image2": "Null",
            "image3": "Null",
            "image4": "Null",
            "description": "DIMgVX http://www.FyLitCl7Pf7kjQdDUOLQOuaxTXbj5iNG.com",
            "fav": null,
            "user_id": null
        },
        {
            "id": "623",
            "date": "2016-01-31",
            "date_25_days": "2016-02-25",
            "date_30_days": "2016-03-01",
            "time": "12:59:54 PM",
            "user_name": "", 
            "fileupload": "Null",
            "image2": "Null",
            "image3": "Null",
            "image4": "Null",
            "description": "2lx6j8 http://www.FyLitCl7Pf7kjQdDUOLQOuaxTXbj5iNG.com",
            "fav": null,
            "user_id": null
        }
    ]
}

Answer 1

我认为您缺少对当前登录用户的限制。将其添加到 ON 子句(:currentUserId 占位符):

SELECT a. * , b.property_id AS fav, b.user_id
FROM `property_for_sale` a
LEFT OUTER JOIN `cpo_favourite_property` b ON a.id = b.property_id AND b.user_id = :currentUserId
WHERE a.property_type = 'Commercial'
UNION
SELECT a. * , b.property_id AS fav, b.user_id
FROM `property_for_rent` a
LEFT OUTER JOIN `cpo_favourite_property` b ON a.id = b.property_id AND b.user_id = :currentUserId
WHERE a.property_type = 'Commercial'
ORDER BY id DESC