MySQL database design + query - Laravel

1
votes

I got a little problem with my Database.

In my application i am retrieving data from a Excel sheet. I am using the framework Laravel and the library Laravel Excel(maatwebsite).

When i upload a file. In this example there are 7 rows from the sheet being insert into the database.

Database: enter image description here

I retrieve 5 from them with this query:

  $documentData = DB::table('documents')
  ->select(array('weld', 'diameter', 'thicknes', 'steelgrade', 'material', 'weldingdate', 'welderid', 'requestid'))
  ->where('diameter', '21.3')
  ->where('thicknes', '2.5')
  ->where('steelgrade', '—Ú20')
  ->get();

I select the data based on diameter,thicknes and steelgrade (my unique combination for every excel template(multiple combination possible)).

I place this information into the excel file that matches the combination and save it. That all works fine. But! when i upload another file (same unique combination for excel template), with almost the identical information except one column.

The query will retrieve all the row (10 after 2 uploads). How can i make sure it will only grab the latest upload. I know Laravel offers the created_at, updated_at column but i was wondering if there is something else. Order by/group by? or Latest();

I wonder if anyone has suggestions how i can solve this.

2
mysqldatabaseexcellaravel
You can groupBy the unique value (I guess weld) and then orderBy updated_at for getting the last records. - Troyer
@Troyer If checked the column updated at at 4 rows are updated_at: 2017-11-28 12:11:10 and one row is 2017-11-28 12:11:11. You would one get the last 4 but i need the last 5. Or am i wrong? - The M

2 Answers

1
votes

You can groupBy for the unique value and then orderBy created_at, and use take() for getting the last inserted values.

DB::table('documents')
  ->select(array('weld', 'diameter', 'thicknes', 'steelgrade', 'material', 'weldingdate', 'welderid', 'requestid'))
  ->where('diameter', '21.3')
  ->where('thicknes', '2.5')
  ->where('steelgrade', '—Ú20')
  ->groupBy('weld')
  ->orderBy('created_at', 'desc')
  ->take(5); //Replace 5 for the count of the import
0
votes

you could do: 

$documentData = DB::table('documents')
   ->select(array('weld', 'diameter', 'thicknes', 'steelgrade', 'material', 
   'weldingdate', 'welderid', 'requestid'))
   ->where('diameter', '21.3')
   ->where('thicknes', '2.5')
   ->where('steelgrade', '—Ú20')
   ->latest()
   ->get()
   ->take($count);

$count = amount of upload records each time..so maybe set it dynamically or manually each time after you have uploaded.