Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT id_event , event_title, event_details, dates.event_date, id_show, id_category, distance FROM(
- SELECT event.id_event id_event, event_title, event_details, min(event_date_time) event_date_time, event_showtime.id_show , event_category.id_category id_category, ( 6371 * ACOS( COS( RADIANS( '49.20513921227407' ) ) * COS( RADIANS( event_showtime.latitude ) ) * COS( RADIANS( event_showtime.longitude ) - RADIANS( '18.762441839599678' ) ) + SIN( RADIANS( '49.20513921227407' ) ) * SIN( RADIANS( event_showtime.latitude ) ) ) ) AS distance
- FROM event
- JOIN event_showtime ON event.id_event = event_showtime.id_event
- JOIN event_category ON event.id_category = event_category.id_category
- JOIN ( SELECT id_event, min(event_date_time) as event_date_time FROM event_showtime
- WHERE event_date_time > NOW()
- GROUP BY id_event ) AS dates ON event.id_event = dates.id_event
- WHERE event_date_time > NOW()
- GROUP BY event.id_event
- HAVING distance < '5'
- ORDER BY distance
- LIMIT 0 , 20
- )t
- SELECT
- es.id_show,
- e.id_event ,
- e.event_title,
- e.event_details,
- d.event_date_time,
- ec.id_category,
- ( 6371 * ACOS( COS( RADIANS( '49.20513921227407' ) ) * COS( RADIANS( es.latitude ) ) * COS( RADIANS( es.longitude ) - RADIANS( '18.762441839599678' ) ) + SIN( RADIANS( '49.20513921227407' ) ) * SIN( RADIANS( es.latitude ) ) ) ) AS distance
- FROM event e
- JOIN event_showtime es ON e.id_event = es.id_event
- JOIN event_category ec ON e.id_category = ec.id_category
- JOIN ( SELECT id_event,
- min(event_date_time) as event_date_time
- FROM event_showtime
- WHERE event_date_time > NOW()
- GROUP BY id_event ) AS d
- ON (es.id_event = d.id_event AND es.event_date_time =d.event_date_time)
- WHERE d.event_date_time > NOW()
- GROUP BY e.id_event
- -- HAVING distance < '5'
- ORDER BY distance
- LIMIT 0 , 20
- `id_show`, `id_event`, `latitude`, `longitude`, `event_date_time`
- (1, 1, 49.2016762922894, 18.7615620750428, '2014-03-31 16:13:17'),
- (2, 1, 49.2016762922894, 18.7615620750428, '2014-04-01 20:00:00'),
- (3, 2, 49.2113914818564, 18.7520992416382, '2014-03-31 15:00:00'),
- (4, 2, 49.0545135142313, 20.2952223676682, '2014-04-16 11:00:00'),
- (5, 2, 49.2113914818564, 18.7520992416382, '2014-04-23 11:00:00'),
- (6, 2, 49.0545135142313, 20.2952223676682, '2014-04-30 11:00:00'),
- (7, 2, 49.2016762922894, 18.7615620750428, '2014-04-29 12:00:00'),
- (8, 1, 49.2016762922894, 18.7615620750428, '2014-04-24 12:00:00');
- SELECT t.* FROM (
- SELECT
- es.id_show,
- e.id_event,
- e.event_title,
- e.event_details,
- MIN(es.event_date_time) event_date_time,
- ec.id_category,
- es.distance
- FROM
- event e
- JOIN event_category ec
- ON e.id_category = ec.id_category
- JOIN
- (SELECT
- `id_show`,
- `id_event`,
- `latitude`,
- `longitude`,
- event_date_time,
- (
- 6371 * ACOS(
- COS(RADIANS('49.20513921227407')) * COS(RADIANS(event_showtime.latitude)) * COS(
- RADIANS(event_showtime.longitude) - RADIANS('18.762441839599678')
- ) + SIN(RADIANS('49.20513921227407')) * SIN(RADIANS(event_showtime.latitude))
- )
- ) AS distance
- FROM
- event_showtime
- WHERE `event_date_time` > NOW()
- HAVING distance < 5) es
- ON (e.id_event = es.id_event)
- GROUP BY e.id_event
- ) t
- JOIN `event_showtime` es USING(id_event,event_date_time)
- ORDER BY t.distance
- LIMIT 0, 20
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement