samedi 18 juin 2016

Laravel: Query Builder Join Multi Tables

I have 3 tables:

users_tb with columns:

user_id (pk)
user_name

device_tb with columns:

user_id (fk)
device_id (pk)
device_name

part_tb with columns:

deive_id (fk)
part_id (pk)
part_name
user_buy (fk)

Relationship:

device_tb one to many part_tb (1 device can create from to many part)

user_tb one to many device_tb (1 people can create to many device)

user_tb one to many part_tb (1 people can buy to many device)

How i can count the total parts used for all device per user?

Example: device: PC1 - created from parts: mouse, keyboard, cpu - by user: Allen

Result: User Allen used 3 parts

"User" buy parts different "User" created device, never same one user.

Edited:

My code

DB::table('users_tb')->leftjoin('device_tb', 'device_tb.user_id', '=', 'users_tb.user_id')
->leftjoin('part_tb', 'device_tb.device_id', '=', 'part_tb.device_id')
->select('users_tb.user_name', DB::raw("(SELECT COUNT(part_tb.id) FROM part_tb) AS counter"))
->where('users_tb.level', '>', 7)
->havingRaw("(SELECT COUNT(part_tb.id) FROM part_tb) > 0")
->groupBy('device_tb.device_id')
->orderBy('users_tb.user_name')
->get()->toArray();

My result:

Name1 - 3
Name1 - 4
Name1 - 3
Name2 - 11
Name2 - 2

How i can SUM counter? Example:

Name1 - 10 (3+4+3)
Name2 - 13 (11 + 2)

I'm so sorry about my English :((

Any help will be appreciated.

Aucun commentaire:

Enregistrer un commentaire