0

I make a tv program schedule and I need to sort dates that are more than 600 seconds apart

But it don't work ;(

Anyone know how to do it?

Many thanks in advance to those who will help me.

$dateMin = Carbon::now('Europe/Paris')
    ->endOfDay()
    ->addDay()
    ->addHours(-4)
    ->timestamp;
    
$dateMax = Carbon::now('Europe/Paris')
    ->endOfDay()
    ->addDay()
    ->timestamp;

$datas = Capsule::table('channels')
    ->select('channels.number',
        'channels.slug',
        'channels.display-name',
        'channels.icon',
        'programs.start',
        'programs.stop',
        'programs.title',
        'programs.img',
        'programs.thumbnail',
        'programs.desc'
    )
    ->where([
        ['start', '>', $dateMin],
        ['stop', '>', $dateMin],
        ['start', '<', $dateMax],
    ])
    ->whereRaw('stop - start > 601')
    ->leftJoin('programs', 'channels.slug', '=', 'programs.slug')
    ->orderBy('number')
    ->orderBy('start')
    ->get();

    return $datas->groupBy('display-name');
valgreg
  • 18
  • 2
  • What error are you getting? You can try to qualify the column names in the `where` statements - `'programs.start', '>', $dateMin` to see if that works – Donkarnash May 22 '22 at 20:01
  • I have no error, it returns all data even those that are longer than 600 seconds – valgreg May 23 '22 at 05:19
  • Welcome to Stackoverflow. Your current question is very broad and does not give any details on the issue. Please add clear information, how the code behaves and what the expected behavior is. – wasserholz May 23 '22 at 08:32
  • `whereRaw('stop - start > 601')` is filtering for difference between stop and start to be greater than 601, isn't it? So naturally the results will include records with difference between stop and start being greater than 600. If you want to filter records with difference less than 600 then the condition should be `whereRaw('stop - start < 600')` - is that right? – Donkarnash May 23 '22 at 18:32
  • Is right but i want filter records with difference greater than 600 – valgreg May 24 '22 at 04:21

1 Answers1

1

Without much context, the only thing I can think of is to try grouping the parameters in where clause

$datas = Capsule::table('channels')
    ->select('channels.number',
        'channels.slug',
        'channels.display-name',
        'channels.icon',
        'programs.start',
        'programs.stop',
        'programs.title',
        'programs.img',
        'programs.thumbnail',
        'programs.desc'
    )
    ->where(function($query) use($dateMin,$dateMax) {
        $query->where([
            ['start', '>', $dateMin],
            ['stop', '>', $dateMin],
            ['start', '<', $dateMax],
        ])
        ->whereRaw('stop - start > 601')
    })
    ->leftJoin('programs', 'channels.slug', '=', 'programs.slug')
    ->orderBy('number')
    ->orderBy('start')
    ->get();

If this doesn't work, try dumping the query sql for both, without parameter grouping (code in your question) and with parameter grouping (code above in my answer) and see the sql statement generated. You can get the query sql using toSql() in place of get().

Donkarnash
  • 12,433
  • 5
  • 26
  • 37