I have a User entity and a Hours entity. One user can have multiple hours. So a OneToMany for user-hours. And a ManyToOne for hours-user.

I used the symfony docs to create this. Symfony created this getHours() function in the User class for me that I want to use.

/**
 * @return Collection|Hours[]
 */
public function getHours(): Collection
{
    return $this->hours;
}

I want to use this because I need to create a page where I show basicly all the user data that exists. So Hours is just one example of the many other entities that are connected with User. I thought, if I relate them all with User, I will only need to fetch/query User in my controller and from there call $user->getHours() and the other entities.

Here comes the issue:

$user->getHours() returns ALL the hours connected to this user. But I only want the hours between two specific dates. Basicly I want to know how to do a WHERE statement. But obviously before all the hours are fetched from db. Since I know I could do getHours() and then do if statements to filter out the hours between a date. I dont want that.

In essential I want this repository function below, but without the need to keep calling these seperately from the User object. In this functions case I need the user id, which I already have when calling the User object. I hope this is possible and makes sense.

// Hours repository:
/**
 * Get hours registered by userID (id) which are registered on dates between date1 and date2.
 * 
 * Returns fields shown in ->select();
 * 
 * @return Hours[] Returns an array of Hours objects
 */
public function getHoursBetweenDate($id, $date1, $date2)
{
    return $this->createQueryBuilder('h')
        ->select('h.id', 'h.project_id', 'h.date', 'h.hours', 'h.travel_time', 'h.submitted_by', 'h.approved_by')
        ->where('h.user = :id')
        ->andWhere('h.date between :date1 and :date2')
        ->setParameter('id', $id)
        ->setParameter('date1', $date1)
        ->setParameter('date2', $date2)
        ->addOrderBy('h.date', 'ASC')
        ->getQuery()
        ->getResult()
    ;
}

I use SQL Server as db.