Level 104
How is the value null if the column is not nullable; did you modify the original migration?
Jul 1, 2022
19
Level 1
Getting a default value of 0 in the DB when the column amount is null
Here's my migration file:
public function up()
{
Schema::create('income', function (Blueprint $table) {
$table->id();
$table->string('description');
$table->bigInteger('amount')->default(0);
$table->date('date');
$table->string('receipt')->nullable();
$table->foreignId('user_id')->nullable()->constrained('users')->onDelete('cascade');
$table->timestamps();
});
}
I'm looking to get an amount of 0 when no amount is entered in the date (for each day).
I tried adding ->default(0) but it doesn't seem to work.
Level 1
@tykus No i didn't, do i have to make the column nullable ? What i mean is that when i try to get the data of the amount of each day for let's say a mount, i get an array with only the non 0 numbers, while i'm looking to get an array of all the numbers whether it's 0 or a higher number.
Level 104
@Mar55 the solution would be to make an Accessor
public function getAmountAttribute($value)
{
return $value ?? 0;
}
No i didn't, do i have to make the column nullable ?
I don't know how a NOT NULL column could have null in that case
Level 1
@tykus I can't add nullable because the user is required to add an amount when he wants to create an income.
Level 104
@Mar55 so that was my question - how are you getting a null amount if the column is not nullable??? You asked:
Getting a default value of 0 in the DB when the column amount is null
Where does the null come from?
Level 73
Default sets the amount to 0 if a null value is passed in the insert.
Level 1
@Tray2 So what could be the solution ? Do i have to focus on the Controller instead ?
Level 73
@Mar55 What is it that you want to achieve?
Can you give a sample of data and the desired result?
Level 1
@Tray2 I'm getting this array when i dd the incomes of each day during a month: ^ array:9 [▼ 0 => "3000" 1 => "450" 2 => "2000" 3 => "4000" 4 => "500" 5 => "2800" 6 => "4000" 7 => "1500" 8 => "5000" ]
While i'm looking to get the incomes of all the days even those where there is no income (these one should display a value of "0").
Level 104
@Mar55 that is a very different question! You want the query to return 0 for the total amount when there is no record(s) for somes dates within the period.
You can solve this in the database query (solution depends on your DBMS) or in PHP using DatePeriod
Level 1
@tykus I'm working with phpMyAdmin, what could be the solution ?
Level 104
@Mar55 the UI you are using is irrelevant. But I suppose you are using mysql?
Level 73
@Mar55 So let me get this straight you have records like
- day 1 10
- day 3 15
- day 4 30
- day 6 21
and so on, the dates that doesn't exist ( 2 and 5) should be represented as 0
- 10
- 0
- 15
- 30
- 0
- 21
Is that the way you want it?
Level 1
@Tray2 Yes exactly
Level 104
@Mar55 and the question about which database server you are using?
Level 1
@tykus I'm using MySQL
Level 1
@tykus So ?
Level 104
@Mar55 so... you need to generate the dates in the range; you can do this using the following SQL query:
WITH RECURSIVE dates(date) AS (
SELECT DATE(?)
UNION ALL
SELECT DATE_ADD(date,INTERVAL 1 day) FROM dates WHERE date <= ?
)
SELECT date from dates;
If you LEFT JOIN the income table; you will get your records as needed, e.g. untested but should be close
WITH RECURSIVE dates(date) AS (
SELECT DATE(?)
UNION ALL
SELECT DATE_ADD(date,INTERVAL 1 day) FROM dates WHERE date <= ?
)
SELECT date, COALESCE(SUM(income.amount, 0)) as total_amount
FROM dates
LEFT JOIN income on income.date = dates.date
GROUP BY dates.date
Level 2
If records don’t exist in DB you can’t get them. You should populate the collection with PHP by looping in the collection and filling the gaps.
Please or to participate in this conversation.