Table query with CONCAT and JOIN?

Question

Table query with CONCAT and JOIN?

Navigation

#1 by (3 votes)

2

I need to get a collection as a result of querying in 3 tables using concat and join , the image below shows the relationship between them:

Itriedtousethecodebelow

$teste=DB::table('clientesasc')->leftjoin('reservasasr','r.codReserva','=','b.ce_denunciacrime')->leftjoin('produtosasp','p.codProduto','=','r.codReserva')->select('p.codProduto','r.codReserva',DB::raw("CONCAT(c.codCliente,', ') as codCliente"))
      ->limit(1)
      ->get();

dd($teste);

% w / o result should be something like this:

#items: array:1[
0 => {#498 
  +"codProduto": 1
  +"codReserva": 2
  +"codCliente": 20,30,40
}]

Imagine this table below, the result would be the first two columns plus the concatenation of the third values, resulting in array shown above.

<table border=1>
<tr>
<th>codProduto</th><th>codReserva</th><th>codCliente</th>
<tr>
<td>1</td><td>2</td><td>20</td>
</tr>
<tr>
<td>2</td><td>2</td><td>30</td>
</tr>
<tr>
<td>3</td><td>2</td><td>40</td>
</tr>

join laravel

asked by anonymous 10.07.2018 / 13:49

1 answer

How can I do to get the insert in the log with logged in user? Identity - Asp.net core 2.0 Best way to find out if the user is online / offline

score 3 · Answer 1

I was able to resolve using 'GROUP_CONCAT' as in the query below:

$teste = DB::table('clientes as c')
    ->leftjoin('reservas as r', 'r.codReserva', '=', 'b.ce_denunciacrime')
    ->leftjoin('produtos as p', 'p.codProduto', '=', 'r.codReserva')
    ->select('p.codProduto','r.codReserva',
              DB::raw("GROUP_CONCAT(c.codCliente,'') as codCliente"))
    ->limit(1)
    ->groupBy('p.codProduto','r.codReserva')
    ->get();

dd($teste);