CODEWITHSUNDEEP
mysqlsql
Zarwell
Zarwell

Reputation: 121

MySQL - a subquery and a WHERE IN

I have a table of items properties, and a table of user_favorites structured like this:

When a user "favorites" an item it adds a row to this table using the item id, the user id, and sets the favorite property to 1 (if they "unfavorite" it's changed to 0).

I'm trying to figure out how to show the user's favorites when they view lists of products.

I currently do a query based on user search input and get an array of product IDs. Now I want to do a query amongst those products to create a table of product information - and I want it to include whether or not the item is a user favorite.

I've tried something like this:

select i.tid, i.name as name, i.image as image,  (SELECT favorite FROM user_favorites WHERE user_id=77 ) as favorite
    FROM items i 

    left join user_favorites ufav on (ufav.item_id = i.tid)
    WHERE i.tid IN (79, 98, 105 . . .) 
    order by favorite DESC, name ASC

..but of course I get a Subquery returns more than 1 row error

I've also tried something like this:

   select i.tid, i.name as name, i.image as image,  ufav.favorite as favorite
    FROM items i 
    left join user_favorites ufav on (ufav.item_id = i.tid)
    WHERE i.tid IN (79, 98, 105 . . .) AND ufav.user_id=77
    order by favorite DESC, name ASC

. . . but this only returns items that are favorited (as you'd expect). I'd like to return all the items in the list of IDs, but I also want to know which items have not been "favorited".

Can I do this in one query, or is my best option to run two separate queries?

Upvotes: 5

Views: 155

Answers (3)

Zarwell
Zarwell

Reputation: 121

Here's the approach I ended up taking:

(
    SELECT i.tid, i.name as name, i.image, 1 AS favorite
    FROM items i
    LEFT JOIN user_favorites ufav on (ufav.item_id = i.tid)
    WHERE ufav.user_id = //the user id
    AND i.tid IN (79, 98, 105 . . .) 
 )

UNION DISTINCT

 (
     SELECT i.tid, i.name as name, i.image as image, 0 AS favorite
     FROM items i 
     WHERE NOT EXISTS (SELECT *
                       FROM user_favorites ufav
                       WHERE ufav.item_id = i.tid
                       AND ufav.user_id = //the user id
                       AND ufav.favorite=1
                       )
     AND i.tid IN (79, 98, 105 . . .)  
  ) 
  ORDER BY favorite DESC , i.name ASC

any issues with this? It seems to be working like I'd hoped, so far...

Upvotes: 1

John Woo
John Woo

Reputation: 263933

I think you need to use CROSS JOIN here because you want to show all items to all users, example

SELECT  d.item_ID, d.Itemname, d.user_ID, 
        COALESCE(c.`favorite`, 0) Favorite
FROM
        (
          SELECT   a.item_ID, a.Itemname, b.user_ID
          FROM     items a CROSS JOIN 
                   (
                       SELECT DISTINCT user_ID
                       FROM user_favorites
                   ) b
        ) d LEFT JOIN user_favorites c
            ON c.item_ID = d.item_ID AND
              c.user_ID = d.user_ID
-- WHERE d.user_ID = 1        -- this is you condition
ORDER BY d.user_ID, d.Item_ID

Upvotes: 2

Sashi Kant
Sashi Kant

Reputation: 13465

Try this ::

select 
i.tid, 
i.name as name, 
i.image as image,  
ifnull(favorite,false) as isFavorite

FROM items i 
left join user_favorites ufav on (ufav.item_id = i.tid)
WHERE i.tid IN (79, 98, 105 . . .)  and ufav.user_id=77
    order by favorite DESC, name ASC

Upvotes: 1

Related Questions