CODEWITHSUNDEEP
phpmysqllaravel
Soul Coder
Soul Coder

Reputation: 804

How to join other table and count the row in laravel?

I am trying to count how many require position there are for each jobseeker. I have two tables: jobseeker and jobposition.

jobseeker: 

+----+---------+-----------+----------------+
| id | fb_name | fullname  | desireposition |
+----+---------+-----------+----------------+
|  1 | John    | John Cena |              3 |
|  2 | Christ  | Christ    |              4 |
|  3 | Thomas  | Cfitcher  |              2 |
+----+---------+-----------+----------------+

and jobposition: 

+----+--------+------------------+
| id | job_id | require_position |
+----+--------+------------------+
|  1 |     12 |                3 |
|  2 |     13 |                3 |
|  3 |     14 |                4 |
|  4 |     15 |                5 |
|  5 |     16 |                4 |
|  6 |     17 |                3 |
+----+--------+------------------+

My expected result is:

+----+---------+-----------+----------------+-----------------------+
| id | fb_name | fullname  | desireposition | total_requireposition |
+----+---------+-----------+----------------+-----------------------+
|  1 | John    | John Cena |              3 |                     3 |
|  2 | Christ  | Christ    |              4 |                     2 |
|  3 | Thomas  | Cfitcher  |              2 |                     0 |
+----+---------+-----------+----------------+-----------------------+

I want to count how many require position there for each jobseeker.

Here is what I tried using crossJoin, but am unsure which join I actually need to be using.

$jobseekers = Jobseeker::crossJoin('jobpositions')
              >select('fullname','fb_name','desire_position', DB::raw('count(require_position) as total_requireposition'))
            ->groupBy('fullname')->paginate(10);

Can anyone help guide me? Any help would be highly appreciated.

Upvotes: 1

Views: 70

Answers (1)

Barmar
Barmar

Reputation: 780724

The regular MySQL query you want is:

SELECT s.id, fullname, fb_name, desireposition, IFNULL(COUNT(require_position), 0) AS require_position
FROM jobseeker AS s
LEFT JOIN jobposition AS p ON s.desireposition = p.require_position
GROUP BY s.id

I don't use Laravel, but I think the translation would be:

$Jobseeker->select('fullname','fb_name','desire_position', DB::raw('IFNULL(COUNT(require_position), 0) as total_requireposition'))
        ->leftjoin('jobposition', 'desireposition', '=', 'require_position')
        ->groupBy('jobseeker.id')
        ->paginate(10)

Upvotes: 4

Related Questions