Skip navigation.

Best practice for large list (custom queries)

Submitted by hanleenders on Wed, 06/04/2008 - 03:51

Hello,

In our applications we display a lot of large lists (more then 1000 rows) containing columns out of several tables. The solution we now have is that we build a custom query in the Mapper class with al lot of joins. From the controller class we directly call the mapper to get an array of rows, by example:

$orm = Xyster_Orm::getInstance();

$mapper = $orm->getMapperFactory()->get('Entity');

$rows = $mapper->getJoinedLargeList();

Is this this way to go or is there a 'best practice' solution closer to the a Xyster Set/Collection solution?

‹ Xyster_Orm Singelton behaviour Xyster 6 - validate issues ›

Would the query or

Submitted by Double Compile on Wed, 06/04/2008 - 10:16

Would the query or reportQuery methods from Xyster_Orm work? Are the joins on relations, or are they more complex?

-DC

The queries are

Submitted by hanleenders on Thu, 06/05/2008 - 02:00

  • The queries are complex: we use SELECT * FROM (SELECT ROW_NUMBER() OVER for paging and CASE syntax (MSSQL).
  • They entities have ORM relations, by example:
    • User hasOne Company
    • We want to show in a list the UserName and the CompanyName
    • Instead of one query to get the Users and 1000 queries to get the CompanyName we want to use only one query

Should this be possible using Xyster functions?

Just using the example you

Submitted by Double Compile on Sat, 06/07/2008 - 14:41

Just using the example you gave about the company name, this is the kind of thing I would do:

 <?php
 $query = $orm->reportQuery('User', 'select userName, company->companyName limit 20 offset 10');

The reportQuery will automatically do joins for your entity's relationships, like company->companyName. If your query is much more complicated, I'd say use a custom method and put the results in a Xyster_Data_Set.

-DC

User login