How to get value based on hasmany table first value?

Level 75

You will need a where clause, and an orderby if you want to order it.

Level 34

@sin2san

$startDate = '2021/04/08';
$endDate = '2021/04/11';

$batch = Batch::with(['batchSchedules' => function($query) use ($startDate, $endDate){
	$query->whereBetween('Date', $startDate, $endDate)
		->orderBy('Date', 'desc');
}])->find(3);

And to retrieve the first batchSchedules, do this:

$batch->batchSchedules()->first();

Since your batch schedules are hasMany relationship, it will return a collection of BatchSchedules so you need to get the only first item.

Level 1

@neilstee

This is what I am doing exactly but my concern is I need to return batches based on batch schedule first row value.

Level 34

@sin2san then just change with to whereHas:

$batch = Batch::whereHas(['batchSchedules' => function($query) use ($startDate, $endDate){
	$query->whereBetween('Date', $startDate, $endDate)
		->orderBy('Date', 'desc');
}])->find(3);

Level 1

$allData = Batch::whereHas(['schedule' => function($query) use ($aStartDate, $aEndDate){
	$query->whereBetween('date', [$aStartDate, $aEndDate])->orderBy('date', 'desc');
}])->paginate(10);

dd($allData);

ErrorException (E_WARNING)
strpos() expects parameter 1 to be string, array given

I am getting this error.

Level 34

@sin2san sorry but it should be two parameters not array

$allData = Batch::whereHas('schedule', function($query) use ($aStartDate, $aEndDate){
	$query->whereBetween('date', [$aStartDate, $aEndDate])->orderBy('date', 'desc');
})->paginate(10);

dd($allData);

Level 1

On trying this it's returning based on all schedule dates not the first date.

Level 34

@sin2san that's why I told you to run first. or limit it by 1

$allData = Batch::whereHas('schedule', function($query) use ($aStartDate, $aEndDate){
	$query->whereBetween('date', [$aStartDate, $aEndDate])->orderBy('date', 'desc')->limit(1);
})->paginate(10);

dd($allData);

Level 1

On trying first()

Illuminate \ Database \ QueryException (42S22)
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'batches.id' in 'where clause' (SQL: select * from `batch_schedule` where `batches`.`id` = `batch_schedule`.`batch_id` and `date` between 2021/04/12 and 2021/04/30 and `batch_schedule`.`deleted_at` is null limit 1)

On trying limit(1) it still returns based on all schedule dates.

Level 34

@sin2san can you try this:

use Illuminate\Database\Eloquent\Builder;

$allData = Batch::whereHas('schedule', function(Builder $query) use ($aStartDate, $aEndDate){
	$query->whereBetween('date', [$aStartDate, $aEndDate])->orderBy('date', 'desc')->limit(1);
})->paginate(10);

Check the docs here: https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence

Level 34

Also, make sure that $query->whereBetween('date', [$aStartDate, $aEndDate] is correct. Try to debug your code by adding:

DB::enableQueryLog();
// your query here
dd(DB::getQueryLog());

Check if SQL is correct.

Level 1

It's returning based on all data values from the schedule table not based on first row value.