Reputation: 221
Code
SELECT t1.*
FROM table_one_items t1
INNER JOIN (SELECT t2.item_id,
t2.date_favorited
FROM table_two_favorites t2
WHERE t2.user_id IN( 1, 2, 3 )) t3
ON t1.item_id = t3.item_id
OR t1.user_id IN( 1, 2, 3 )
WHERE t1.published = 1
GROUP BY t1.item_id
ORDER BY t3.date_favorited DESC,
t1.date_created DESC
Question:
I'm looking for the items in chronological order
despite coming from a table and a subquery
table with their own date columns. How do I go about doing this?
What I've tried:
I have tried moving the t3.date
around and ordering the subquery
itself but that doesn't work. Any attempt to modify the subquery changes the result set, and I can't think of another way to write it to get the same results but with the correct ordering. I'm aware there might be a better way to write this query, but I haven't been able to figure that out either. I feel lucky this works!
Additional Info:
The first table is an index of all items, while the second table is a "favorites" table that only includes items that have been favorited by users. With t1.item_id = t3.item_id OR t1.user_id IN(1,2,3)
I'm essentially getting items in t1 that are made by these users OR items that have been favorited by these users.
Examples:
Table One "Item Table": (Date is "date created")
item_id 1 user_id 2 date_created 2015-01-1 00:00:00
item_id 2 user_id 2 date_created 2015-01-3 00:00:00
item_id 3 user_id 1 date_created 2015-01-5 00:00:00
item_id 4 user_id 3 date_created 2015-01-7 00:00:00
Table Two "User Favorites": (Date is "date favorited")
item_id 2 user_id 2 date_favorited 2015-01-6 00:00:00
item_id 1 user_id 2 date_favorited 2015-01-6 00:00:00
item_id 1 user_id 3 date_favorited 2015-01-8 00:00:00
Desired Results:
item_id 3 (ordered by date_created: 2015-01-5 00:00:00)
item_id 4 (ordered by date_created: 2015-01-7 00:00:00)
item_id 2 (ordered by date_favorited: 2015-01-6 00:00:00)
item_id 1 (ordered by date_favorited: 2015-01-8 00:00:00)
Current Results:
item_id 1 (ordered by date_created)
item_id 2 (ordered by date_created)
item_id 3 (ordered by date_created)
item_id 4 (ordered by date_created)
Any help would be greatly appreciated. (And I'll be sure to mark the answer!)
Upvotes: 0
Views: 532
Reputation: 3431
Try using a CASE statement in the ORDER BY to select which date you want, so:
ORDER BY
CASE
WHEN t3.date > t1.date AND t3.item_id = t1.item_id THEN t3.date
ELSE t1.date
END DESC
If you want to sort by the maximum of the two dates. However I'm not sure your subquery would be doing what you expect; unless items created by the user are always in table_two regardless of whether they had favorited them
Upvotes: 1
Reputation: 893
Try following with order by clause:
ORDER BY
COALESCE(t3.date,t1.date) DESC;
Above query can help ordering for your requirements though you have opportunity to tune your SQL.
Upvotes: 0