CODEWITHSUNDEEP
sqllaravellaravel-query-builder
Farveaz
Farveaz

Reputation: 608

Comma separated column values in a laravel join query

I have a laravel webapp in which my database is structured via the below tables. The relationship is that transactions hasmany loading_hire_agreements via lhas_transactions. 

Table name: transactions 
| id | date              | number |
|----|-------------------|--------|
| 1  | 12:00:00 00:00:00 | TRN#1  |
| 2  | 10:00:00 00:00:00 | TRN#2  |

Table name: loading_hire_agreements
| id | number |
|----|--------|
| 1  | JY1001 |
| 2  | JY1002 |
| 3  | JY1003 |

Table name: lhas_transactions
| transction_id | lha_id |
|---------------|--------|
| 1             | 1      |
| 1             | 2      |
| 2             | 3      |

I want to write a join statement using laravel's DB:table()->join(\* any relevant join *\) so that the below output is generated.

| transactions.id  | transactions.number | loading_hire_agreements.number |
|------------------|---------------------|--------------------------------|
| 1                | TRN#1               | JY1001, JY1002                 |
| 2                | TRN#2               | JY1003                         |

I "have" to use DB:table() because I am connecting this query to a front-end datatable service.

Upvotes: 2

Views: 2455

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521804

Try using GROUP_CONCAT and join all three tables:

$users = DB::table('transactions')
         ->join('lhas_transactions as lt', 'transactions.id', '=', 'lt.transaction_id')
         ->join('loading_hire_agreements as lha', 'lt.lha_id', '=', 'lha.id')
         ->select(DB::raw('transactions.id, transactions.number, GROUP_CONCAT(lha.number ORDER BY lha.id) AS agreements'))
         ->groupBy('transactions.id', 'transactions.number')
         ->get();

Upvotes: 4

Related Questions