Query builder inner join on this and that (subquery with two parameters)

Hello,

I have a little problem with a query Im trying to make with the query builder :

So basically I have a table with some employee's ids, a counter, and a date (which is the year) what I want is getting the last available year for each employee in one Query.

I have managed to do it in raw mysql it looks like this :

SELECT * from compteur_conges inner join (
            SELECT employe_id,   MAX(year) AS year 
            FROM `compteur_conges` 
            WHERE employe_id   IN (".$idListString.") 
            GROUP BY employe_id
            ) as a 
            on a.employe_id = compteur_conges.employe_id 
            and a.year = compteur_conges.year

The problem I have is that I don't know on how to make a subquery with the query builder with ON this AND that (employe_id and year in this case).

If anyone knows how to do this that would be a great help ! Or if you have a complete different idea on how to do it, i am open to suggestions !

SilenceBringer

4 years ago

Level 55

@pedro4000 according to your needs

what I want is getting the last available year for each employee in one Query.

you can use you subquery only

SELECT employe_id,   MAX(year) AS year 
            FROM `compteur_conges` 
            WHERE employe_id   IN (".$idListString.") 
            GROUP BY employe_id

and you will have last year for each employe_id

pedro4000

4 years ago

Level 1

Hey, thanks for the answer,

If I only use the subquery, I have the employe_id and the year, but what I need is the whole line corresponding to this entry : I have two counters lets say

Employe_id  | Year | Counter_1 | Counter_2
		788	| 2021 |    35 	   |  	4

And I need all that info, the problem is that if I only use the subquery, I will have the first row of employe_id = 788 so the counter are the first one encountered and the year is the max one, so I will have the line correponding to year 2017 for example.

So my answer to that was to do this inner join to get the right info, can't see another way for now

pedro4000

4 years ago

Level 1

don't know if it will be useful to anyone but I found a way :

        select('compteur_conges.id as compteur_id', 'compteur_conges.*', 'oe.*')
        ->join(DB::raw('(SELECT employe_id, MAX(year) AS year
                         FROM compteur_conges 
                         WHERE employe_id IN ('.$idEmployesString.')
                         GROUP BY employe_id) cc1'), function($join)
            {
                $join->on('compteur_conges.employe_id', '=', 'cc1.employe_id');
                $join->on('compteur_conges.year', '=', 'cc1.year');
            })
        ->leftJoin('openpap.employes as oe', 'oe.id', '=', 'compteur_conges.employe_id')
        ->orderBy('oe.nom')
        ->paginate(20);

Please or to participate in this conversation.