lundi 8 octobre 2018

How to compare and get data From DB Json value in Laravel 5.5

I am new in laravel. I want to compare and get data according to date if date matches the current date on grater then current date and pagination set according to get result.

'[\"28-09-2018\"]'
'[\"27-08-2018\",\"15-10-2018\",\"23-10-2018\"]'
'[\"27-11-2018\",\"15-12-2018\",\"23-12-2018\"]'

This is my SQL:

INSERT INTO `events` (`id`,  `title`, `organizer_name`, `email`, `number`, `alternate_number`, `event_category`, `event_dates`, `start_time`, `end_time`, `venue`, `long`, `lat`, `entry_fee`, `description`, `is_paid`, `is_activity`, `is_active`, `deleted_at`, `created_at`, `updated_at`) VALUES
(1,  'Test', 'Demo', 'msworldqueen@gmail.com', '9818043775', NULL, '12', '[\"28-09-2018\"]', '22:45:00', '23:00:00', 'Rajasthan 313001, India', '73.7124790', '24.5854450', '{\"Normal\":\"1000\"}', 'asdfasdfasdf', 1, '0', 1, NULL, '2018-08-27 00:31:37', '2018-08-27 00:31:37'),
(2,  'teststestse', 'sadfdsfa', 'admin@ngf.com', '999999999', '99999999', '0', '[\"27-08-2018\",\"15-10-2018\",\"23-10-2018\"]', '13:09:00', '12:09:00', 'slougia tastour 9014, Testour, Tunisia', '9.4422664', '36.5499000', NULL, 'test', 0, '0', 1, NULL, '2018-09-15 04:57:21', '2018-09-15 04:58:16');
(3,  'tesede,o', 'sadfdsfa', 'admin@ngf.com', '999999999', '99999999', '0', '[\"27-11-2018\",\"15-12-2018\",\"23-12-2018\"]', '13:09:00', '12:09:00', 'slougia tastour 9014, Testour, Tunisia', '9.4422664', '36.5499000', NULL, 'test', 0, '0', 1, NULL, '2018-09-15 04:57:21', '2018-09-15 04:58:16');

And I Try

 $curdate = date('d-m-Y');

    $events = Event::where('event_dates', '!=',  '')
                ->where(function ($query) use ($curdate) {

                    $query->whereRaw(
                        'JSON_CONTAINS(event_dates, '>=', $curdate)'
                    );
                    /*$query->whereRaw(
                        'JSON_CONTAINS(event_dates, \'["' . $curdate . '"]\')'
                    );*/

                    /*foreach (event_dates as $id) {
                        $query->orWhereRaw(
                            'JSON_CONTAINS(event_dates, \'["' . event_dates . '"]\')'
                        );
                    }*/

                    return $query;
                })->paginate(3);



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire