CODEWITHSUNDEEP
mysqlsqlmariadb
Luka
Luka

Reputation: 758

How to Order By a Subquery result

I have 2 DB tables, Events and Event_Dates so each Event can have multiple Dates. I now try to read out the next upcoming event and a list of all upcoming events ordering by the date.

So I created the following query with a subquery, but the order by is ignored. After some research I realised that this is common, but I am still not sure how to work around this. Could somebody give me a hint?

MyEvent Table stores the Event Information like Location, Event name and Description. The Event_Dates table stores the event_id and one or multiple dates, if the event goes over more than 1 day. I

f I just check my Subquery then I get the correct order by the event_date, but the Main Query ignores that order by event_dates. So if I have the following in my event dates table

id | event_id | event_date
1  | 58 | 2019-02-23
2  | 59 | 2019-02-24
3  | 60 | 2019-02-09

The subquery returns 60, 58, 59 which is correct so I would like to display the events in that order, but instead it shows 58, 59, 60.

Select * FROM events WHERE id in(
 select event_id from event_dates 
    where event_date >= NOW() order by ABS(DATEDIFF(NOW(),event_date))
)

UPDATE-

Great and quick help got me the solution and this is how it should look in Laravel:

         $events = Event::select('events.*')
                ->distinct('events.id')
                ->join('event_dates','event_dates.event_id','=','events.id')
                ->where('event_date', '>=', \DB::raw('curdate()'))  
                ->where('published',1)    
                ->orderBy('event_dates.event_date')   
                ->get();

Upvotes: 0

Views: 69

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You have recurring events. An event can have occured mutliple times in the past and can occur mutliple times in the future, too. You want to show future events ordered by their date. That's a mere inner join:

select e.*
from events e 
join event_dates ed on ed.event_id = e.id and ed.event_date >= now()
order by ed.event_date;

With events Christmas and Easter for the years 2017, 2018, 2019, 2020, you'd get:

Easter
Christmas
Easter
Christmas

Upvotes: 1

GMB
GMB

Reputation: 222432

It looks to me like you just need a JOIN between two tables, properly filtered and sorted :

SELECT e.*, d.*
FROM events e
INNER JOIN event_dates d ON d.event_id = e.event_id AND d.event_date >= NOW()
ORDER BY d.event_date

Upvotes: 1

Related Questions