Query with one to many relationship

Sounds like it's for an admin / report. I'd dump data to local machine, already have an ODBC connection setup and do the report in either visual foxpro or ms access. I did monthly A/R, A/P reports like that at a trucking company.

A desktop database is far more efficient for large reports and that sort of thing.

Just the way I do things.

Edit: I hope you don't want all 3 million at one time, normally reports are ran weekly / monthly with just those results, and in case of A/R any not paid yet.

Either way look at chunking results if needed.

This is my actual question but still no proper answer:

https://stackoverflow.com/questions/46027556/how-to-optimize-a-big-query

OK now I might have some temporary solution for this problem...

What if I have a column 'total_work_hours' in users table and update that value each time when new work_hours record is inserted or deleted or updated...

Then I won't have to calculate the work hours when I want to show the total_work_hours for each user?

Is this a bad thing to do?

The answer is only query whats needed. If you really want to query 3 million records often, good luck. Even paginated 20 per page, most people aren't going to paginate through all that mess.

You need to roll up your sleeves and get to work on queries that summarizes the data for the admin / chief / or whoever.

A group by would be a starting point.

The ORM isn't the way to go here.

If your application is going to be that huge you had better starting learning enterprise java programming, or for php compiled c++ classes.

I just want to learn how do you guys solve this type of problem using Laravel

A large enterprise application isn't solved with laravel or php.

Edit: Fedex requirements

* Java – J2EE, JDBC, JMS, EJB’s, etc
*  ICE Faces
 * Web Services – WSDL / SOAP
 * Weblogic
 * Eclipse
*  Linux & Shell Scripting
*  SQL
 * Hibernate
 * Spring
 * SVN Source control
 * ANT and Maven
*  AngularJS and HTML5

My problem is that I'm using datatables and i need to load everything so it can be sortable in browser... but you are probably right i still need to learn a lot

@lewis4u forget you ever heard the word datatables for huge results, stick to normal html table with normal pagination. Php or blade either, but only query what's needed.

Edit: quick lesson

There's a huge difference in a year end printed report and viewing on screen.

So if Boss only needs to see information on Bob Johnson, have a query pull up Bob Johnson with the information between two dates.

Boss not interested in Mary Adkins when viewing Bob.

Try to just pull what's needed (paginated) and you will be fine.

What about sorting? this is the only reason I am using datatables

You can have table headers as links, whereby user clicks say lastname and via a route, have a controller method order by last name.

For learning purpose, install cakephp 3.* and do a cake bake to return controller model view, and you will see how it works. But really it's just a controller method to order by a certain field.

I'm not saying don't use datatables, I am saying no need to query 3 million results at once. Bob in example above surely doesn't have 3 million records.