How to fetch data from db with unique date but different timings

Create a new table days and refer to them instead of storing date in the current table. Then request the relation from the days table.

Or groupBy date your request, and sum "litres" when is am and "litres" when its pm, but it will more "raw" sql than the other.

If you don't need exact time then a simpliest way is to have only one row for a single Day. Split Time in two columns am, pm (integer) then insert or update customer litres in that Day.

@vilfago @oriceon thank you for you responses.

@vilfago is there any other way to do with the exisiting table itself.

@oriceon without altering the table is it possible to do that??

actually i have an array, which totally adds the total milk litres and total rate.

this is the query,

  $sales = Sales_details::orderBy('created_at','desc')->get();

      $sales_details = [];

      foreach ($sales as $key => $sale) {

        //total litres initialisation for all customers
        $sales_details[$sale->customer_name]["total_litres"] = 0;

        //total rate initialisation for all customers
        $sales_details[$sale->customer_name]["total"] = 0;
      }



      foreach ($sales as $key => $sale) {

        $sales_details[$sale->customer_name]["total_litres"] += $sale->no_of_litre;

        //sum up the the total rate grouped by customer id

        $sales_details[$sale->customer_name]["total"] += $sale->total;
      }

        $all=Sales_details::orderBy('created_at','asc')->get();

         dd($sales_details);

      

when i do dd the output looks like this

