Best Practices - Retrieving Large Datasets With Relations

Question

Best Practices - Retrieving Large Datasets With Relations

dimhoLt Feb '15

Created Feb '15	Last Reply Feb '15	Replies 4	Views 914	Votes 1

dimhoLt 22.6k

Feb '15

Hello!

I'm considering best practices in Phalcon when retrieving large datasets from the database. The built-in ORM functionality is amazing when you're just retrieving a couple of items and then getting their related records using the magical methods:

<?php

use \MyApp\Models\SupportTicket;
use \MyApp\Models\SupportTicketMessage;
use \MyApp\Models\Customer;

$tickets = SupportTicket::find(array("limit" => 10));

foreach ($tickets as $ticket) {
    echo "Ticket " . $ticket->getId() . " was created my customer with email: " . $ticket->getCustomer()->getEmail() . ".\n";
}

However, I'm building a rest interface with models having 20+ related records, and if I want to use the built-in functionality as above to retrieve the items, I can get 1200+ DB questies for a single request.

I could use the QueryBuilder or a Resultset, but I'm unsure as of how to both fetch several records with relations AND get them as the Model objects in the PHP code.

Which is the best practice in this case?

Thanks for your time.

// dimhoLt

1

Prasetyo · Answer 1 · 2015-02-02T03:36:13-07:00

If I recall correctly by default phalcon model use lazy load technique;

when you call $ticket->getCustomer()->getEmail() it actually running mysql query. Its good when you have tons of related record and dont want to show them all.

If in case you have to show all records you may use eager loading technique (I dont know if this feature exist in phalcon).

Dylan Anderson · Answer 2 · 2015-02-02T09:26:22-07:00

IIRC by default phalcon model use lazy load technique;

That is correct - the related models aren't loaded until you actually ask for them.

Also, @Prasetyo, since this is a fairly international forum, and not everyone is up on their Internet lingo, it might be best to not use acronyms. I've updated your post, spelling out "IIRC".

dimhoLt · Answer 3 · 2015-02-02T11:32:39-07:00

Thanks for your replies guys, but I already know that Phalcon loads lazily. That's awesome by default, but when I want to retrieve large datasets like in the example, and still load them into usable objects, which is the best way to do this?

Dylan Anderson · Answer 4 · 2015-02-02T12:00:57-07:00

Oh - so you're saying you WANT to load all that stuff?

The only useful search results pointed to this: https://forum.phalcon.io/discussion/629/eager-loading.

For the life of me though, I don't know why you would want to do this.

Ian Hu · Answer 5 · 2015-02-05T01:28:43-07:00

Thanks for your replies guys, but I already know that Phalcon loads lazily. That's awesome by default, but when I want to retrieve large datasets like in the example, and still load them into usable objects, which is the best way to do this?

I guess what you want is the LOW LEVEL SQL interface https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql. The QueryBuilder WILL convert to several low level sql to retrive data from database, and that may not smart enough for performance. but the low level sql can be the best performance as you want.