CODEWITHSUNDEEP
sqldatabaselaravel
Show More
Show More

Reputation: 45

Get the week start date and week end date from week number in raw query

!Get the week start date and week end date from week number in raw query #2 after group all ids in array

$test=DB::table('bookings')
->select([

     'bookings.organization_id',
     DB::raw('week(bookings.created_at) as week'), // this give me week number I need start date and end date starting date Sunday and end date Saturday 
     DB::raw('year(created_at) as year'),

the second after group by organization_id I need all booking id in array not string

// I try this but give like this "1,4,6,7,9"  I need ["1","4","6","7","9"]

DB::raw('group_concat(bookings.id) as bookings_id'),

Upvotes: 0

Views: 684

Answers (1)

Sehdev
Sehdev

Reputation: 5682

To get the start and end of week you can use this:

$test=DB::table('bookings')
->select([
'bookings.organization_id',
  DB::raw('DATE(bookings.created_at   + INTERVAL (1 - DAYOFWEEK(bookings.created_at)) DAY) as start_date'),
  DB::raw('DATE(bookings.created_at + INTERVAL (7 - DAYOFWEEK(bookings.created_at)) DAY) as end_date')
])
->get();

And for second question:

MySQL does not have concept of array type for data.

You need to explode the data into an array, using php code like this:

$bookingsIds = explode(',', $test->bookings_id);

Upvotes: 1

Related Questions