Count Columns query with between specific time

Question

There are two tables, named as exchanges table and finaltrades table.

Exchanges table has START_TIME field (with format : 00:00:00).

Another finaltrades table has exchange_id.

I want to count entries only which come between start_time to start_time+1hrs from final trades table. FOR EXAMPLE: If start_time has 09:15:00, then count between 09:15:00 to 10:15:00 records only.

$Count_Trades = FinalTrade::where('user_id', '=', $user_id)
                             ->whereColumn('start_time', '+', 01:00:00)
                             ->count();

How can i correct this code by relationship?

Exchnages table

finaltrade table

Answer 1

You should define a relationship between FinalTrade Model and Exchanges Model.

This defines the relationship in FinalTrade Model.

public function exchanges(){
    return $this->hasOne('App\EXCHANGE_MODEL_NAME', 'id', 'exchange_id');
}

you can find more details on relationships here.

Now you can find your records using this query

$countTrades = App\FinalTrade::where('user_id', '=', $user_id)->exchanges()
    ->where('start_time', '>=', $your_desired_time)->where('start_time' , '<=' , strtotime($your_desired_time) + 60*60)->count();