John Davidson

Message:

I would appreciate some help regarding laravel/octobercms query builder.
I have two models/tables as follows:

CV model has many Experiences, Experience model belongs to CV.

CVs table  

| id | user_id |  

| --- | ------ |  

| 1 | 11 |  

| 2 | 22 |  







Experiences table  

| id | cv_id | from | to |  

| --- | ------ | ----- | --- |  

| 1 | 1 | 2019-01-01 | 2020-01-01 |  

| 2 | 1 | 2020-05-02 | 2021-09-01 |   

| 3 | 2 | 2019-01-01 | 2019-05-01 |  

| 4 | 2 | 2020-01-01 | 2021-05-01 |   

I'm trying to use query builder to filter CV's based on Experiences duration. For example, CV with id: 2, has two experiences with id 3 and 4, each with 4 moths of experience, so in total CV with id: 2 has 8 months of experience.

So far I tried variations of:

$query = Cv::query()->whereHas('experiences', function ($query) {

        $query->addSelect(DB::raw("SUM(DATEDIFF(COALESCE(`to`, CURDATE()), `from`)) AS sum_experience"));

    });

Above produces:

select * from `cvs` where exists (select *, SUM(DATEDIFF(COALESCE(`to`, CURDATE()), `from`)) AS sum_experience from `cv_expriences` where `cvs`.`id` = `cv_expriences`.`cv_id`)

But I don't know how can I compare sum_experience value, to a value I need.
Any help is appreciated.

---

My Answer:
>whereHas('experiences', function ($query) {
$query->selectRaw('SUM(DATEDIFF(to, from)) as total_experience')
->groupBy('cv_id')
->having('total_experience', '>=', 8);
})->get();

This query will filter CVs based on the total experience calculated from the sum of the differences between the 'to' and 'from' dates in the Experiences table. The 'having' clause ensures that only CVs with a total experience of 8 months or more are returned.