array:69 [▼
  "Johnpan" => array:2 [▼
    "total_litres" => 53.0
    "total" => 1855.0
  ]
  "Muthuraj" => array:2 [▼
    "total_litres" => 29.0
    "total" => 1160.0
  ]
  "M.M Pannai" => array:2 [▶]
  "Paramasivam" => array:2 [▶]
  "Selvam" => array:2 [▶]

it fetched name, that person total litres and total amount.

i also need the date and time like i mentioned in my question

can we modify this in any way??

You want to get all users in one query, or only one ?

@vilfago

all data.

i have a query which fetches all the customers their total litres of milk and total amount.

think this example

17-01-2019      am      Abdul       30       litres

17-01-2019      pm      Abdul        50      litres.

17-01-2019      pm      Abdul        90      litres.

the query i mentioned above fetches

Abdul 170 litres. this is done.

but that division as per date and time how to do?

It's better to use your db engine for this, than multiple foreach. Cleaner and more efficient. Try :

$sales = Sales_details::groupBy('customer_name', 'date', 'time')
  ->sum('litres')
  ->orderBy('created_at','desc') //not sure it's useful
  ->get();

dd($sales);

@vilfago thank you for your effort. may i know what is db engine?

and i tried

 $sales = Sales_details::groupBy('customer_name', 'date', 'time')->sum('no_of_litre')->orderBy('created_at','desc')-> get();

but showing error


Call to a member function orderBy() on float

and also tried

  $sales = Sales_details::groupBy('customer_name', 'date', 'time')->sum('no_of_litre')->get();

but error

Call to a member function get() on float

whats the problem

db = database. Your database is optimized to handle this kind of work.

 $sales = Sales_details::groupBy('customer_name', 'date', 'time')->select('customer_name', 'date', 'time', DB::raw('SUM(no_of_litres) as total'))->orderBy('created_at','desc')-> get()

@vilfago i executed the query. i got

Collection {#1232 ▼
  #items: array:112 [▼
    0 => Sales_details {#1119 ▶}
    1 => Sales_details {#1120 ▶}
    2 => Sales_details {#1121 ▶}
    3 => Sales_details {#1122 ▶}
    4 => Sales_details {#1123 ▶}
    5 => Sales_details {#1124 ▶}
    6 => Sales_details {#1125 ▶}
    7 => Sales_details {#1126 ▶}
    8 => Sales_details {#1127 ▶}
    9 => Sales_details {#1128 ▶}
    10 => Sales_details {#1129 ▶}
    11 => Sales_details {#1130 ▶}
    12 => Sales_details {#1131 ▶}
    13 => Sales_details {#1132 ▶}
    14 => Sales_details {#1133 ▶}
    15 => Sales_details {#1134 ▶}

and if i open 0 => Sales_details {#1119 ??

Collection {#1232 ▼
  #items: array:112 [▼
    0 => Sales_details {#1119 ▼
      #dates: array:3 [ …3]
      #appends: array:1 [ …1]
      #connection: "mysql"
      #table: null
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #attributes: array:4 [ …4]
      #original: array:4 [ …4]
      #changes: []
      #casts: []
      #dateFormat: null
      #dispatchesEvents: []
      #observables: []
      #relations: []
      #touches: []
      +timestamps: true
      #hidden: []
      #visible: []
      #fillable: []
      #guarded: array:1 [ …1]
    }
    1 => Sales_details {#1120 ▶}
    2 => Sales_details {#1121 ▶}
    3 => Sales_details {#1122 ▶}

how can i see values.

do i need to pass it to a view file. then only can i see the output??

Look at the #attributes array, to see if the info in it is what you want

But it seems to look good

Do you have a customer table and a relation set between customer and sales order?

@vilfago i cant click that attribute array. if i click nothing happens.

let me pass it to a view and show the result..

@vilfago noo sorry, actually this is my first project. so at that time i not have any idea abt relationships.

so for everthing i created a table and in all tables i included the columns, id, customer_id, customer_name, customer_location.

i dont have any relationships..

Are you sure you don't want to create a correct database ?

If you have a customer table, you can only have "customer_id" in the other table. And when you need the customer name, you use this "customer_id" to retrieve the name (and with Laravel, it's so easy that you want to use it).

Even if it's not always correct, it's a good start to say that you never have to store twice the same information in a relational database.

Try that to show data : dump($sales->first()); dd($sales->first()->customer_name);

@vilfago sorry for the delay and than kyou for your suggestions..

right said. it will be better to have a relationship. that no need of storing the date twice.

when i joined here i got this project as my first project, and this is running locally only in my client system.

for first 2 months its ok for client but now he is asking the change like i mentioned in this question.

so when i started first i dont have idea abt relationship, so for urgent i done this for him.

Now my client is working with this project. he is storing in the db. so i change the db sturcture once again he need to start from first to store his inventory process.

but if i change the coding without any modification in the db just i can update in his system. so the the output he is expecting he can have it..

thats why iam trying to solve the issue withou not touching the db..

now i am clear about relationships and iam using that in my ongoing project.

wait let me try thisdump($sales->first()); dd($sales->first()->customer_name); and say

@vilfago actually iam having all the tables, customer table, supplier table, sales table, purchase table. but in all tables i included customer_id, customer_name, customer_location etc etc..

@vilfago

with the help of this coding

 $sales = Sales_details::orderBy('created_at','desc')->get();

      $sales_details = [];

      foreach ($sales as $key => $sale) {

        //total litres initialisation for all customers
        $sales_details[$sale->customer_name]["total_litres"] = 0;

        //total rate initialisation for all customers
        $sales_details[$sale->customer_name]["total"] = 0;
      }



      foreach ($sales as $key => $sale) {

        $sales_details[$sale->customer_name]["total_litres"] += $sale->no_of_litre;

        //sum up the the total rate grouped by customer id

        $sales_details[$sale->customer_name]["total"] += $sale->total;
      }

      

i got this output in my view when i pass the $sales_details.

Refer: https://imgur.com/a/T6JGvWj

i used the coding

<thead>
    <th>Customer Name</th>
    <th colspan="2">5-12-2018</th>
    <th colspan="2">7-12-2018</th>
    <th>December 5-7</th>

    <th></th>
</thead>
<thead>
    <th>Sales</th>
    <th>am</th>
    <th>pm</th>
    <th>am</th>
    <th>pm</th>

    <th>Total ltrs</th>
    <th>Total Rate</th>
</thead>

<tr>
    <td>Malgudi (Made this entry Manual)</td>   
    <td>35</td>
    <td>29</td>
    <td>30</td>
    <td>20</td>
    <td>114</td>
    <td>3876</td>
</tr>

@foreach($salesDetails as $customerName => $details)
<tr>
    @isset($details['total_litres'])
    <td>{{ $customerName }}</td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td>{{ $details['total_litres'] }}</td>
    <td>{{ $details['total'] }}</td>
    @endisset
</tr>
@endforeach

i got the customer name, their total litres of milk and their total amount.

the problem is the am and pm, for the dates. Refer the image , in that, see only am and pm milk are empty. how can i do that please help mee for that..

and the solution for what u sent is dump($sales->first());dd($sales->first()->customer_name);

Sales_details {#1119 ▼
  #dates: array:3 [▼
    0 => "created_at"
    1 => "updated_at"
    2 => "date"
  ]
  #appends: array:1 [▼
    0 => "formatted_date"
  ]
  #connection: "mysql"
  #table: null
  #primaryKey: "id"
  #keyType: "int"
  +incrementing: true
  #with: []
  #withCount: []
  #perPage: 15
  +exists: true
  +wasRecentlyCreated: false
  #attributes: array:4 [▼
    "customer_name" => "Johnpan"
    "date" => "2019-01-18"
    "time" => "am"
    "total" => 40.0
  ]
  #original: array:4 [▼
    "customer_name" => "Johnpan"
    "date" => "2019-01-18"
    "time" => "am"
    "total" => 40.0
  ]
  #changes: []
  #casts: []
  #dateFormat: null
  #dispatchesEvents: []
  #observables: []
  #relations: []
  #touches: []
  +timestamps: true
  #hidden: []
  #visible: []
  #fillable: []
  #guarded: array:1 [▶]
}


"Johnpan"

Kindly help and give a solution

You were lucky to be able to sale something so "rickety"...

What you need now is even more complicate, and it's probable that your client will need wvwn more...

If you have customer id in all table, and you have to push some new code, why you cannot set relations in models and push that with ? But as you wish...

You set 2 dates in your tables

    <th colspan="2">5-12-2018</th>
    <th colspan="2">7-12-2018</th>

Where they come from ? Is this static ? So you only need for these 2 dates ?

Try to figure what is the real need, and build your code. It seems the query I suggest work fine to group the row with the same customer_name, date and time, so you have a good start.

You can try use mapToGroups() to have some more usefull information ( https://laravel.com/docs/5.7/collections#method-maptogroups).

@vilfago

no the dates are not static. for explanation i kept it.

when fetching records i will use

  @foreach ($sales->unique('formatted_date') as $ss)       
        <th colspan="2">{{Carbon\Carbon::parse($ss->formatted_date)->format('d') }}</th>
        @endforeach

so that the dates will be listed in th without duplication

like this

1    2    3    4   5  

after that i need to fix all the records based on the customer.

i think this will be possible by making a new table only.

becoz i cant get the idea..

@snapey

Sorry for tagging you. i know this is wrong, but i need help. Kindly can you suggest me an idea please for this question for this thread.

i am suffering for idea.

with the help of my table i can do this? else i need to create another table only??

this is my sales_details table structure

Refer: https://imgur.com/a/3b1jd5k

in the above image format only i using table structure. my table also in the same structure.

i got partial answer that i mentioned in this thread.

Kindly if possible suggest you idea please.

even i asked this question 3 times with different formats.

Refer:

https://laracasts.com/discuss/channels/laravel/problem-in-viewblade-need-to-know-the-format

https://laracasts.com/discuss/channels/laravel/need-a-logical-help-in-viewblade-file

Kindly if possible answer..