fetch multiple records from a single table having same id-CodePudding

I am writing a function in laravel 5.8 in which I want to get sum of the user amount that lies within my selected range, I am using the two input fields in view which they get the price range "T0 and From" e.g if the user enters 100 and 1000, it should show the list of the users who has amount > 100 and amount < 1000. The problem is there are multiple records of the same users as shown below in the table. I want to sum the amount of the users and show the user if their amount is within range

table name= deposits

<table>
<thead>
<tr>
<th>id</th>
<th>user_id</th>
<th>name</th>
<th>amount</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>12</td>
<td>ali</td>
<td>100</td>
</tr>
<tr>
<td>2</td>
<td>12</td>
<td>ali</td>
<td>800</td>
</tr>
<tr>
<td>3</td>
<td>12</td>
<td>ali</td>
<td>50</td>
</tr>
<tr>
<td>4</td>
<td>15</td>
<td>khan</td>
<td>1100</td>
</tr>
<tr>
<td>6</td>
<td>9</td>
<td>james</td>
<td>850</td>
</tr>
<tr>
<td>7</td>
<td>9</td>
<td>james</td>
<td>90</td>
</tr>
</tbody>
</table>

CodePudding user response：

This behavior of your query is correct, to get what you need it is necessary to work on the query. Assuming this fields in your deposits table: id, user_id, name, amount in according with data showing in the table.

You need to sum the amount grouping by user_id

Example query:

$deposit = Deposits::select(
                       'id',
                       'user_id',
                       'name',
                       'sum(amount)',
                    )->whereBetween('amount', [100, 1000])
                    ->groupBy('user_id')->get();

CodePudding user response：

DB::table('deposits')
        ->selectRaw('user_id, SUM(amount) as total_amount')
        ->where('amount', '>', '100')
        ->where('amount', '<', '1000')
        ->groupBy('user_id')
        ->get();

Result is:

[
  {
    "user_id":9,
    "total_amount":850
  },
  {
    "user_id":12,
    "total_amount":800
  }
]

CodePudding user response：

app('db')
   ->table('users')
   ->select('name')
   ->selectRaw("SUM(amount) as total_amounts")
   ->havingBetween('total_amounts', [ 100, 1000 ])
   ->groupBy('user_id')
   ->get();

You can use the raw query for the sum per user and use the having between for the sum of amounts.